Totals based on date value

[jacqui26]

Hi Everyone,

Thanks in advance for taking the time to bother to read this.

I have a question in access that is driving me mad, please note i have just started using and still don't understand it very well but what i think i need to do is basic.

Anyway so i have a table that has a date in it and paid receipts for that day. Each date can have many paid receipts. I would like to be able to total the daily receipts for every day.

Hopefully that makes sense.

Can anyone help me on this?

Thanks!!

[MMcCarthy]

Hi Jacqui

Without your table structure I can only give you an example based on a simple structure that should work. so assuming a table as follows:

tblReceipts

ReceiptID (PK) Autonumber
ReceiptDate    Date/Time
ReceiptAmt     Currency

The following query should give you the total receipts based on any date.

SELECT ReceiptDate, Sum(ReceiptAmt) as TotalPaid
FROM tblReceipts
GROUP BY ReceiptDate

The group by statement is what you need to get the total for each date. This is known as an aggregate query.

Mary

[jacqui26]

Hi Mary,

Thank you so much for taking the time to help me.

The table you have set up is exactly what i have but the query didn't work. It just displayed the amount figure.

I need it to add all amounts that have the same date.

Sorry, as i said very new to this and probably doing something wrong.

Thanks Again,
Jacqui

[MMcCarthy]

Hi Mary,

Thank you so much for taking the time to help me.

The table you have set up is exactly what i have but the query didn't work. It just displayed the amount figure.

I need it to add all amounts that have the same date.

Sorry, as i said very new to this and probably doing something wrong.

Thanks Again,
Jacqui

The query I gave you should have worked. Can you copy and paste in the SQL of the query you used. It might help if I had the correct field and table names

[jacqui26]

Hi Mary,

I actually just figured out what i had wrong and it is working perfectly.

Thank you again so much!!

[MMcCarthy]

Hi Mary,

I actually just figured out what i had wrong and it is working perfectly.

Thank you again so much!!

Glad you got it working :)

[jacqui26]

Hi Again Mary!

Not sure if you will see this but wondering if it is possible to make it so if the sum if left blank - ie. no paid receipts were made on that day if i still registers and enters a zero instead of excluding day completely?

Trusting that makes sense.

Cheers,
Jacqui

[MMcCarthy]

Hi Again Mary!

Not sure if you will see this but wondering if it is possible to make it so if the sum if left blank - ie. no paid receipts were made on that day if i still registers and enters a zero instead of excluding day completely?

Trusting that makes sense.

Cheers,
Jacqui

If there is no received amount on a date will that date be in the table as a record?

[jacqui26]

Yeh there would be because there are a few other fields that are linked from query to report that don't show up now if no paid receipt value is entered.

Eg: Date Paid Receipts (SUM)(as per last question) and also daily takings.

So if there are daily takings on the day they don't display because there are no paid receipts.

Does that make sense?

thanks,

Jacqui

[MMcCarthy]

OK this should work then ...

SELECT ReceiptDate, Sum(NZ(ReceiptAmt,0)) as TotalPaid 
FROM tblReceipts 
GROUP BY ReceiptDate

[jacqui26]

Thanks Mary but that didn't seem to work. Didn't seem to make any difference

This is my SQL - might help.

SELECT [Paid Receipts].Date, Sum(NZ([Paid Receipts].Amount,0)) AS [Total Daily Paid Receipts], [Takings Query].[Total Takings]
FROM [Takings Query] INNER JOIN [Paid Receipts] ON [Takings Query].Date = [Paid Receipts].Date
GROUP BY [Paid Receipts].Date, [Takings Query].[Total Takings]

Thanks,

Jacqui

[MMcCarthy]

Thanks Mary but that didn't seem to work. Didn't seem to make any difference

This is my SQL - might help.

SELECT [Paid Receipts].Date, Sum(NZ([Paid Receipts].Amount,0)) AS [Total Daily Paid Receipts], [Takings Query].[Total Takings]
FROM [Takings Query] INNER JOIN [Paid Receipts] ON [Takings Query].Date = [Paid Receipts].Date
GROUP BY [Paid Receipts].Date, [Takings Query].[Total Takings]

Thanks,

Jacqui

Yes that does make a difference. Change your inner join to a left join ...

SELECT [Paid Receipts].Date, Sum(NZ([Paid Receipts].Amount,0)) AS [Total Daily Paid Receipts], [Takings Query].[Total Takings]
FROM [Takings Query] LEFT JOIN [Paid Receipts] ON [Takings Query].Date = [Paid Receipts].Date
GROUP BY [Paid Receipts].Date, [Takings Query].[Total Takings]

[jacqui26]

Thank You thank You! It works!