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

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.

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]));

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Dmunday" wrote:

> 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