problems with multiple relationships over multiple tables - see picture.

[thydzik]

I have the following problem.

I have 3 tables made up of ID and NAME and other parameters.
NAME is unique field in each table, whilst ID is not unique. the three tables share the same fields, but can't be combined due to the NAME field not being unique throughout.

Now I have a single table that has a unique ID so I want to make a 1-inf relationship between this table and all three. the problem is how do I look at all three tables at the same time instead of having to insert subdatasheet on only one table.

see attached picture for the relationships.

 

[Banana]

I hope you are not actually using "Name" for fieldname as this is reserved by Access/Jet and will casue more problem; make it like ClientName or something similar.

You say the three tables have same fields but cannot share as "Name" isn't same; this suggest to me you have normalization problem. You may want to look up about the normalization. There should be only one table storing all description that could be related to three lookup tables (assuming the rightmost three tables are truly distinct from each other).

Furthermore, I'm not understanding why isn't ID unique; if IDs are your keys, you will want them to be unique so you can relate names (which may be same or similar) easily.

AFAICT, you can only see one subdatasheet at a time. I'd worry about the table design before I get to the view configuring.

 

[Krij]

I have the following problem.

I have 3 tables made up of ID and NAME and other parameters.
NAME is unique field in each table, whilst ID is not unique. the three tables share the same fields, but can't be combined due to the NAME field not being unique throughout.

Now I have a single table that has a unique ID so I want to make a 1-inf relationship between this table and all three. the problem is how do I look at all three tables at the same time instead of having to insert subdatasheet on only one table.

see attached picture for the relationships.

Looking at your solution, you really just need one table with 'ClientID','ClientName','Descr'.

Like Banana said: change to ClientName or something, try to normalize a little and have a main form for main table, insert all subform on this mainform and voilá.....It works fine for my database. When relations are set correct, all underlying fields will show correct data.

By the way...Why not make ID as autonumber, then have a text field producing an ClientID or whatever ID is. This can be done in code like: Nz(DMax("[IDfield]","tblMain"),1)+1 & "2006"

Also change ID to something more descriptive like ClientID etc.

Good luck

 

[thydzik]

Thanks for the replies and mentioning about fields named "NAME", didn't knwo that.

I will explain my situation abit more, and the reason the database is designed that way.

Firstly, I don't create any of the tables myself. So the 3 different tables come from 3 different locations, I wish I could combine them but I can't as the NAME field is not unique between all of them. And I need this NAME field to find its matching data in the second set of tables. The two tables don't have the same amount of records.

The ID number is unique on the single table but on the three tables multiple records can have the same ID. hence 1 to inf.

The problem is that I want this single table to look at all 3 tables, since they all have the same fields. but I can only select the one table for the subdatasheet.

I have also tried performing a unison on the 3 tables, but then the relationships are not preserved.

 

[thydzik]

okay, this how I managed to solve my problem... thought it would be useful to let people know.

Firstly I did a join on the matching pairs of tables, with table1.name = table2.name then I unioned these 3 tables together. from this query I created the relationship.

edit: I spoke too soon. the subdata sheet is able to be seen in the table, yet when i create the report I get 'you have chose fields from the record sources which the wizard can't connect. but they seam to be clearly conected when I view the table and see the subdata

 

[Banana]

I've found Wizard to be sorely lacking when you're managing something involving more than two tables.

But I should add that just because you can see the subdatasheet does not mean that Access knows how it is related. It can understand a hierarchical relationship (e.g. Customers -> Orders -> Items orders) but not a sibling relationship (e.g. Customers -> Address and Customers -> Contacts; Access cannot see how Address and Contacts relate to each other). Assuming that your tables is properly designed, Access can handle both relationship, but will require more work if you need the latter scenario.

As for report, since you don't have to have a updatable query, you can make a query pulling all data together from three tables and make query your report's recordsource.