Question about totaling queries.

[kjspear]

Hello everyone,

I have a question about queries. On my report is there a way to add another query to the code below;

=DSum("[PAID]","registration query","[PAID]")

This adds the PAID field in the registration query. But what I'm trying to do is get this to subtract an amount from another query. Something like;

=DSum("[PAID]","registration query","[PAID])-("[NOTPAID]","registration notpaid","[NOTPAID]")


This is in the properites on a text box on the report.

Any assistance would be appreciated.

Thanks
KJ

 

[dhookom]

Maybe you should tell us a little about your table structure. I assume your registrations are coming from just one table so I don't understand why you would need to DSum()s.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kjspear]

They are coming from one table. However, one query totals all records except for No Show Attendees. The other query fro the same table, totals ONLY No Show Attendees. The problem I'm having is that the first query totals all who have paid and not paid including the No Show Attendees. I want to exclude the No Show Attendees who haven't paid in the amount due field and just include them in the Paid field along with the rest of the attendees. The other attendees who haven't paid, I still would like to include them in the Not Paid field.

Thank you,
KJ

 

[dhookom]

If you provided the table structure and some sample records, I bet someone here could provide an answer using one query and one domain aggregate function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]