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)=chaos+flooded_bathroom… 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...

[rajeevs]

Dear Zmbd

Can you send me your test DB so I can have a look and try to build the same way you did.
Thank you

[zmbd]

I'll pass that along this evening... sorry the DB is at home :)

In the meantime, will you please post what you have been working on to solve this?

(While I'm at it: Rabbit, tried the suggestion as given in #14, that doesn't seem to work)

[Rabbit]

I'll wait till you post your sample db as I'm too lazy to mock one up right now lol.

[zmbd]

Just waiting for rajeevs to post the most recent effots... otherwise the information in post 9 is how I built the DB for the cross-product-to-hasread queries.
However, the second paragraph in 9 didn't work quite as expected... stupid jet.

[rajeevs]

No luck so far. I am stuck with another issue in another db. I will be posting that because it is very critical for me at the moment. But to this issue so far i can make a report based on viewed staff and documents. Main issue is still pending
Hope you will give me your test DB.

Thank you

[rajeevs]

I am still waiting for zmbd's sample db. because the query approach was not clear for me

[twinnyfo]

rajeevs (et al),

Try this query. I did not use any right or left joins, because it would cause ambiguous joins and the query would not work. I simply count how many records are not null, and if it is not, then the person has viewed the document.....

TRANSFORM IIf(Count(Tbl3.Staff) Is Not Null,"Yes","No") AS Viewed
SELECT Tbl1.Staff
FROM Tbl2
    INNER JOIN (Tbl1 INNER JOIN Tbl3
        ON Tbl1.Staff = Tbl3.Staff)
        ON Tbl2.Document = Tbl3.Document
GROUP BY Tbl1.Staff
PIVOT Tbl2.Document;
 

Hope this hepps....

[zmbd]

I had read R's last post to mean that R would post latest efforts.

[zmbd]

@ Twinnyfo:
Tried your query in my test DB.
If in the documents table there is (are) a document(s) that no-one has beend assigned (or read) then that title does not show in the cross-tab.
Nor does is show all of the staff.
So this is the result using your query:

Whereas this is what I think OP is after:
(well, I didn't do the fill in the blanks as your query does... maybe I'll do that here in a moment, easier on the eyes me-thinks.)

{Edit} Rememebered why I left those empty cells...
Staff/Document intersection:
Blank = not assigned
No = Assigned but not read
Yes = Assigned and read.{/edit}

[twinnyfo]

Z,

How did you join your tables? After playing around with this one, I presume you came upon this:

TRANSFORM IIf(Count(Tbl3.Staff) Is Not Null,IIf(Count(Tbl3.Staff)>0,"Yes","No")) AS Viewed
SELECT Tbl1.Staff
FROM Tbl2
    LEFT JOIN (Tbl1
        RIGHT JOIN Tbl3 ON Tbl1.Staff = Tbl3.Staff)
        ON Tbl2.Document = Tbl3.Document
GROUP BY Tbl1.Staff
PIVOT Tbl2.Document;
 

I agree with you that values in each row/column is not so easy on the eyes....

[zmbd]

Nice try,
You return all of the documents accross the top; however, now you only return a blank row for unassigned staff to documents and only those staff with documents assigned to them.
You also can not do two lefts to the assigned as JET chokes

[twinnyfo]

Mine seems to work fine with the two lefts. You just have to get them in the right order. And, when the Value result is "None", it idicates the document is not assigned to anyone (or hasn't been viewed by anyone).

My question is how would we know a document was assigned to a person? IF the document is listed in Tbl3, assigned to a person, that seems to indicate that it has been read. Another (perhaps,better) thought is to add a check box field to Tbl3, just to indicate whether the document has been viewed. Use that field as the Value, either as max or min or sum. Then, if a document is assigned to someone, it will show up in the column headings, and it will indicate whether it is read. Also, if a document is not assigned to anyone, it will not show up on the list. This also would not require any right or left joins.....

[zmbd]

By two lefts:
Tbl_RequiredReading
RequiredReading_PK
FK_Staffpk
FK_Documentpk

If you open query and try to left join the tbl_staff on primary key to tbl_RequiredReading AND
left join tbl_documents to tbl_RequiredReading
(tbl_staff.staff_pk LEFT Tbl_RequiredReading.fk_staffpk
(tbl_documents.documents_pk LEFT Tbl_RequiredReading.fk_documentspk)
You will get an error.

If you: staff_pk>required_fk_staff::required_fk_document>d ocuments_pk you also do not return as OP desired.

Twinnyfo - From what I read in the second part of your post, you have the basic design of my test db - My db:
tbl_staff
staff_pk - autonum
staff_fname - text(50)

tbl_documents
documents_pk - autonum
documents_title - text(50)

tbl_requiredreading
requiredreading_pk - autonum
[{FK_Staff_inRR - numeric(long) (tbl_Staff(1:m))}
{FK_Documents_inRR - numeric(long)(tbl_documents(1:m))}
compound index as unique not pk]
requiredreading_hasread - boolean(default-false)

As you've guessed, what I've done:
In tbl_requiredreading, if the document is assigned to the staff member as a required document to read then a record is entered with FK_requiredreading_Staff=Staff_pk and FK_requiredreading_Documents=documents_pk. When the Staff reads the document requiredreading_hasread=true.

What you suggest does not meet OP's requirements... it appears that OP wants something along an EXCEL worksheet that has all of the titles accross the top, assigned or not, and all of the staff along the rows - even if no assigned reading has been made. (or pivot the header/row) This makes perfect sense to me as it is easy to tell if the document has been assigned to anyone and the status etc... the solution I used is given in post#9

[twinnyfo]

Z,

Well, I will admit that I was unclear from the original post exactly what fields were actually included in OP's tables. That being said, this can also be done without any relationships at all between the tables in the query, since we just want a complete list of all staff and a complete list of all documents. Then, we just determine if the assigned reading table meets the criteria of the rows/columns. If so, then determine if the doc has been read based on the Check, and convert to a readable format. The below maintains OP's original table/field names, also fulfilling all requirements of all staff and all docuemnts.

TRANSFORM Max(IIf(Tbl-3.Staff=Tbl-1.Staff And Tbl-3.Document=Tbl-2.Document,IIf(Viewed,"Read","Not Read"))) AS Read
SELECT Tbl-1.Staff
FROM Tbl-1, Tbl-2, Tbl-3
GROUP BY Tbl-1.Staff
PIVOT Tbl-2.Document;
 

I am just looking for simpler ways to skin this cat. I am not, however, trying to beat a dead horse. I just want to understand the solution better myself. Your solution in Post #9, when I did it, produced the error of ambiguous outer joins, which is why I've been throwing other ideas out there. Reversing the join does not produce an error, but will not include all staff or documents when unassigned....

[zmbd]

Twinny,
That caught it.
In the attached, it is qry_twinnyfo3... I've also included your other suggestions in the DB

Post#9: 1st: crossproduct between staff and documents then do the double left join between the crossproduct and the required reading table. The advantage here is no iif statement for the SQL engine to bugger.

Post#9 second part fails - I noted that in post#19, I've also edited post#9 noting that the suggestion in paragraph 2 doesn't work.

Attached is in V2010 give a jingle if needed in v2003

[twinnyfo]

Z,

Thanks for the test DB. I can understand now what you've done and how you got there. I am sure that your method would be much quicker with large sets of data, as well.

Thanks for your advice on this, as it helps me in my query design as well...

[rajeevs]

Hi zmbd
I tried to copy the attached DB, but I am running Access 2007 and I am unable to open this DB. Could you advise how to?
Regards

[zmbd]

I saved it in V2010 and it should be compatible with 2007 as I used only the most basic of elements...
Silly question ... please honestly I mean no offense... did you un-zip it first?
I sent some files to a friend once, he's quite intelligent and he couldn't get the files to open... turns out... he was trying to open the zipped Excel workbooks... chuckle.

[rajeevs]

Hi zmbd

Yes - I unzip the file using the program 7-Zip. My windows ver is Windows 7 Professional and office with Access 2007.
After unzip, when I tried to open the DB, I get the error message as unrecognized database format for pivottable.accdb

Regards

[zmbd]

win7 64bit
or 32bit?

[rajeevs]

Win7 64 bit and office 2007

[zmbd]

Most unfortunate.
D/L to WinXP(MCE)SP3 at home used windows to extract... file opened.
Tried to convert to v2003; however, some setting is preventing that ... something about general sort order. I'll try that agian later.

In the meantime, the information provided in post#28 and in the 1st part of post#9 should be enough to re-create the DB on your end.