MS Access 97 SQL help

[banjo ben]

Hi, I have a data table that looks like the following: -

fld_ID, fld_Foreign, fld_Date
12, 174, 17/12/2010
13, 174, 07/01/2011
14, 174, 12/01/2011
15, 174, 20/02/2011
16, 174, 28/02/2011
17, 174, 15/12/2010
18, 174, 07/01/2011
19, 174, 17/12/2010
20, 192, 31/01/2011
21, 192, 28/02/2011
22, 185, 01/12/2010
23, 192, 11/03/2011
24, 192, 11/01/2011
43, 195, 21/01/2011
44, 195, 28/01/2011
45, 195, 14/02/2011
46, 195, 28/02/2011
47, 195, 01/03/2011
48, 195, 20/03/2011
49, 195, 05/04/2011
50, 195, 20/01/2011

I am trying to return the last fld_ID before today grouped by fld_Foreign. So the results would look something like the following: -

fld_ID
14
22
24
43

The records are not always entered in chronological order, so this means I can't use the LAST function, and I tried using an extra query inbetween that is sorted by date so that the records appear in chronological order, but the LAST function still seems to pick up the records in the order from the main table. selecting only dates that occur before today is easy enough, likewise with the grouping on fld_Foreign. What I thought would be a very straight forward query is really starting to annoy me now. I am using MS Access 97.

Ben

 

[gemma-the-husky]

it isn't just an access 97 problem - it just isnt easy to do (not possible directly) . (note also that last doesn't do what you think, and is not so useful anyway)


So - given your date , if you have a query to find the unique values of the foreign_id, this will give you values 174, 185, 192 etc.

Now try a totals query - groupby foreign_id, and have min (or max) value of the date

this will give you say

174, 15/12/2010
185, 01/12/2010
192, 11/01/2011
195, 21/01/2011

however - what you cannot do is ALSO retrieve the id number of the record supplying the date. The only way to find that is to go back and a dlookup on the foreignid and date extracted above.

you would think putting expression in the totals query might work, but it doesn't - and obviously, putting the id as a groupby is no good either

It's a pain, but that appears to be the way a dbs works.

 

[banjo ben]

Hi Dave, thanks for the quick response. I was hoping to avoid the dlookup route as the multiple criteria sounded a bit daunting. I gave ti a go though, and am still not quite there yet. So now I have a query showing the following: -

fld_ID, fld_Date
174, 12/01/2011
185, 01/12/2010
192, 11/01/2011
195, 21/01/2011

Which is showing the correct foreign id's and dates. In a new query I created my dlookup but I must be doing something wrong: -

fld_New: DLookUp("fld_ID","tbl_Milestones","[fld_Foreign] = " & [fld_Foreign] & " And [fld_MilestoneDate] = #" & [fld_MilestoneDate] & "#")

The following is returned from this lookup

fld_New, fld_Date
, 12/01/2011
, 01/12/2010
, 11/01/2010
43, 21/01/2011
(note the empty spaces in the top 3 rows)

I tried a few variations in the lookup, like concatenating the values so there would only be one set of criteria, but still got nowhere.

Ben

 

[vbaInet]

This should do it (aircode):

SELECT [COLOR=Red][B]TableName[/B][/COLOR].fld_ID FROM TableName 
WHERE [B][COLOR=Red]TableName[/COLOR][/B].fld_ID IN (SELECT TOP 1 Q.fld_ID FROM [COLOR=Red][B]TableName[/B][/COLOR] AS Q WHERE Q.fld_Foreign = [B][COLOR=Red]TableName[/COLOR][/B].fld_Foreign AND Q.fld_Date < Date() ORDER BY Q.fld_Date DESC) 
ORDER BY [COLOR=Red][B]TableName[/B][/COLOR].fld_ID;

Amend the highlighted bits.

 

[banjo ben]

Thanks vbaInet,

This works great - and it is all contained within the one query. Thanks to Dave also - I managed to get a workaround for the dlookup using inner joins on the new query with the original table joining.

Cheers
Ben

 

[vbaInet]

Happy to help Ben!