This must be basic but it's got me beat

[Stuart Green]

I have 2 tables, tbl_main and tbl_sub. Both are linked by a number field. On my form over tbl_main, I would like to put all my tbl_main fields and just one field from tbl sub and display the relevant data frm tbl_sub based on the link. I know I can do it by inserting a subform but I only want 1 field (in practise there are several more sub tables, so I do not want lots of sub forms, rather 1 field from each). The syntax to put in the DATA sources has got me beat, I keep getting the #NAME error. I keep feeling that this must be basic stuff, but it has sucked my brains out. I will be ver so humble if someone could point out the error of my ways

 

[Fizzio]

Create a query containing the fields from both tables you want on the form and set the recordsource of the form to that query.

 

[Stuart Green]

Quick response Fizzio!! I had better explain what I am trying to do more and then you can say whether I am barking mad or not. I wish to record the name and address of all customers in my main table. This table also has a straightforward sequential number on it. These customers can be registered with me for up to eight different regimes (and not just for one, there may be multiples reason and each one has a different style of registration number). So, I have created 8 other tables, one for each regime which holds diferent data dependant on the regime and a different style of registration number. The form I am working on is based around the main table. The user inputs the name and address and then has eight buttons to select from. When you click on the appropriate button you are taken to the appropriate table, to enter other data including a registration number (this filed exists on all eight sub tables). The sequential number from the main table is written automatically to the sub record to act as the link. What I am trying to do is show eight fields next to these buttons that show what the various regime numbers already registered are. So, as you navigate through every record on the main table, the various regimes they are registered in, and the number will also show up.
(As a further "refinement", if this field is populated, a modify button appears that takes you to the record to modify data if you wish). Sounds good on paper, but relies on me pulling in the one field (registration number) from eight different tables. I can't work out how the query method of being the data source would do this

 

[Fizzio]

In general, you would have one table for customers, one for regime and a join table of CustomerRegimes unless as you say each regime requires the recording of different data then as you have done, each regime would need a separate table.

Can a customer register for more than one regime and can they register for the same regime more than once? This will dictate whether you have a 1-1 or 1-many relationship.

You can add all 8 tables to the query, pulling the Registration ID from each table and displaying this on the form. In the query though, you will need to ensure that each registrationID has a different name if stored as 'RegistrationID' in all the tables.

Here is a very rough and ready demo (only Regime 1 works)

 

[Stuart Green]

Fizzio, thank you so much for your efforts, greatly appreciated (and eye opening for me). You have given me a way forwards, thank you

 

[Fizzio]

No probs - was the demo useful in pointing you in the right direction?

 

[Stuart Green]

Certainly was! Would never have got there in a million years without it (I am an old DataEase user and having trouble thinking "differently")