Date conversion to period

[Guest]

I currently have a table (Periods) which holds the data Period,Financial
Year, Start Date and End Date. I also have a table (Payments Analysis) which
holds data invoice number, invoice date, value and paid date.

I want to use a query on the table (Payments Analysis) that will give me the
period number and financial year against any paid date.

Thanks in advance.

Derek

 

[Beth F.]

Derek,
Assuming your date ranges do not overlap in the periods table, try
this:

SELECT PaymentsAnalysis.PaidDate, periods.period, periods.financialYear
FROM periods, PaymentsAnalysis
WHERE PaymentsAnalysis.PaidDate BETWEEN periods.startdate AND
periods.enddate

Beth F.

 

[Guest]

SELECT [Payments Analysis].[invoice number],
[Payments Analysis].[invoice date],
[Payments Analysis].[value],
[Payments Analysis].[paid date],
Periods.period,
Periods.[Financial Year]
FROM [Payments Analysis], Periods
WHERE ((([Payments Analysis].[paid date])
BETWEEN Periods.[start date]
AND Periods.[end date]));