Queries with a table of related records

[Hank.School]

[Solved] Queries with a table of related records

Hi guys,
Having a problem here and I can't seem to figure it out.

I have a query that provides records for a form. That query has its primary table which holds associations between records in another table. The query has duplicate 'cascades' necessary to retrieve my data but something is going wrong at the first related table.

To make it easier to understand, lets say my primary table, Meetings, holds a meeting between two customers. The primary table would contain two fields (among others), Customer1 and Customer2 which each hold an ID to two different customers in the same customers table. The cascade would be for each customer's associated information which is all held in other tables, each referenced by previous IDs. Yeah, I know... that's not too easy to understand either I will try to explain the cascade part further if required but I am hoping you understand.

The way I have handled this previously in a query was to duplicate each of the 'sub-tables' and relate one cascade of relationships to Customer1 and the other cascade to Customer2 and it has always worked. Now I am building a new form which should do the same thing but the same customer is being pulled up by both cascades.


In my test form, I have made fields which show the two customer IDs from the query's primary table and they are indeed different. But when I change the fields to display the data from the next related table (let's say customer name) both fields have the same name. Does anyone know why this would happen? The only difference for this query (as opposed to others I have done like this previously) is that this has 18 different tables... my others have been much smaller.

Anyone have any ideas?

Just to clarify, the customers is only an example... I would not structure a list of customers in a way requiring a cascade of relationships like this. The actual data is circuits which are a connection between two facilities but I think this is easier than trying to explain the whole database.

In the mean time, I am going to try and cut out some of the lower tables and see what happens.

Thanks
Hank

 

[plog]

Can you post a screen shot of the query in design view? Better yet a database of the query itself?

 

[Hank.School]

Thanks for the reply...


I could post the screenshot but, in simplifying, I think I have found the problem. Still don't know why it is happening but I see what is wrong.


When I copy the same table twice into the query, the second one gets a '1' appended to the name. If I view the query in data view, everything is fine.



When I try to assign a field from that table1 to a text box, it initially looks like it is taking it but it drops the 1 from the table name for some reason.


Never had this behavior before. Any ideas?

 

[plog]

You've described a rube goldberg machine and words can only help a person understand so much of it. Need to see it for myself to get my mind around it.

 

[jdraw]

Hank,

I have no idea what you are talking about.
As plog requested --a screen shot or SQL of the query would be useful to readers.

is this close?

You have a table of customers and you want to manipulate/identify Customer Associations/Meetings?

Customer----->CustomersHadMeetings<---Customer1

where table CustomersHadMeetings
contains fields such as
Customer.CustID
Customer1.CustID
DateMtg
Notes

This one has me quite confused

but the same customer is being pulled up by both cascades.

 

[Hank.School]

Thanks jdraw,

You are very close...
Let's say I have a table of Customers, each with a Customer ID. Then let's say I have a table of Meetings which contain fields CustomerA, CustomerB, MeetDate.
The fields CustomerA and CustomerB in the Meetings table contain customer IDs from the Customers table which identify the two customers that participated in the meeting. Hopefully this is straightforward.

In building a query based on the Meetings table, I will require two copies of the Customers table to provide the information for CustomerA and separately for CustomerB. The way I accomplish this in Access is to have two copies of the Customers table in the query... and to relate the Customer ID field in one of those copies to the field CustomerA in the Meetings table and the Customer ID field in the other copy of the Customers table gets related to CustomerB in the Meetings table. (By 'relate' I mean that a relationship is built in the graphical query design window by connecting the fields with a line) I have done this with other queries and it works fine. I believe this is common practice.

As I said in my previous post, I have discovered that the problem is not in the query... posting the screenshot or the SQL statement will be of no help. I have discovered that the problem is in associating the fields from the query to textboxes on a form. You see, when I have a query that contains two copies of the same table (the Customers table), it will call the first table by its name (Customers), but when the table is added again, Access gives it another name by appending a '_1' to it (Customers_1). It does this in order to differentiate between the fields from the two copies of the Customers tables (or, more properly, the different data in the two different customer records).


When a textbox is bound to a field from the query, that field is selected from the 'Control Source' dropdown of the textbox's properties. So the textbox which displays the name of CustomerA will be bound to 'Customers.Name' while the textbox that gets bound to the field for the name of CustomerB would be bound to a field called 'Customers_1.Name'. When I select the field for the CustomerB textbox, I select 'Customers_1.Name' from the list of available fields. That is exactly the behavior I would expect. When I view the form, both textboxes (CustomerA and CustomerB) are displaying the name of CustomerA. That is not what I would expect. When I double check the Control Source property for the CustomerB textbox, it contains 'Customers.Name' instead of the 'Customers_1.Name' that I selected. So it turns out that it is not a problem with the query per se (as I originally thought) but a problem of the bound names changing. Does anyone have an idea why it would change and how to prevent it?


If all this sounds Rube Goldberg I assure you the database is structured properly... it would only be my description that is Rube Goldberg and for that I apologize. If my description is still not clear, I will post screenshots of what I am talking about.


Thanks
Hank

 

[jdraw]

No, not Rube Goldberg. That table/query structure is what I was thinking.

Can you post a copy of the database in zip format? I'll take a look.
You might also identify a meeting and the participating people/custoomers.

 

[Hank.School]

The database is way too big to post (even zipped) but I have stripped everything off except the relevant tables and form. I have confirmed that the database still behaves as described (on my copy of Access at least)

Open the form in design view, it is based off of a very simply query. In the database, a 'Circuit' is a connection between two 'Facilities' who's IDs relate to two fields in the Circuits table. Rather than getting side-tracked describing telecom circuit design, I used the same concepts with 'customers' to make it easier to understand. View the query in Data view and you will see the query is working fine... both IDs are listed as would be expected.

When the text box for Z Facility on the form is bound, one of the options for binding is the TBL_Facilities_1.Facility_ID field. Select this and view the form. You will see (hopefully!) that the ID for A Facility is displayed in both text boxes. Now, go back to the binding for that textbox and you will see it is now TBL_Facilities.Facility_ID despite that the other entry was selected... the '_1' was removed.

Can you confirm the behavior? Is there a limit to the length of a field name or something that is screwing me up?


Thank you, I really appreciate it.

 

[jdraw]

Hank,
I'm looking at your sample database--I made a copy.
Then I put an alias on each of the tbl_Facilities in the recordsource of the form as per these png files attached.

See if that helps.


You could make a query with the tables and use the query as the forms recordsource.
Good luck.

 

[Hank.School]

YES! That works fine, thank you so much.



When I give the field in the query an alias and select that alias in the textbox binding, it keeps the correct setting and the form displays correctly.


Do you know why it wouldn't work without the alias? I know I have done this before and don't think I used an alias back then.



Hank

 

[jdraw]

Glad you have a solution. I did make a query and using that as the recordsource of th form and that worked.
Here's the SQL of that query

SELECT TBL_Circuits.ID, TBL_Circuits.Circuit_Name, TBL_Circuits.A_End_Facility, TBL_Circuits.Z_End_Facility
FROM TBL_Facilities AS TBL_Facilities_1 INNER JOIN (TBL_Facilities INNER JOIN TBL_Circuits ON TBL_Facilities.Facility_ID = TBL_Circuits.A_End_Facility) ON TBL_Facilities_1.Facility_ID = TBL_Circuits.Z_End_Facility;