Date Query Problem

[Graham1972]

Hello All,

I'm having a problem with a date query.

I need the query to search though a table field call period e.g 30/9/2002 - 6/10/2002 and just chose the most recent date entrys.

The query needs to automatically chose the most recent dates as it populates various reports.

Cheers

 

[EndersG]

Create an aggregate query where it groups the records by the date field. Save it as Query1. Then use Query1 as a subquery in a new query (Query2) and group it on the date field using the aggregate function, LAST

 

[Rich]

Should be Max on the date field

 

[EndersG]

I stand corrected. Thanks Max

 

[EndersG]

I mean Rich.

 

[Jon K]

> .... a table field call period e.g 30/9/2002 - 6/10/2002 <


Is Period a text field containing text such as:-
30/9/2002 - 6/10/2002
14/10/2002 - 20/10/2002 ?

Or is it a date/time field containing individual dates?


And for what field do you want to choose the most recent entries?
For example, the most recent entry for each company in the CompanyName field.

 

[Rich]

You could of course also use the Top values property to return more than one recent date per company

 

[Jon K]

Rich,

From the following table tblSales (in which Date is a date/time field):-

SaleMemo ------ Date -- StaffID - Company ------ Amount
1 ------------ 7/7/02 -- S001 ---- ABC Company -- $500.00
2 ------------ 8/8/02 -- S001 ---- ABC Company -- $400.00
3 ------------ 9/9/02 -- S006 ---- ABC Company -- $300.00
4 --------- 10/10/02 -- S006 ---- XYZ Company -- $200.00
5 --------- 11/11/02 -- S003 ---- XYZ Company -- $100.00

I was able to return the most recent entry for each company (i.e. records of SaleMemo #3 and #5) using this query:-

SELECT *
FROM tblSales as a
WHERE [Date] = (Select Max([Date]) from tblSales where Company=a.Company)


I tried Top 1. It seemed to apply on the whole query result and I couldn't use it to return the most recent record for each company.

Jon

 

[Rich]

The easiest way is to display the query result via a subform/report, that way you can set criteria reference to say employee ID and return the Top say3 per employee, I do know that Raskew had a similar problem which I think was solved via a query at Elk but that site's gone now so he may post the answer here

 

[Graham1972]

Date Query problem

HI all,

Thanks for yor replys, but I still can't get it too work. The problems being that the table field"Period" is a text field.

I have created a query using a "Left" expression that will isolate the beginning of the week(e.g of 30/9/02 - 6/10/02= isolate 30/9/02). But due to the fact it is a text field a max query won't pull out the most recent date.

Any further elucidations would be greatly appreciated

G

 

[Jon K]

Use CDate() to change the text returned by the left function to date, e.g.

cDate(left(Period, instr(Period, " - ")-1))

 

[Graham1972]

Thanks John,

Works like a dream, much appreciated

G