• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

show all rows in crosstab query

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
nfstrong
Asked:
nfstrong
  • 18
  • 18
1 Solution
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
is the form open?
0
 
nfstrongAuthor Commented:
I've tried it with the form open and with the form closed and I get that error each time.
0
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
ok, I tried to use the BUILD function and it gave me the same syntax and the same error.
0
 
aikimarkCommented:
please paste the two queries that were the result of the build tweak
0
 
nfstrongAuthor Commented:

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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
I just posted that in my prior comment
0
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
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
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:
If you hide the columns in the query and save the query, the columns stay hidden when it is opened.
0
 
nfstrongAuthor Commented:
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
 
aikimarkCommented:

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
 
nfstrongAuthor Commented:
I didn't think it would be that simple.  Thanks for all your help!
0
 
aikimarkCommented:
So that works, right?
0
 
nfstrongAuthor Commented:
It appears to.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 18
  • 18
Tackle projects and never again get stuck behind a technical roadblock.
Join Now