Solved: Crosstab SQL syntax required

[Ken Puls]

Hey guys,

Not strictly a VBA question, but this will be leveraged via VBA on an Access db from Excel...

I have a crosstab SQL query that looks liket this:


TRANSFORM Sum(tblInvDetail.Amount) AS SumOfAmount
SELECT tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber
FROM tblExpGroups INNER JOIN ((tblSuppliers INNER JOIN tblInvoices ON tblSuppliers.SupplierID = tblInvoices.SupplierID)
INNER JOIN (tblExpType INNER JOIN tblInvDetail ON tblExpType.ExpTypeID = tblInvDetail.ExpTypeID) ON tblInvoices.InvID = tblInvDetail.InvID)
ON tblExpGroups.ExpGroupID = tblExpType.ExpGroupID
WHERE (((tblInvoices.InvDate)>=#2/1/2006# And (tblInvoices.InvDate)<=#2/5/2006#) AND ((tblInvDetail.DeptID)=150))
GROUP BY tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber, tblInvDetail.DeptID
ORDER BY tblExpGroups.ExpGroupName
PIVOT tblExpGroups.ExpGroupName;

Problem is that it is only returning my columns for tblExpType.ExpGroupName where there is actualy data in one of the records. This makes sense, and normally is what most people would want, but there are only 13 records in that table, and I need them all to show every time. If nothing is in the column, then fill it with blank or zeros, I don't care, but I need those headers. Reason for the above is that I'm trying to drop it into a static Excel table, and I don't want my data always shifting.

Is there a statement in SQL that you can add to always show all columns in a crosstab? http://vbaexpress.com/forum/images/smilies/102.gif

[Ken Puls]

Figured that was hard to read, so I'd add a picture...

In the example below, we haven't bought any "Paper" on those invoices, so the "Paper" column doesn't show. That's what I'd like to fix.

[XLGibbs]

Ken change the join to a Left join to show all records.

i.e. Replace Inner Join with Left

TRANSFORM Sum(tblInvDetail.Amount) AS SumOfAmount
SELECT tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber
FROM tblExpGroups LEFT JOIN ((tblSuppliers INNER JOIN tblInvoices ON tblSuppliers.SupplierID = tblInvoices.SupplierID)
LEFT JOIN (tblExpType LEFT JOIN tblInvDetail ON tblExpType.ExpTypeID = tblInvDetail.ExpTypeID) ON tblInvoices.InvID = tblInvDetail.InvID)
ON tblExpGroups.ExpGroupID = tblExpType.ExpGroupID
WHERE (((tblInvoices.InvDate)>=#2/1/2006# And (tblInvoices.InvDate)<=#2/5/2006#) AND ((tblInvDetail.DeptID)=150))
GROUP BY tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber, tblInvDetail.DeptID
ORDER BY tblExpGroups.ExpGroupName
PIVOT tblExpGroups.ExpGroupName;

[Ken Puls]

Thanks, Pete! I'll give that a shot tomorrow AM. :)

[XLGibbs]

Sure Ken! You have to kind of "picture" the tables from Left to right. The position of the table you want all records from requires the "side" join instead of an inner join, but you can't uintermingle lefts and rights. Either all Left, all Right..from what you stated it looks like Left. The very fist one may need to be Left Outer , followed by Left...Left also may work.

[Ken Puls]

Hey Pete,

I can't get this to work with any combination of Left or Left Outer Joins. It keeps telling me that the "Join Expression is Not Supported"

[XLGibbs]

It is kind of a nasty join, I didn't notice the sub joins inside at first glance. If this is in access, can't you just use the query designer to change the joins?

In the meantime, I will retype the syntax based on what i see. I live in SQL all day, so I know that the joins are supported, it is just a matter of lining them up right.

[Ken Puls]

LOL

I probably could, only I don't know Access real well. How does one change a Join Type in the query designer? :dunno

Honestly, I've looked and couldn't figure it out. ;)

[XLGibbs]

You should see the joins in the picture. Double click the line representing the join and the rest is obvious, 3 choices....

Let me know. The query designer spits out lots of goofy looking syntax on joins, I was rewriting it the way a normal person would....

You just can't intermingle joins. Position the table you want All records from on the Left, and make all the other tables flow that way...when you are done changing it..the line will be an arrow....the arrows should all go the same way...

[Ken Puls]

Okay, found the joins. I made them all go one way, and ended up with a mixture of Left and Right Joins in the SQL syntax... which would be great if it actually worked. I'm still getting the same results:

If I pull up all records for February, I get all records in the db (as I should), which means all columns are present. If I limit those to only pull results for Dept 150, though, I only get the columns that they have used, and the rest just hide.

I'm attaching a copy of the db. The query is there.

[XLGibbs]

Well, if you want all the records from tblExpType, you need to have that has your "primary table". The column headings were based on the expGroupName, of which there were fewer...

Here is the SQL I came up with

TRANSFORM Sum(tblInvDetail.Amount) AS SumOfAmount
SELECT tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber
FROM tblExpGroups RIGHT JOIN ((tblSuppliers RIGHT JOIN tblInvoices ON tblSuppliers.SupplierID = tblInvoices.SupplierID) RIGHT JOIN (tblExpType LEFT JOIN tblInvDetail ON tblExpType.ExpTypeID = tblInvDetail.ExpTypeID) ON tblInvoices.InvID = tblInvDetail.InvID) ON tblExpGroups.ExpGroupID = tblExpType.ExpGroupID
WHERE (((tblInvoices.InvDate)>=#2/1/2006# And (tblInvoices.InvDate)<=#2/28/2006#))
GROUP BY tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber, tblInvDetail.DeptID
ORDER BY tblExpType.ExpTypeName
PIVOT tblExpType.ExpTypeName;


Attached is the rewrite. I like your naming syntax. Much of my work is named that way. qry_Idiot, qry_Stupid, qry_Wontworkever etc..

[XLGibbs]

Whoops I may have taken out the '150' criteria on that one field by mistake, but the query produced the 13 columns I think you wanted..

[Ken Puls]

Hi Pete,

Yep, actually needs to be based on the ExpGroupName. What I'm trying to do is summarize all the invoice details into the 9 Expense Group Types. I need to take that info, drop it into a static Excel table so that I can chart it vs other data. :(

[XLGibbs]

MS Query right into a pivot chart?

anyways, here is the SQL to get all 9 groupnames as your column headers:

TRANSFORM Sum(tblInvDetail.Amount) AS SumOfAmount
SELECT tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber
FROM tblExpGroups LEFT JOIN ((tblSuppliers RIGHT JOIN tblInvoices ON tblSuppliers.SupplierID = tblInvoices.SupplierID) RIGHT JOIN (tblExpType LEFT JOIN tblInvDetail ON tblExpType.ExpTypeID = tblInvDetail.ExpTypeID) ON tblInvoices.InvID = tblInvDetail.InvID) ON tblExpGroups.ExpGroupID = tblExpType.ExpGroupID
WHERE (((tblInvoices.InvDate)>=#2/1/2006# And (tblInvoices.InvDate)<=#2/28/2006#))
GROUP BY tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber, tblInvDetail.DeptID
ORDER BY tblExpType.ExpTypeName
PIVOT tblExpType.ExpTypeName;

[Ken Puls]

You're going to hate this... It's still pulling ExpType not ExpGroup as headers... :dunno

[XLGibbs]

TRANSFORM Sum(tblInvDetail.Amount) AS SumOfAmount
SELECT tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber
FROM tblExpGroups LEFT JOIN ((tblSuppliers RIGHT JOIN tblInvoices ON tblSuppliers.SupplierID = tblInvoices.SupplierID) RIGHT JOIN (tblExpType LEFT JOIN tblInvDetail ON tblExpType.ExpTypeID = tblInvDetail.ExpTypeID) ON tblInvoices.InvID = tblInvDetail.InvID) ON tblExpGroups.ExpGroupID = tblExpType.ExpGroupID
WHERE (((tblInvoices.InvDate)>=#2/1/2006# And (tblInvoices.InvDate)<=#2/28/2006#))
GROUP BY tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber, tblInvDetail.DeptID
ORDER BY tblExpGroups.ExpGroupName
PIVOT tblExpGroups.ExpGroupName;



Ooops. Wrong notepad. http://vbaexpress.com/forum/images/smilies/doh.gif

[XLGibbs]

for easier criteria, you can also use Between #date# and #date# which is inclusive ...

[XLGibbs]

PS, I hope you are not copy/pasting this data into excel if it is for a recurring report. It is my goal to eliminate cut/paste from the universe as it applies to Access and Excel.

[Ken Puls]

Good to know on the between part. :)

When you ad a department as a criteria (try 150), though, you still loose some of the column headers. :(

[Ken Puls]

PS, I hope you are not copy/pasting this data into excel if it is for a recurring report. It is my goal to eliminate cut/paste from the universe as it applies to Access and Excel.

It's a long story, but it basically boils down to the fact that I can't afford Access licenses. I'm building an Excel front end for specific users to do certain things with my data.

What I'm doing is this...

I am querying an Access DB from Excel via ADO. The query will be structured to be dynamic for department, start date and to date. The data will also be matched up with revenue data to map a theoretical Flash Food cost for a varying periods of time for one... and later combined... departments.

Everything runs into temporary files in Excel from the db and are thrown away at close, but Excel is my reporting engine.

[XLGibbs]

Yeah, there is not matching data on Type ...

Why do not use MS Query and feed a select query to a pivot table, you can format the table to be "static" by removing the "save data with table" and "enable drill down" check marks...

at leat that way you have the "show items with no data option"

I can't seem to configure the joins to get them all either, although intuitevely, I know beyond all doubt it CAN be done...but it may need to be done with a control table of group names that does not rely on a join to exp type...strange...I do so much of this with SQL server, it seems odd I can't get it to go with access...

[XLGibbs]

You can also structure the pivot table to always have the same exact structure no matter which dept ID you choose. And you can make nifty interactive charts by pressing the chart button on the PT toolbar.

This I am sure you already know, but for the benefit of our visitors.

[XLGibbs]

It's a long story, but it basically boils down to the fact that I can't afford Access licenses. I'm building an Excel front end for specific users to do certain things with my data.

What I'm doing is this...

I am querying an Access DB from Excel via ADO. The query will be structured to be dynamic for department, start date and to date. The data will also be matched up with revenue data to map a theoretical Flash Food cost for a varying periods of time for one... and later combined... departments.

Everything runs into temporary files in Excel from the db and are thrown away at close, but Excel is my reporting engine.



Seems like a good candidate for the .udl file trick as well..
Use a template excel file which has the links via ADO connections and built the results of the select query into a pivot table via code....or feed them into a table on a hidden sheet, feeding the pivot table dynamically. The query table can always be there in the template file, and can accept parameters. I just built a similar front end at work...all they see is the result, but i have a query table/pivot table arrangement to do all the math and sorting behind the scenes.

I have them input dates which then get fed to the location of my parameter cells on the hidden sheet, requeries the database with those parameters and they have their report....I do at least one of these types of setups a week of late....so if you need ANY help, I am on a little vacation this week from the office....


Also explains why you needed the SQL syntax... :)

[Ken Puls]

Hmmm....

Retrieval into a pivot table, eh? I guess I could do that. I prefer the sorting options on the regular methods if I can, but I can make that work. :)

[XLGibbs]

What I do is feed the data into a table, which is dynamically linked to a fixed position and structure pivot table. The data in it's source is unique to the parameters provided, and the PT only has the summary information...in this case it sounds like the sum of amounts in the 9 categories.

[Ken Puls]

Right, okay.

See, what I was going for was similiar, but trying to leverage SQL to do some of that dirty work for me. Rather than dump raw data and reformat it with a pivot table, I was going to have it dump me a listting of the data previously summarized so that the pivot table was unnecessary. It seemed like the easiest way to go.

I'm actually generating multiple reports off of this one query. One piece is a summarized view of what we bought from these suppliers, and another is the actual food cost percentage of revenue, which will obviously need additional source data. There's not a ton of difference between the regular and summarized columns, so I could always just hard code it in Excel, but I was trying for some flexibility going forward is all.

I hate little hurdles like this. LOL!

[XLGibbs]

Problem is the crosstab has no "show items with no data" option like the pivot table, so you would need a series recursive pre-built queries to populate the "missing" data....
since some dept IDs have no expType matching....I have tried a number of differnt things, but the cross tab requires at least one cell in the result set to be populated with a value.

[XLGibbs]

Ken, here is an option...

A select query with your criteria as one result set:



SELECT tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber, tblExpGroups.ExpGroupName, IIf(Sum([Amount]) Is Null,0,Sum([Amount])) AS Total, tblInvDetail.DeptID
FROM tblExpGroups LEFT JOIN ((tblSuppliers RIGHT JOIN tblInvoices ON tblSuppliers.SupplierID = tblInvoices.SupplierID) RIGHT JOIN (tblExpType LEFT JOIN tblInvDetail ON tblExpType.ExpTypeID = tblInvDetail.ExpTypeID) ON tblInvoices.InvID = tblInvDetail.InvID) ON tblExpGroups.ExpGroupID = tblExpType.ExpGroupID
GROUP BY tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber, tblExpGroups.ExpGroupName, tblInvDetail.DeptID
HAVING (((tblInvoices.InvDate) Between #2/1/2006# And #2/28/2006#) AND ((tblInvDetail.DeptID)=150));


and then your cross tab query doing a LEft join on the group names to that result set



TRANSFORM Sum([stupid query].Total) AS SumOfTotal
SELECT [stupid query].SupplierName
FROM tblExpGroups LEFT JOIN [stupid query] ON tblExpGroups.ExpGroupName = [stupid query].ExpGroupName
GROUP BY [stupid query].SupplierName, [stupid query].InvDate, [stupid query].InvNumber
PIVOT tblExpGroups.ExpGroupName;


Unfortunately to get all records with a crosstab a recursive query is necessary. The SQL to do it all in one is particularly nasty, and I just about had it before my daughter turned off the computer.

[Bob Phillips]

How about


TRANSFORM Sum(tblInvDetail.Amount) AS SumOfAmount
SELECT tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber
FROM tblExpGroups INNER JOIN ((tblSuppliers INNER JOIN tblInvoices ON tblSuppliers.SupplierID = tblInvoices.SupplierID)
INNER JOIN (tblExpType INNER JOIN tblInvDetail ON tblExpType.ExpTypeID = tblInvDetail.ExpTypeID) ON tblInvoices.InvID = tblInvDetail.InvID)
ON tblExpGroups.ExpGroupID = tblExpType.ExpGroupID
WHERE (((tblInvoices.InvDate)>=#2/1/2006# And (tblInvoices.InvDate)<=#2/5/2006#) AND ((tblInvDetail.DeptID)=150))
GROUP BY tblSuppliers.SupplierName, tblInvoices.InvDate, tblInvoices.InvNumber, tblInvDetail.DeptID
ORDER BY tblExpGroups.ExpGroupName
PIVOT tblExpGroups.ExpGroupName In
("Cleaning","GST","Non Alc","Smallwares","Food","Paper");


increase the list to all options.

[Ken Puls]

How about

...

increase the list to all options.

:thumb

Thanks, Bob! That's it!

I'd prefer to make the list dynamic, in case a new catagory is added, but I'll live with it. I tried putting a SELECT query in there, but, as you probably know, it didn't work. I'm still feeling my way through SQL. :)

Thanks!

[Bob Phillips]

:thumb

Thanks, Bob! That's it!

I'd prefer to make the list dynamic, in case a new catagory is added, but I'll live with it. I tried putting a SELECT query in there, but, as you probably know, it didn't work. I'm still feeling my way through SQL. :)

Thanks!

Not fully dynamic, but how about creating a table of valid values, and query that.

[XLGibbs]

Ken you can make that list dynamic (can't believe I didnt think of this option..)

[VBA]
PIVOT tblExpGroups.ExpGroupName In (Select expGrouName from tblExpGroups);

[Ken Puls]

I tried it, but that's why I made my comment above. It seems to be thinking that is some kind of expression or something?