mrmmickle1
Well-known Member
- Joined
- May 11, 2012
- Messages
- 2,461
My Current Query Produces Data like this:
Current Query:
I would like for the query to produce values that look like this:
Is there an easy way to change my query to do 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:
Code:
'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?