Select x from y where z=max and 1

[Danyul]

Hi All,

I have a table (mytable) that has the following columns:

ID EnhanceID Comment

ID is a numeric primary key
EnhanceID is numeric
Comment is text

I have a simple report ('myreport') that returns all of the comments for an 'EnhanceID' and the corresponding 'ID' value (each 'EnhanceID' may have many comments associated with it, each comment is uniquely identified by 'ID').

I'd like a sql query that returns the comment associated with the maximum value of 'ID' for a particular EnhanceID.

I'd like to get the value of 'EnhanceID' from text100 (a text box) on 'myreport'

I have tried in query design view, but can't build what I am after. My sql knowledge falls short as I don't know how to correctly state:

Select 'comment' from 'mytable' where 'EnhanceID' = 'myreport.text100' AND mytable.ID=MAX(ID).

Any help greatly appreciated.

Regards

Dan

 

[HarleyQuinn]

There are a number of ways to achieve this, one way would be to order the ID's descendingly and just take the top one. E.g.

SELECT TOP 1 Comment
FROM MyTable
WHERE EnhanceID=1234
ORDER BY ID DESC;

Hope this helps


HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Danyul]

Thanks HarleyQuinn, worked like a charm!

Based on what I've seen on the forum learning to use SQL more effectivley will save me a fair few lines of code, and save what little hair I have left!

Cheers

Dan

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Danyul]

Hello Again!

I assumed (and we all know what happens when you assume!), that when I substitued your code:

SELECT TOP 1 Comment
FROM MyTable
WHERE EnhanceID=1234
ORDER BY ID DESC;

with:

SELECT TOP 1 comment
FROM mytable
WHERE enhancementID= myreport.enhancementID
ORDER BY ID DESC;

it would work

- one problem, whilst the code does return the correct comment associated with the first EnhancementID on the report, it returns the same comment for the rest of the EnhancementIDs returned in the report.

i.e. it appears the value of myreport.enhancementID is not re-evaluated when deciding on the comment to return as the records are added to the report.

The unusual thing is that the enhancementID field is updating correctly with each record displayed, it just appears that access is not re-reading it when decinding on the comment to return.

How do I correct this?

FYI all of the fields are sitting in a detail section, if that makes a difference.

Thanks

Dan

 

[CBJOAT]

Dan
When I read through this, it seems like I am missing part of your problem. It sounds like you have a form, 'myreport', that has a text box on it, 'text100', and you are entering an enhancement ID in this textbox and trying to run a report for the most recent comment on that enhancement ID. But then it sounds like you are also trying to add additional records to the same report later (spooling your output). If that is the case, you need to remember that Access pulls data for a report at one time. If you need to spool your report data, then I suggest a new table specifically for the report and have your report pull from that table. Then you can delete the contents from that table when you are ready to start spooling for your next report.

The other way I read this was you want the latest comment for an enhancement ID based on records in a table. Here is what I put together but then I am missing the purpose for ‘text100’.
Create a new query called qryMaxIDPerEnhID
SELECT Max(myTable.ID) AS MaxOfID, myTable.EnhanceID
FROM myTable
GROUP BY myTable.EnhanceID;

Then join your new query with 'myTable' in the query for your report, qryMaxCommentForReport
SELECT myTable.ID, myTable.EnhanceID, myTable.Comment
FROM myTable INNER JOIN qryMaxIDPerEnhID ON (myTable.ID = qryMaxIDPerEnhID.MaxOfID) AND (myTable.EnhanceID = qryMaxIDPerEnhID.EnhanceID);

You can of course add additional criteria to limit the enhancement IDs that are returned.

 

[lespaul]

you mean this (typed untested):

Select comment from mytable where EnhanceID = myreport.text100 AND mytable.ID=(SELECT MAX(ID) FROM mytable where EnhanceID = myreport.text100)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Danyul]

Hi Guys,

Thanks for these replies

cheers

Dan