Query based on 2 related tables

[Guest]

Access 2003. I have a JOB DETAILS (one) table and an ADD ORDER (many) table
in a one to many relationship. JOB DETAILS has an estimated delivery field.
ADD ORDERS has an estimated delivery field. I want to create a query for a
report that will be a schedule showing all JOB DETAILS records and all ADD
ORDER records sorted by date essentially merging the two. Since JOB DETAILS
has all the pertinant info I need any ADD ORDER record pulled up to show the
related JOB DETAILS record.

 

[Marshall Barton]

Access 2003. I have a JOB DETAILS (one) table and an ADD ORDER (many) table
in a one to many relationship. JOB DETAILS has an estimated delivery field.
ADD ORDERS has an estimated delivery field. I want to create a query for a
report that will be a schedule showing all JOB DETAILS records and all ADD
ORDER records sorted by date essentially merging the two. Since JOB DETAILS
has all the pertinant info I need any ADD ORDER record pulled up to show the
related JOB DETAILS record.

Add both tables to your query. You can the add all the
fields (up to 255) you want from both tables. Make sure the
linking line is between the job pk field and the related fk
field in the order table.

Then you can use the report's Sorting and Grouping window
(View menu) to group on a unique field in the job table.
Select Yes for the Group Header. Now you can add the job
text boxes to the group header section and the order text
boxes to the detail section.