MS Access SQL Query Data Side By Side instead of Stacked

[mrmmickle1]

My Current Query Produces Data like this:

Access 2013
	A	B	C
1	Invoice Number	Invoice Total	GL_Account
2	CINV4413	$2,200.00	Supply: 20-05-509-5480
3	CINV4514	$1,750.00	Supply: 20-05-509-5480
4	CINV4574	$2,400.00	Supply: 20-05-509-5480
5	CINV4773	$1,500.00	Supply: 20-05-509-5480
6	CINV4844	$2,800.00	Supply: 20-05-509-5480
7	CINV4947	$3,900.00	Supply: 20-05-509-5480
8	CINV4413	$3,000.00	Freight: 20-05-509-5600
9	CINV4514	$49,000.00	Freight: 20-05-509-5600
10	CINV4574	$36,700.00	Freight: 20-05-509-5600
11	CINV4773	$38,100.00	Freight: 20-05-509-5600
12	CINV4844	$37,850.00	Freight: 20-05-509-5600
13	CINV4947	$39,700.00	Freight: 20-05-509-5600
Sheet1

Current Query:

'Both GL Accounts Together in Same Query
SELECT tblInvoiceReport.[Invoice Number], Sum(tblInvoiceReport.[Total Amount]) AS [Invoice Total], "Supply: 20-05-509-5480" AS GL_Account
FROM tblInvoiceReport
WHERE (((tblInvoiceReport.Item)<>"Processing Fee" And (tblInvoiceReport.Item) Not Like "Carrier*"))
GROUP BY tblInvoiceReport.[Invoice Number], "Supply";
UNION ALL
SELECT tblInvoiceReport.[Invoice Number],  SUM(tblInvoiceReport.[Total Amount]) AS [Invoice Total], "Freight: 20-05-509-5600" AS GL_Account
FROM tblInvoiceReport
WHERE tblInvoiceReport.Item LIKE "Carrier*" Or   tblInvoiceReport.Item = "Processing Fee"
GROUP BY tblInvoiceReport.[Invoice Number];

I would like for the query to produce values that look like this:

Access 2013
	A	B	C
1	Invoice Number	Supply: 20-05-509-5480	Freight: 20-05-509-5600
2	CINV4413	$2,200.00	$3,000.00
3	CINV4514	$1,750.00	$49,000.00
4	CINV4574	$2,400.00	$36,700.00
5	CINV4773	$1,500.00	$38,100.00
6	CINV4844	$2,800.00	$37,850.00
7	CINV4947	$3,900.00	$39,700.00
Sheet1

Is there an easy way to change my query to do this?

 

[Joe4]

Let's say that your query above in named "Query1". Create a new query based off of that one with this code:

SELECT 
    Query1.[Invoice Number], 
    Sum(IIf([GL_Account]="Supply: 20-05-509-5480",[Invoice Total],0)) AS [Supply: 20-05-509-5480], 
    Sum(IIf([GL_Account]="Freight: 20-05-509-5600",[Invoice Total],0)) AS [Freight: 20-05-509-5600]
FROM Query1
GROUP BY Query1.[Invoice Number];

 

[Joe4]

You can also do this more dynamically with a Cross-Tab Query. That code would look like this:

TRANSFORM Sum(Query1.[Invoice Total]) AS [SumOfInvoice Total]
SELECT Query1.[Invoice Number]
FROM Query1
GROUP BY Query1.[Invoice Number]
ORDER BY Query1.[GL_Account] DESC 
PIVOT Query1.[GL_Account];

 

[mrmmickle1]

Joe4,

Thanks, I ended up merging the two together! Works like a charm!! Really appreciate the help

SELECT Query1.[Invoice Number], Sum(IIf([GL_Account]="Supply: 20-05-509-5480",[Invoice Total],0)) AS [Supply: 20-05-509-5480], Sum(IIf([GL_Account]="Freight: 20-05-509-5600",[Invoice Total],0)) AS [Freight: 20-05-509-5600]
FROM (SELECT tblInvoiceReport.[Invoice Number], Sum(tblInvoiceReport.[Total Amount]) AS [Invoice Total], "Supply: 20-05-509-5480" AS GL_Account
FROM tblInvoiceReport
WHERE (((tblInvoiceReport.Item)<>"Processing Fee" And (tblInvoiceReport.Item) Not Like "Carrier*"))
GROUP BY tblInvoiceReport.[Invoice Number], "Supply"
UNION ALL
SELECT tblInvoiceReport.[Invoice Number],  SUM(tblInvoiceReport.[Total Amount]) AS [Invoice Total], "Freight: 20-05-509-5600" AS GL_Account
FROM tblInvoiceReport
WHERE tblInvoiceReport.Item LIKE "Carrier*" Or   tblInvoiceReport.Item = "Processing Fee"
GROUP BY tblInvoiceReport.[Invoice Number])  AS Query1
GROUP BY Query1.[Invoice Number];

 

[Joe4]

You're welcome!

Note my last post above, which shows another more dynamic way of doing it (so you aren't hard-coding those column headers in case they may ever be other values).

 

[mrmmickle1]

Thanks for the two methods. I'm fiddling around with the CrossTab Query Now.

 

[Joe4]

I'm fiddling around with the CrossTab Query Now

If you use the Crosstab Query Wizard, it will walk you through it (it is pretty straightforward). Just be sure to choose the SUM function for the mathematical function to apply to the Invoice Total field. I t will add an extra Total column in there, but if you then switch your query to Design View, you can easily remove that.

 

[mathematician]

Thanks for the two methods. I'm fiddling around with the CrossTab Query Now.

Replace Sheet1 with your table name

TRANSFORM Min(Sheet1.[Invoice Total]) AS [MinOfInvoice Total]
SELECT Sheet1.[Invoice Number]
FROM Sheet1
GROUP BY Sheet1.[Invoice Number]
PIVOT Sheet1.GL_Account;