Aggregating Data

[mfarley]

Hi there,
I'm new to access so please bear with me. I have a table with my customer name (text), the date they used the service (date), how many hours (number) they used and if they paid (yes/no). What I'd love is to get a query to give me a total number of paid and unpaid hours used for a given month for each customer.
So it would look like this:

customer name Paid Hours Unpaid Hours
customer X 5 10
customer Y 4 3
customer Z 5 0

I've used expressions within the query and got this:

customer name Paid Hours Unpaid Hours
customer X 5 0
customer X 0 10
customer Y 4 0
customer Y 0 3
customer Z 5 0



I've tried making an unpaid query and a paid query for a given month and then trying to union the two -- which failed miserably.

I'm sure this is straightforward and I'm just missing something. Some help would be greatly appreciated.

Thanks!!!

[JoeM]

Take a look at Aggregate (Totals) Queries in Access's help files. You can group on your Customer Name and Month, and Sum your Paid and Unpaid Hours fields.

[mfarley]

Hi JoeM,
Thanks for the response. I don't have a paid and unpaid hours fields. I just have an hours field and a check box for if the hours were paid or not. Do you have any other suggestions?

Thanks again!

[JoeM]

I don't have a paid and unpaid hours fields. I just have an hours field and a check box for if the hours were paid or not.

I thought you said you already created expressions to get this.

I've used expressions within the query and got this:

You can create a new Aggregate query based on that query, and sum on your calculated expressions.

[mfarley]

Sorry - I misunderstood you. And *yay* it worked great. Thanks again for all your help!

[JoeM]

Glad it worked out for you!