Problem with numbers

[JvM]

Good morning,

I have a few queries with several rows with data. I want the query to select the last row with data.

The rows heave an increasing number from one till ....

Can anybody help me select the last row?

Thankxx

Jan

 

[Rich]

Look up Max or DMax

 

[JvM]

Do I put this in the criteria field?

 

[AncientOne]

use a totals query and select Max from the available options

 

[JvM]

I tried it, but still get all the rows and not just the last one...

 

[namliam]

This is the SQL you want:

SELECT * 
FROM [Your Table]
WHERE [Your ID]=(SELECT Max([your ID]) AS MaxOfID 
                 FROM [Your table]);

Note:
Your id = your increasing number from 1 till ... (autonumber or such)
Your table = the one table you want the last record from

* Its NOT a good idea to use spaces in your table/field names (forms/modules also not such a good idea) but since i have no clue as to your DB layout i thought i would take it into account.

Regards

 

[AncientOne]

I tried it, but still get all the rows and not just the last one...

Go into design view for your query. Select SQL view. Copy that and paste it into a post, so we can see where you're going wrong

 

[JvM]

SELECT [01_Maandomzet].Jaar, [01_Maandomzet].Maandnr, [01_Maandomzet].Maand, [01_Maandomzet].Maandomzet, [01_Maandomzet].Budget, IIf([maandomzet]>=[Budget],"ja",IIf([maandomzet]<[Budget]*0.9,"nee","bijna")) AS MRP
FROM 01_Maandomzet
GROUP BY [01_Maandomzet].Jaar, [01_Maandomzet].Maandnr, [01_Maandomzet].Maand, [01_Maandomzet].Maandomzet, [01_Maandomzet].Budget;

I want to see the last entry for Maandnr...

This is the month number.

Jan

 

[AncientOne]

You don't have the key field (the one that increases in value) included in your query. This should be the one with MAX in the Totals row if you want to show the last record.

 

[namliam]

Jan,

Greetings from Amsterdam !!!!

use:

SELECT 
    [01_Maandomzet].Jaar, 
    [01_Maandomzet].Maandnr, 
    [01_Maandomzet].Maand, 
    [01_Maandomzet].Maandomzet, 
    [01_Maandomzet].Budget, 
    IIf([maandomzet]>=[Budget],"ja",IIf([maandomzet]<[Budget]*0.9,"nee","bijna")) AS MRP 
FROM 
    01_Maandomzet 
Where 
    [01_Maandomzet].Jaar = (Select Max(Jaar) as MaxJaar from 01_maandomzet) AND
    [01_Maandomzet].Maandnr = (Select Max(maandnr) as MaxMaand) from 01_maandomzet)

Or leave out the bit about a jaar

I dont see you using any sum or avg here so i left out the group by bit.....

Regards

The mailman

 

[JvM]

Heh,

Thankx for the replies but I get a syntax error on the last piece
of sql.

It says : syntaxisfout in query expression [01_maandomzet].jaar = (selectMax(jaar) as Maxjaar from 01_maandomzet) and [01_maandomzet].maandnr = (select Max(maandnr) as MaxMaand) from 01_maandomzet.

When I take this bit out it runs stuck on the last where comment..

 

[AncientOne]

I understood JvM wanted the most recent record, to be searched on an autonumber in a saved query. In which case he could use either MAX or LAST

 

[JvM]

I tried that but it gives me all records from up to down sorted on highest number. But it doens't give me the last record..

 

[AncientOne]

That's because you are using grouping on other fields. Try either of these approaches;

Use a totals query and put LAST in each column of the criteria.

use a select query, sort descending on the autonumber field and select 1 in the Top Values property. This is probably the best way toi do it.

 

[namliam]

SELECT 
    [01_Maandomzet].Jaar, 
    [01_Maandomzet].Maandnr, 
    [01_Maandomzet].Maand, 
    [01_Maandomzet].Maandomzet, 
    [01_Maandomzet].Budget, 
    IIf([maandomzet]>=[Budget],"ja",IIf([maandomzet]<[Budget]*0.9,"nee","bijna")) AS MRP 
FROM 
    01_Maandomzet 
Where 
    [01_Maandomzet].Jaar = (Select Max(Jaar) as MaxJaar from 01_maandomzet) AND
    [01_Maandomzet].Maandnr = (Select Max(maandnr) as MaxMaand from 01_maandomzet)

LOL bracket to many after MaxMaand.... Try this...

P.S. Where are you at?

 

[Jon K]

I agree with AncientOne.

use a select query, sort descending on the autonumber field and select 1 in the Top Values property. This is probably the best way toi do it.

The query is:-
SELECT TOP 1 *
FROM [TableName]
ORDER BY [IncreasingNumber_field] DESC;

(just replace with the correct table name and field name)

 

[namliam]

Hmz, nice one as welll... Would not have thought of it myself....

Does sound to me to be the faster and/or better option...
Not sure tho on the speed bit... Something for Mile to test??

Regards

 

[Jon K]

You have raised a good point, Mailman.


I find that, as far as speed is concerned, the best approach is to move your subquery up as an independent query, join it back to the table in a second query, and index the IncreasingNumber_Field in the table.

qryMaxNum:-
SELECT Max([IncreasingNumber_Field]) AS MaxNum
FROM TableName;

qryTwo:-
SELECT TableName.*
FROM TableName INNER JOIN qryMaxNum ON [TableName].[IncreasingNumber_Field]=[qryMaxNum].[MaxNum];

In table design, index the IncreasingNumber_Field with "Yes (No Duplicates)".


When qryTwo is run against 330K+ records on my system, the result is almost instantaneous.

Your query using Max in a subquery benefits slightly from the index. The one using Top 1 does not benefit from the index at all and is the slowest.

However, without the index, the two-query approach is the slowest of the three.

 

[JvM]

Good morning to all,

I tried to go to the Rolling Stones yesterday and couldn't post an reply to all your effort.

The Stones didn't deliver, Mick had a cold...

But back to acces.

Namliam, thank you for that last bit of code, it works great!!!

The last three posts I do'n't understand. Maybey somebody can explain them to me.. I have to make a subquery?

Oh yeah, Namlian I'm in Geleen in Zuid Limburg..

Again thank you all for your effort.. You helped me excellend!!


Jan

 

[Jon K]

Glad Mailman's code works out perfectly for you.

The last three posts I do'n't understand. Maybey somebody can explain them to me.. I have to make a subquery?

In your query you have already used a subquery. It is the select statement in the brackets.

The last three posts discussed the speed of the three ways to return the last record (i.e. the one with the maximum number) from a very large table. Obviously you do not have a speed problem with your query. You can just ignore them.