Solved: Crosstab SQL syntax required

[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:


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

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


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

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

[vba]
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");
[/vba]

increase the list to all options.

[Ken Puls]

How about

...

increase the list to all options.

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]

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?