A Simple List of Records...

[cycledick]

Access 2003 v11 SP2
I have a database with three tables; Customer, Sites, & Systems. They are all linked. Customers to Sites to Systems. One "customer" record with one or more "site" records with each "site" having one or more "system" records. Make sense?

I want to create a simple list that shows how many and what kind of "systems" there are in that particular "site" record. Does that make sense? So, if I have Company A and they have two sites in Chicago and Dallas. In Chicago they only have 1 system and in Dallas they have 4 systems. I want to have a little display window on my form that will display the different type of systems at each site, so the person looking for information can see how many systems they have and what kind without having to scroll through records. I can't figure that out.

I've tried doing various things do get the data in a list box or text box through just queries or linking to a specific field, but I always get ALL the system records for ALL sites for ALL customers, not just the system record for the particular site I am on. It would be nice if I could get them to show up in a list box that the user could just double-click the system (or highlight it and click a button) and it would display the proper record, but one step at a time.

I appreciate any guidance.

 

[fneily]

Set up a fourth table, tblMatches, which would have three fields: CustomerID, SiteID and SystemID. They would form a three field primary key. So for companyA, tblMactches would look like:
CompanyA Dallas System1
CompanyA Dallas System2
CompanyA Dallas System3
CompanyA Chicago System1
etc.
Of course the above is each field's primary key from their table which is then combined to form the multi-field primary key for the tblMatches.

After that, retrieving the data is easy through - queries, listboxes, coding.

 

[traingamer]

I am somewhat puzzled by fneily's answer. The tables are already related so there is no need for an artificial linking table.

One way to accomplish this would be to set up a form with two combo boxes. The first combo box would display a list of customers. When you select a customer, the second combo box would list the sites for that customer. Selecting a site would populate a subform with the systems for that site.

See: Combo Box - dependant on another Combo Box faq702-4289 for help with setting up the combo boxes.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[fneily]

I am not sure of his relationships. One customer can have many sites and Dallas can have many customers. Also, one site can have many systems, and could a system be in more then one site??
So I see many-to-many relationships rather then his stated one-to-many's.
So maybe my whole fallacy’s wrong?