Query problem with multiple tables

  • Thread starter Thread starter Kyle
  • Start date Start date
K

Kyle

I need to do two separate queries from the 6 tables that I
have in my design. The first Query1 uses for my receiving
form design & the second Query2 uses for my Work Progress
form design. Somehow on my second form setup, every time I
update a field Access returns with an error message of "you
can not add or change a record because a related record is
required in table C." The following is how my relationship
setup.

Table A has a one to many(O-M) relationship with table B;
Table B has O-M with table C. Table C has One to One
relationship with Table D and Table E with a joint type 2.
The primary key in the relationship is WorkOrderID.

My Query1 requires fields from Table A, B, C & D for
setting up receiving form which has no problem. Query2
requires fields from Table A, B, C, D & E to setup Work
Progress Form. In query2 setup, I need only some of the
fields from Table C & 1 or 2 fields from Table D. All
fields from Table A & B. All fields from Table E. This is
where the error message comes up when I try to update field
in my Work Progress form.

I hope the above stated my problem as I could not figure
out what is causing the error message. The setup seems to
be valid. Any help would be appreciated. Thanks.
 
Table A has a one to many(O-M) relationship with table B;
Table B has O-M with table C. Table C has One to One
relationship with Table D and Table E with a joint type 2.
The primary key in the relationship is WorkOrderID.
Click to expand...

I'd suggest that you not try to create One Grand Master Query to do
this: instead, use the tools that Access provides!

I'd make a Form based on Table A; a Subform based on Table B; and a
sub-Subform based on a query joining tables C, D and E.

Note that one to one relationships are QUITE RARE; ordinarily you can
just include all the fields in one table. You might want to look at
these tables and see if the design is ideal.
 
Back
Top