Creating Query with tables and one to many relationships

[Imbriani]

I'm having a problem with Access and relating tables that I can't seem to find a solution or definitive answer for. I hope some of you can help.
I have six Access tables - one main table and five satellite tables - that are all related in a one to many relationship (the main table being the one) and all are joined to the main table on the primary key. I have been unable to create a query using all six tables so that I can create a data entry form. Information needs to be placed in all tables. I have been unable to find out if this is possible in a one to many relationship. These tables are serving two purposes - they are collecting data from an online program in Cold Fusion PLUS I am trying to use the same data to print reports. Any suggestions and/or advice would be appreciated.

 

[Blorf]

If your objective is a data entry form, I would recomend not using a query to base them on.

Make one form for the main (1) table, and create sub forms for the other (Many) tables.

What I generaly do is make forms one at a time, and drag and drop the sub forms onto the main form. Makes all the links for me, so I can be brain numb while doing it.

Hope that helps.

 

[Imbriani]

Blorf,

I tried doing this and there seems to be a problem with all the tables being linked in a one to many relationship with the main table. If I delete the existing relationships and do as you suggested, will that get around the issue of the one to many relationships?

Also, is it true that all these tables can be linked together with the main table on the primary key (fumigationID) without the satellite tables being linked to each other?

 

[Blorf]

Yes.

If I understand you, you have a structure like this

Main table
------ Sub table---other sub ---- next sub

You should have it like this


:-----sub1
:-----sub2
Main table----- sub3
:-----sub4

 

[lespaul]

Why would the satelite tables be linked to each other? And why would you want to delete all your relationships?

When you set up the sub forms it will ask you what you want to do, look up child records based on the master field.

what are you having problems with in setting up the subforms?



Leslie

 

[Imbriani]

Maybe a simpler explanation will help. I have a main table (tblfumigation) with a primary key (FumigationID). Each satellite table contains the Fumigation ID as a foreigh key. These tables are joined in one to many relationships, tblFumigation being the one and the other tables as manys. Every satellite table is linked to the main table via the FumigationID field, sort of like the spokes of a bike wheel with tblfumigation in the middle. I have been unable to find an example of this, so I was unsure this configuration was even functionable. The only examples I could find showed a main table linked to another table which was linked to another table - sort of a daisy chain configuration. I am trying to compose a query including ALL the fields of EVERY table so I can make a data entry form and populate all the tables with data. That was the original plan. But, I have tried subforms by using ONLY the tables and never queries to enter data. Is this possible and will the data go in correctly if I can make the main form/subform data entry sheet work? Do I link the subforms to that main form via the child/parent options? Is this how Access knows to put all the information for one record in the adjoining tables under the primary key for that record? I"m sorry if this sounds like a stupid question.