asked on

MS Access 2010 Query inside Query problem

The database I am working with has just 1 large table (which I know is ridiculous but 1 step at a time here). I need to produce a single query that displays 3 calculations. It seems easy enough but I am clearly missing something because I keep getting prompted with a "Enter Parameter" error. Currently, I have created 2 separate queries that calculate "Total Payment" and "Total Charge" now the final query is where I am having an issue. It displays the "Total Payment" and "Total Charge" correctly however I cannot get it to subtract the two and display in a third column named "Difference". Please let me know if any further info is required. I attached the SQL code. Thank you in advance!!!!

Dale Fye

What do the first 2 queries look like?

clover10

ASKER

query 1:

SELECT DExtract.[Element], Sum(DExtract.[AmountTBCAPB]) AS [Total Charges]
FROM DExtract
GROUP BY DExtract.[Element];

Open in new window

query 2:

SELECT DExtract.[Element], DExtract.[ValTT], Sum(DExtract.[AmountTBCAPB]) AS [Total Payments]
FROM DExtract
GROUP BY DExtract.[Element], DExtract.[ValTT]
HAVING (((DExtract.[ValTT])="Payments"));

Open in new window

als315

Try this query:

SELECT DExtract.[Element], Sum(DExtract.[AmountTBCAPB]) AS [Total Charges] 
Sum(IIF(DExtract.[ValTT]="Payments", DExtract.[AmountTBCAPB], 0)) AS [Payments]  
Sum(IIF(DExtract.[ValTT]<>"Payments", DExtract.[AmountTBCAPB], 0)) AS [Difference] FROM DExtract
GROUP BY DExtract.[Element];

Open in new window

clover10

ASKER

That makes sense to me.. it gives me a "the SELECT statement includes a reserved word or an argument that is mispelled or missing, or the punctuation is incorrect" I am looking to see if I can find the error too

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clover10

ASKER

Thanks!! I think we're almost there.. I get an error now that says "Enter Parameter Value: DExtract.AmountTBCAPB"

als315

If you have table DExtract and field AmountTBCAPB in it, syntax seems correct now.
Can you upload sample DB with only few strings from your table (only this table, without other objects)?

als315

Sample DB
DbExtract.accdb

clover10

ASKER

It is huge.. so here is a subset
DExtract-Sample.xlsx

clover10

ASKER

I see your example makes perfect sense. I think the issue is there are multiple "AmountTBCAPB" lines per "Element". For the "Total Charges" I need everything in the "AmountTBCAPB" to add up per "Element". For the "Payments", I need everything in "AmountTBCAPB" added up where ValTT = "Payments". The final difference column, Total Charges - Payments. Does that make sense?

als315

I see one Payment is positive - is it correct?
Can you show expected result for your sample?
Try new sample
DbExtract.accdb

clover10

ASKER

Payment is not always positive, sometimes it is also negative

clover10

ASKER

DExtract-Sample.xlsx

clover10

ASKER

I got it now! Thanks so much!!!

clover10

ASKER

Perfect!! Exactly what I needed and fast!!