connecting fields in mulitple tables

[buscher75]

Hello, I have three tables that collect data. Each has an operatorID field.
I then have a table that has the operatorID and the operator's name.

Ultimately, I would like to produce a summary report pulling the operatorID
from each table and show the persons name not their ID. A field called
“SerialNumber†connects all three data collection tables through a
relationship. I can successfully show the employee’s name with one
operatorID field in the query, but get an error trying to run the query when
all three table's "operatorID" fields are listed. What am I doing wrong? Do
I need 3 different queries and then pull from each query in the report? Or,
is there a way to convert the information within the report?

I appreciate your help.

 

[Klatuu]

It shouldn't be necessary to include the operator id 3 time is the query as
output. They are only needed in the Join. What error are you getting?

 

[buscher75]

I was wrong, it does not show an error, it returns zero records when you run
the query. Under one serial number I want to show the three operators that
worked on that unit with their names not their badge numbers.

 

[Klatuu]

Post the SQL of the query as it is now.

 

[buscher75]

SELECT [Green Tag].[Serial Number], [Green Tag].Operator AS [Green
Tag_Operator], [Station 25].Operator AS [Station 25_Operator], [Station
33].Operator AS [Station 33_Operator]
FROM [Station 33] INNER JOIN ([Station 25] INNER JOIN [Green Tag] ON
[Station 25].[Serial Number] = [Green Tag].[Serial Number]) ON [Station
33].[SERIAL NUMBER] = [Green Tag].[Serial Number];

By the way, if you can not already tell, my knowledge of Access is limited,
so I really appreciate you time.

 

[Klatuu]

Open your query builder and look at the joins between Green Tag and each of
the other 3 tables. You do that by clicking directly on the line between the
join fields. Sometimes, you will get the wrong dialog, so close it and try
again. You have to be exactly on that line.
When you do, the Green Tag name should be on the left side of the table name
box and the other table on the right. My guess is that option 1 is selected.
You need to select option 2.
--
Dave Hargis, Microsoft Access MVP

SELECT [Green Tag].[Serial Number], [Green Tag].Operator AS [Green
Tag_Operator], [Station 25].Operator AS [Station 25_Operator], [Station
33].Operator AS [Station 33_Operator]
FROM [Station 33] INNER JOIN ([Station 25] INNER JOIN [Green Tag] ON
[Station 25].[Serial Number] = [Green Tag].[Serial Number]) ON [Station
33].[SERIAL NUMBER] = [Green Tag].[Serial Number];

By the way, if you can not already tell, my knowledge of Access is limited,
so I really appreciate you time.

Post the SQL of the query as it is now.