[b]Which Query?? [/b]

[trystanhuwwilliams1]

Please can someone help me with this problem, didn't get much sleep last night and I can't seem to work it out.

I've got a main table with 2 fields = POST + NAME
I've got 3 other tables which hold details of qualifications, training & professional memberships.
The tables are linked via the field POST.

How can I create a report for each NAME which lists all the records in the 3 tables?

The problem seems to stem from the fact that there are several records in each table against each NAME.

I.e If I wanted to create a report for a specific person, he or she may have 2 records in the training table, 1 in the 2nd table and 5 in the last table.

Any help would be appreciated,

Regards,

T

 

[sfreeman]

SELECT
tblMain.Name
, tblOne.Data
, tblTwo.Data
, tblThree.Data

FROM
((tblMain INNER JOIN tblOne ON tblMain.Post = tblOne.Post)
INNER JOIN tblTwo ON tblMain.Post = tblTwo.Post)
INNER JOIN tblThree ON tblMain.Post = tblThree.Post;

 

[RickSpr]

I don't think you want to do the inner joins. I'm assuming the three 'other' tables (qualifications, training, and professional memberships) are not related to one another. Doing the inner joins would give you every possible combination, which is what I imagine you're saying is the problem.

What you need is a report containing just the Name, and three subreports, one for each of the 'other' tables.

First, create a separate report for each 'other' table, setting its Record Source to the table name and arranging the columns however you like (or you can set the report to display a datasheet).

Then create the main report, drag NAME onto it from the Field Names list, and drag each of the 'other' table reports from the Database Window to it. You may need to set the subreport controls' Link Master and Link Child properties to POST, if Access doesn't do this automatically.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein