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.
-
-
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) -
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 -thanksComment
Comment