Solved

show all rows in crosstab query

Posted on 2011-03-21
37
464 Views
Last Modified: 2012-08-13
I have a crosstab query in which I would like to display all row values even if there is no value associated.  I have tried the left join option, but that provides multiple instances and creates invalid values.  Any suggestions would be appreciated.  Below is my query.

PARAMETERS [Forms]![frmClientReports]![cmbClientName] Text ( 255 );
TRANSFORM Count(tblClients.ClientNumber) AS CountOfClientNumber
SELECT tblClients.ClientName, tblBranchLocations.Address, tblBranchLocations.City, tblBranchLocations.State
FROM tblBranchLocations INNER JOIN ((tblClients INNER JOIN tblReviewHistory ON tblClients.ClientNumber = tblReviewHistory.ClientNumber) INNER JOIN tblBranchReviewHistory ON (tblReviewHistory.ReviewID = tblBranchReviewHistory.ReviewID) AND (tblClients.ClientNumber = tblBranchReviewHistory.ClientNumber)) ON (tblBranchLocations.BranchID = tblBranchReviewHistory.BranchID) AND (tblBranchLocations.ClientNumber = tblClients.ClientNumber)
WHERE (((tblReviewHistory.ReviewDate)>=DateAdd("yyyy",-3,Now())) AND ((tblClients.ClientNumber)=[Forms]![frmClientReports]![cmbClientName]))
GROUP BY tblClients.ClientName, tblBranchLocations.Address, tblBranchLocations.City, tblBranchLocations.State
PIVOT tblReviewHistory.ReviewDate;
0
Comment
Question by:nfstrong
  • 18
  • 18
37 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 35188881
you need to save the crosstab query and then perform the left join in a second query with the unique values on the left hand side of the join and the crosstab query on the right hand side.
0
 

Author Comment

by:nfstrong
ID: 35190126
I created the below query and it's displaying empty rows for the ClientNumbers that don't match.  Am I missing something?

SELECT qryBranchReviewHistory.*
FROM tblBranchLocations LEFT JOIN qryBranchReviewHistory ON tblBranchLocations.ClientNumber = qryBranchReviewHistory.ClientNumber;
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35190970
Your problem description indicated that you wanted to see all the entries and any matching crosstab data.  Your posted query only references the crosstab query and does not reference the tblBranchLocations.ClientNumber column.

The following query will have two ClientNumber columns.  You might need to specify all the qryBranchReviewHistory columns explicitly to avoid duplicate ClientNumber columns.

SELECT tblBranchLocations.ClientNumber, qryBranchReviewHistory.*
FROM tblBranchLocations LEFT JOIN qryBranchReviewHistory ON tblBranchLocations.ClientNumber = qryBranchReviewHistory.ClientNumber;

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:nfstrong
ID: 35192216
I guess I wasn't real clear on what exactly I'm looking for.  I would like to see all records from the Branch Locations table for a certain Client Number even if there are no values associated with that branch in the qryBranchReviewHistory.  Sample below.

Client Number       Address        City       State       1/25/09       3/24/10       1/24/11
ABC0                    1234 Walnut   Town    CA                   1                                     1
ABC0                     2345 Oak       Ville       AZ                                      1
ABC0                     345  Apple     Fruit       UT                
ABC0                     567  PearTree  Town   CA                  1                                     1
ABC0                     789  Bear        Ville       AZ                                      1

Hopefully this makes sense.  Let me know if you need more information.  I appreciate your help.

0
 
LVL 45

Expert Comment

by:aikimark
ID: 35192330
Then you need to include tblBranchLocations.Address (or whatever the columns are named) in the Select clause as well as include those columns in the Left Join clause, if that is needed to uniquely identify a branch.
0
 

Author Comment

by:nfstrong
ID: 35192699
That still shows too many records.  It shows all the records in the Branch Locations table.  I only want to show the records for the selected Client Number and then populate the crosstab query as shown above.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35192821
you will need to add a where clause or use the query wizard to limit the rows from the branch locations table.  You can use a parameter such as
[Enter Client Number]
0
 

Author Comment

by:nfstrong
ID: 35193658
I included a WHERE clause but it shows each branch with data from the review history query for each branch for that client in the branch locations table.  so if a client has 19 branches, but only 5 show in the review history query, it will show those 5 branches 19 times.

SELECT tblBranchLocations.ClientNumber, qryBranchReviewHistory.*
FROM tblBranchLocations LEFT JOIN qryBranchReviewHistory ON tblBranchLocations.ClientNumber = qryBranchReviewHistory.ClientNumber
WHERE (((tblBranchLocations.ClientNumber)=[Forms]![frmClientReports]![cmbClientName]))
ORDER BY tblBranchLocations.ClientNumber;
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35193772
Please post a spreadsheet with two dummy clients and their location data.

You might need to include a similar (the same?) condition in your query to limit the rows to a client.

Can I assume correctly that your combobox has multiple columns and that the first column is the clientID?
0
 

Author Comment

by:nfstrong
ID: 35198556
Yes, the combo box contains the ClientNumber and ClientName.

The attached spreadsheet includes data from the three tables used in the queries.  Let me know if you need any additional information.
Branches.xls
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35200070
I think the following should give you what you want:

qryBranchReviewHistory
----------------------
TRANSFORM Count(ReviewHistory.ReviewDate) AS CountOfReviewDate
SELECT BranchReviewHistory.ClientNumber, BranchReviewHistory.BranchID
FROM BranchReviewHistory INNER JOIN ReviewHistory ON BranchReviewHistory.ReviewID = ReviewHistory.ReviewID
GROUP BY BranchReviewHistory.ClientNumber, BranchReviewHistory.BranchID
PIVOT ReviewHistory.ReviewDate;




The query showing the data you want
-----------------------------------
SELECT BranchLocations.ClientNumber, BranchLocations.BranchName, BranchLocations.Address, BranchLocations.City, BranchLocations.State, qryBranchReviewHistory.*
FROM BranchLocations LEFT JOIN qryBranchReviewHistory ON (BranchLocations.BranchID = qryBranchReviewHistory.BranchID) AND (BranchLocations.ClientNumber = qryBranchReviewHistory.ClientNumber);

Open in new window

0
 

Author Comment

by:nfstrong
ID: 35207088
Which query do I add my WHERE clause too?  I've tried it in both but I keep getting and error that the database engine does not recognize [Forms]![frmClientReports]![cmbClientName] as a valid field name or expression.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35207154
You could put them in both queries as shown below.  However, you might only need to put them in the second query.

...after the FROM clause, of course

qryBranchReviewHistory
----------------------
TRANSFORM Count(ReviewHistory.ReviewDate) AS CountOfReviewDate
SELECT BranchReviewHistory.ClientNumber, BranchReviewHistory.BranchID
FROM BranchReviewHistory INNER JOIN ReviewHistory ON BranchReviewHistory.ReviewID = ReviewHistory.ReviewID
WHERE (((tblReviewHistory.ReviewDate)>=DateAdd("yyyy",-3,Now())) AND ((tblClients.ClientNumber)=[Forms]![frmClientReports]![cmbClientName]))
GROUP BY BranchReviewHistory.ClientNumber, BranchReviewHistory.BranchID
PIVOT ReviewHistory.ReviewDate;




The query showing the data you want
-----------------------------------
SELECT BranchLocations.ClientNumber, BranchLocations.BranchName, BranchLocations.Address, BranchLocations.City, BranchLocations.State, qryBranchReviewHistory.*
FROM BranchLocations LEFT JOIN qryBranchReviewHistory ON (BranchLocations.BranchID = qryBranchReviewHistory.BranchID) AND (BranchLocations.ClientNumber = qryBranchReviewHistory.ClientNumber)
WHERE (((tblReviewHistory.ReviewDate)>=DateAdd("yyyy",-3,Now())) AND ((tblClients.ClientNumber)=[Forms]![frmClientReports]![cmbClientName]));

Open in new window

0
 

Author Comment

by:nfstrong
ID: 35207231
Right, that still doesn't help with the error I receive and the fact that we aren't pulling anything from tblClients, so that parameter won't work.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35207261
I had copied and pasted your WHERE clause.  It wouldn't work in the  qryBranchReviewHistory query for the exact reason you described.

Corrected below.
qryBranchReviewHistory
----------------------
TRANSFORM Count(ReviewHistory.ReviewDate) AS CountOfReviewDate
SELECT BranchReviewHistory.ClientNumber, BranchReviewHistory.BranchID
FROM BranchReviewHistory INNER JOIN ReviewHistory ON BranchReviewHistory.ReviewID = ReviewHistory.ReviewID
WHERE (((tblReviewHistory.ReviewDate)>=DateAdd("yyyy",-3,Now())) AND ((BranchReviewHistory.ClientNumber)=[Forms]![frmClientReports]![cmbClientName]))
GROUP BY BranchReviewHistory.ClientNumber, BranchReviewHistory.BranchID
PIVOT ReviewHistory.ReviewDate;

Open in new window

0
 

Author Comment

by:nfstrong
ID: 35207304
ok and I don't understand why I would receive this error that the database engine does not recognize [Forms]![frmClientReports]![cmbClientName] as a valid field name or expression.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35207326
is the form open?
0
 

Author Comment

by:nfstrong
ID: 35207338
I've tried it with the form open and with the form closed and I get that error each time.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35207366
are the form name and combobox name correct?

you might need to use the query design wizard's BUILD function to create that reference for you.
0
 

Author Comment

by:nfstrong
ID: 35207413
ok, I tried to use the BUILD function and it gave me the same syntax and the same error.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35207492
please paste the two queries that were the result of the build tweak
0
 

Author Comment

by:nfstrong
ID: 35207524

TRANSFORM Count(tblReviewHistory.ReviewDate) AS CountOfReviewDate
SELECT tblBranchReviewHistory.ClientNumber, tblBranchReviewHistory.BranchID
FROM tblReviewHistory INNER JOIN tblBranchReviewHistory ON tblReviewHistory.ReviewID=tblBranchReviewHistory.ReviewID
WHERE (((tblReviewHistory.ReviewDate)>=DateAdd("yyyy",-3,Now())) And ((tblBranchReviewHistory.ClientNumber)=Forms!frmClientReports!cmbClientName))
GROUP BY tblBranchReviewHistory.ClientNumber, tblBranchReviewHistory.BranchID
PIVOT tblReviewHistory.ReviewDate;

SELECT tblBranchLocations.ClientNumber, tblBranchLocations.Address, tblBranchLocations.City, tblBranchLocations.State, tblBranchLocations.[Zip Code], qryBranches.*
FROM tblBranchLocations LEFT JOIN qryBranches ON (tblBranchLocations.BranchID=qryBranches.BranchID) AND (tblBranchLocations.ClientNumber=qryBranches.ClientNumber)
WHERE (((qryBranches.ReviewDate)>=DateAdd("yyyy",-3,Now())) And ((tblBranchLocations.ClientNumber)=Forms!frmClientReports!cmbClientName));

Open in new window

0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 35207782
For some strange reason, Access wants this query parameter defined, or else it raises the 3070 error.  Go figure?!?
PARAMETERS [Forms]![frmClientReports]![cmbClientName] Text ( 255 );
TRANSFORM Count(tblReviewHistory.ReviewDate) AS CountOfReviewDate
SELECT tblBranchReviewHistory.ClientNumber, tblBranchReviewHistory.BranchID
FROM tblReviewHistory INNER JOIN tblBranchReviewHistory ON tblReviewHistory.ReviewID=tblBranchReviewHistory.ReviewID
WHERE (((tblReviewHistory.ReviewDate)>=DateAdd("yyyy",-3,Now())) And ((tblBranchReviewHistory.ClientNumber)=[Forms]![frmClientReports]![cmbClientName]))
GROUP BY tblBranchReviewHistory.ClientNumber, tblBranchReviewHistory.BranchID
PIVOT tblReviewHistory.ReviewDate;

PARAMETERS [Forms]![frmClientReports]![cmbClientName] Text ( 255 );
SELECT tblBranchLocations.ClientNumber, tblBranchLocations.Address, tblBranchLocations.City, tblBranchLocations.State, tblBranchLocations.[Zip Code], qryBranches.*
FROM tblBranchLocations LEFT JOIN qryBranches ON (tblBranchLocations.BranchID=qryBranches.BranchID) AND (tblBranchLocations.ClientNumber=qryBranches.ClientNumber)
WHERE (((qryBranches.ReviewDate)>=DateAdd("yyyy",-3,Now())) And ((tblBranchLocations.ClientNumber)=[Forms]![frmClientReports]![cmbClientName]));

Open in new window

0
 

Author Comment

by:nfstrong
ID: 35207900
That did work.

I've attached the results I get from the queries.  Is there a way to not display the ClientNumber and BranchID that show up from the first query? (columns F and G)
qryTest.xls
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35207985
The simplest way is to open the second query (non-crosstab) and hide those two columns.

There are probably several other ways to hide them, but the optimal answer might depend on how you are using this query.
0
 

Author Comment

by:nfstrong
ID: 35208038
Our users will be selecting a client from the list in the combo box on the form and then click the button to display this query.  I think if there was a way to remove or hide those two columns it would look cleaner.  Any thoughts?  
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35208067
I just posted that in my prior comment
0
 

Author Comment

by:nfstrong
ID: 35208116
I'm sorry, I guess I misunderstood your post.  I was responding to the second part of it with how I am using the query to determine the optimal answer.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35208197
I gave you the simplest method for hiding the columns.  Did you try it?

Only need to explore other methods if this one doesn't work.
0
 

Author Comment

by:nfstrong
ID: 35208220
While it does work, our users are not technical at all and I don't want them to have to try and hide those two columns everytime they have this query displayed.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35208236
If you hide the columns in the query and save the query, the columns stay hidden when it is opened.
0
 

Author Comment

by:nfstrong
ID: 35208261
Right, but they are in the query where we are selecting all columns because the crosstab query it is referencing will have the dates changed based on the client selected, so I guess I don't see how to only hide those two columns.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35208377

1

open the query, looking at the data

2

right click a column

3

select Hide Column

4

repeat steps 2 & 3 for the second column

5

save the query
0
 

Author Comment

by:nfstrong
ID: 35208416
I didn't think it would be that simple.  Thanks for all your help!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35208442
So that works, right?
0
 

Author Comment

by:nfstrong
ID: 35208451
It appears to.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question