Solved: Crosstab SQL syntax required

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:

Code:

---

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

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.

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

i.e. Replace Inner Join with Left
[VBA]
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;
[/VBA]

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

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.

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"

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.

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. ;)

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...

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.

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
[VBA]
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;
[/VBA]

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

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..

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. :(

MS Query right into a pivot chart?

anyways, here is the SQL to get all 9 groupnames as your column headers:
[VBA]
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;
[/VBA]

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

[VBA]

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;

[/VBA]

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

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

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.

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. :(

Quote:

---

Originally Posted by 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.

---

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.