CrossTab Query three tables

[rajeevs]

I am trying to connect three tables in a crosstab query.
Tbl-1 Field - Staff
Tbl-2 Field - Document
Tbl-3 Fields - Staff Document Viewed
What I am trying is to get a crosstab query or Vb module which can list all the staff from Tbl-1 as the row heading and all the Documents from Tbl-2 as the Column heading and put "Yes" or "No" if the staff viewed the document which is available in Tbl-3
Much appreciated if someone can help me

Regards

[zmbd]

Create a normal select query (Q) that returns all of these values.
Once that is done the cross-tab should be quite easy to create based on (Q)

[rajeevs]

Thank you for the response. But I have tried that approach. The crosstab returns only the staff with Viewed. But my issue is I need to see all the staff from Tbl-1, all the documents in Tbl-2 and the value field to show the staff viewed and the staff names not in the Tbl-3 but available in Tbl-1.
As an example if total staff is 100 in Staff Tbl and Tbl-3 has only 90 staff details who viewed the document I need to crosstab Row source with 100 staff, column heading with the document names and the values as yes or no according to the status of viewed. The viewed field is a date field.
I can generate the crosstab with 90 staff who are available in Tbl-3, but the other 10 staff from the Tbl-1 is not showing. Please help.

Thank you once again

[zmbd]

Create the select query, change to piviot table view, drag and drop the fields into the correct areas of the piviot table.
V2003: http://office.microsoft.com/en-us/ac...001034580.aspx
V2010: http://office.microsoft.com/en-us/ac...101901543.aspx

[rajeevs]

It still shows the same data. Because the select qry can show only the joined fields match. So when I make Pivot it shows the staff which match with the tbl-3 staff. not showing the staff which is not in Tbl-3. I want the unmatched also in the Staff list which is in Tbl-1.

[Rabbit]

You need to use an outer join in your query.

[zmbd]

Thank you Rabbit
At a friends house tonight and lost the network connection just as I clicked post!
....see if I'm back online?!?!?

Yes, back UP!

Rajeevs, please post your SQL, remember to format it in a code block by using the <CODE/> button

[rajeevs]

SELECT 
Tbl1.Staff, 
Tbl2.Document, 
Tbl3.Viewed
FROM Tbl2 
 INNER JOIN 
 (Tbl1 INNER JOIN Tbl3 
   ON Tbl1.Staff = Tbl3.Staff) 
   ON Tbl2.Document = Tbl3.Document;

[zmbd]

Have you made any headway on this?
You need to relate the documents to the staff and you want to relate every document to every staff memeber.
Once you have this relationship, you can then relate that back to the third table.

[[[edit]]This doesn't work for access[[/Edit]]
Or you could left join staff on the third
then left join document on the third
then setup a third relationship between these two queries.]

[rajeevs]

Hi zmbd
Thank you for all the effort you showed to help me out. But still I am getting the same result what i was getting earlier. I cannot see all the document names which is in tbl2 but not in tbl3. Can you suggest a different approach with a VBA module which can check all the staff in tbl1, all the documents in tbl2 and staff against the viewed status of documents in Tbl3 and insert to a new table. I am not so good in Vb but I can understand code and know how to change it if i get a bit of it. Hope you can help me. Thank you once again

[Rabbit]

rajeevs, there's no need for code, you just need to use a left or right outer join instead of the inner joins that you're using. It's a very simple change.

[zmbd]

I don't understand how you are not able to get the query to work using either Rabbit's or my approach.
I just tested the approach I gave you on one of my test databases... worked very well.
I'm currently playing with the SQL you posted in #8 to see which one(s) to make left/right joins on... if Rabbit said it can be done, I suspect that it can....

Please read the posts again and post your efforts.

[zmbd]

@Rabbit:
Tweaking SQL in post 8 every which way and all I get is errors from msaccess.
However, If I do a cross-product to expand staff against documents and then relate this back against the viewed documents on the staff_pk and document_pk then I can return exactly what OP wants... all staff in the rows and all documents in the columns with a single yes/no/blank at the intersections.

Easier method?

[Rabbit]

The only thing that I see that may be causing errors is the parentheses they're trying to use. But if you reorder the joins a little bit, and get rid of the parentheses, I think it should work by doing a table1 left join table3 left join table2.

[zmbd]

Thnx Rabbit: I thought I had tried that and MSA tossed an "Ambiguous Outer Joins…" However, the test DB I built is on the home PC while listening to the children try to bathe each other (2x3yr+1x9yr)=c haos+flooded_ba throom… I kind-of had to go put a stop to that before the bathtub floated down the street!
;-)
I'll give that rearrangement a shot when I get home...