Solved

show all rows in crosstab query

Posted on 2011-03-21
37
460 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now