Combine 2 queries,and Group Fields

[SunnyInCO]

Hello everyone,
Fist time poster here and an Access newbie. I was given the task to create a large database for my management team and need some assistance. I do not know any programming language and not even sure if my question belongs in the "Queries" thread so please direct me in the correct area if needed.

I have a query with the following fields: ReportDate, Location, Vendor, Amount, Source. Source will either contain "AP" or "INV". For most of the ReportDate,Location,Vendor combination have 2 lines, an amount for "AP" and one for "INV".



What I am trying to do for reporting reasons (Excel Pivot Table) is to make one line for the combination(Date,Loc,Vendor) with two new fields "Total AP" and "Total INV" and the amount populate into the corresponding new "Total..." field depending if the source say "AP" or "INV". Confusing, I know so here is an example below:

Current Format:
ReportDate Location Vendor Total Source
1/1/2011 1610 100 $40.00 AP
1/1/2011 1610 100 $30.00 INV
1/1/2011 1610 200 $16.00 AP
1/1/2011 1610 200 $20.00 INV

Goal Format:
ReportDate Location Vendor AP Total INV Total
1/1/2011 1610 100 $40.00 $30.00
1/1/2011 1610 200 $16.00 $20.00

I know who to create the “AP Total” column and “INV Total” columns in a query and us a IIF statement to bring in the correct value based on the Source value. The original source of “AP” and “INV” are from two separate tables then combined into one table using a UNION ALL. The is the only SQL statement I have used.

Any suggestions are appreciated or if I need to post more info please let me know.

[pbaldy]

You can use a totals query with fields like:

IIf(Source = "AP", Total, 0)

So your sum would only be totaling up values with the appropriate code.

[SunnyInCO]

Thanks Paul however I have tried that but the query looks like this:

ReportDate Location Vendor AP Total INV Total
1/1/2011 1610 100 $40.00
1/1/2011 1610 100 (blank) $30.00
1/1/2011 1610 200 $16.00 (blank)
1/1/2011 1610 200 (blank)$20.00

I am trything to get one line for each vendor and an amount in each of the two totals for a total of two line.

Thanks

[pbaldy]

Yes, that's why I said to use a totals query. Try:

SELECT ReportDate, Location, Vendor, Sum(IIf(Source = "AP", Total, 0)) As TotalAP, Sum(IIf(Source = "INV", Total, 0)) As TotalINV
FROM TableName
GROUP BY ReportDate, Location, Vendor

[SunnyInCO]

WOW, it worked. I swear I tried something like this however I was using the query designer, not an SQL statement.

Thank you very much Paul.

[pbaldy]

Happy to help, and welcome to the site by the way! You can use the query designer; you'd have to click on the "Totals" icon on the ribbon (looks like a Greek "E") to see the row containing Group By, Sum, etc.