Query 1

[Swi]

I have the following table.

ID jobnumber clientname stateabbr statecount entrydate entrymonth entryyear dprevenue
1 12345 Test1 MD 50 2/28/2012 02 2012 500.00
2 12345 Test1 PA 150 2/28/2012 02 2012 500.00
3 67890 Test2 VA 250 2/29/2012 02 2012 1000.00

I would like to return a recordset like this:

job # Client Mailing Quantity MD Mailing Quantity PA Mailing Quantity VA Mailing Quantity Total Revenue
12345 Test1 200 50 100 $500.00
67890 Test2 250 5 2 250 $1,000.00

Keep in mind that there could be other states that would be included in this query, not just MD, PA and VA.

I can not seem to think of a way to do all of this in a query.

Any help is greatly appreciated.

Thanks.



Swi

 

[dhookom]

Are you missing some data in the final display? Consider using TGML to format your posts so we can better understand your requirements.

Have you considered a crosstab query?


Duane
Hook'D on Access
MS Access MVP

 

[Swi]

dhookom,

Thank you for the reply. The top data set is from an Access database that I have received. The bottom data set is what I wish for the result set to look like. I am not sure how to tackle this one. I hope this is now formatted more clearly now.

Thanks again.


[tt]ID jobnumber clientname stateabbr statecount entrydate entrymonth entryyear dprevenue
1 12345 Test1 MD 50 2/28/2012 02 2012 500.00
2 12345 Test1 PA 150 2/28/2012 02 2012 500.00
3 67890 Test2 VA 250 2/29/2012 02 2012 1000.00

I would like to return a recordset like this:

Job # Client Mailing Quantity MD Mailing Quantity PA Mailing Quantity VA Mailing Quantity Total Revenue
12345 Test1 200 50 150 $500.00
67890 Test2 250 250 $1,000.00
[/tt]

Swi

 

[dhookom]

I'm not sure why you didn't respond to my question regarding a crosstab.

Create a crosstab query with JobNumber, ClientName, dprevenue, and Sum(StateCount) aa Row Headings. StateAbbr & " Mailing Quantity" as the column heading and Sum(StateCount) as the value.

Duane
Hook'D on Access
MS Access MVP

 

[Swi]

Ok, I will take a look into it.

Thanks.

Swi

 

[Swi]

Ok, here is what I have:

TRANSFORM Sum(stateinfo.statecount) AS SumOfstatecount
SELECT stateinfo.jobnumber AS [Job #], stateinfo.clientname AS [Client], stateinfo.dprevenue AS [Total Revenue], Sum(stateinfo.statecount) AS [Mailing Quantity]
FROM stateinfo
GROUP BY stateinfo.jobnumber, stateinfo.clientname, stateinfo.dprevenue
PIVOT stateinfo.statecount;

I am unsure as to how to append the state name to the summed columns.

Thanks.

Swi

 

[dhookom]

Read my previous reply on the column headings.

Duane
Hook'D on Access
MS Access MVP

 

[Swi]

Thanks! Got it working.

TRANSFORM Sum(stateinfo.statecount) AS SumOfstatecount
SELECT stateinfo.jobnumber AS [Job #], stateinfo.clientname AS Client, stateinfo.dprevenue AS [Total Revenue], Sum(stateinfo.statecount) AS [Mailing Quantity]
FROM stateinfo
GROUP BY stateinfo.jobnumber, stateinfo.clientname, stateinfo.dprevenue
PIVOT [StateAbbr] & " Mailing Quantity";

Swi

 

[Swi]

Ok, this is working beautifully. I would like however to export this to Excel as well. Right now I am dynamically building an HTML table to display on an ASP page which is working fine.

I however would like to automatically export the crosstab query to an Excel spreadsheet. I know you can do this with a SELECT INTO statement but it does not seem to be working with the query above.

Any help would be greatly appreciated. I want to stay away from the Excel Application object and CopyFromRecordset method if possible.

Thanks.

Swi

 

[dhookom]

What's wrong with simply exporting the query?


Duane
Hook'D on Access
MS Access MVP

 

[Swi]

This is going to end up being an application (possibly a web app).

Basically, I want the user to push a button and the query results export to an Excel sheet so that is why I wanted to do it within the SQL statement.

Swi

 

[dhookom]

You can set the contenttype in the ASP (or other) page with code like:

 Response.ContentType = "application/vnd.ms-excel"

Consider this Dynamic Excel Reports with ASP
.

Duane
Hook'D on Access
MS Access MVP

 

[Swi]

Thanks, looking into it...

Swi

 

[Swi]

This solution does not seem to be working on other browsers.

Is there any other way to do this strictly through SQL?

Thanks.

Swi

 

[dhookom]

I'm not well versed in other browsers or IIS or whatever.

If you want the results in Excel, just write a query and/or code to pull the data into Excel and format it.

Duane
Hook'D on Access
MS Access MVP

 

[Swi]

Thanks.

I just wrote it to a protected CSV format.

That way it will open in Excel by default anyway.

Thanks again.

Swi