By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,331 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Multiple Date Query

P: 2
Access 2007 - I have a table containing 6 dates (date, date1, date2, etc. There are payments associated with these dates (payment1, payment 2, ect). I want to write a query that will total all payments for a specific date range by entering a [Start Date] And [End Date] once to cover all six dates and payments. I wrote a query using payment1, payment2, .....sum and date [start date] and [end date]. It doesn't return all the payments (but it does return some)in the table for date1, date2,etc. Should I include all the date fields? Will the user be required to enter start date and end date for all six? Must be an easier way.
Feb 23 '11 #1
Share this Question
Share on Google+
2 Replies

P: 83
Show us the query that you tried.
Your reference to fields such as payment1, payment2... and date1, date2... suggest non normalized data (table structure issues)
Feb 24 '11 #2

P: 2
Here's the query
SELECT [RPC Hazards Payments].[Contract Number], Sum([RPC Hazards Payments].Payment1) AS SumOfPayment1, Sum([RPC Hazards Payments].Payment2) AS SumOfPayment2, Sum([RPC Hazards Payments].Payment3) AS SumOfPayment3, Sum([RPC Hazards Payments].Payment4) AS SumOfPayment4, Sum([RPC Hazards Payments].Payment5) AS SumOfPayment5, Sum([RPC Hazards Payments].Payment6) AS SumOfPayment6
FROM [RPC Hazards Payments]
WHERE ((([RPC Hazards Payments].Date) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [RPC Hazards Payments].[Contract Number];

These tables were originally imported from excel and I didn't want to have to rebuild the database - I'm certainly open to suggestions - still learning -thanks
Feb 24 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.