Query to select a record with highest value (1 Viewer)

[DubaiDave]

Hi,

I have a table with identical groups of records except for the IssueNo.

Issue number increments for each instance of the record:

TitleID.........Issue
Title1...........001
Title1...........002
Title1...........003
Title2...........001
Title2...........002

How can I select:

Title1.......... 003
Title2...........002

i.e. the highest value of Issue for each Title?

Cheers

David

 

[neileg]

Use an aggregate query set to MAX on the Issue field.

 

[DubaiDave]

Use an aggregate query set to MAX on the Issue field.

Using

DMax("table","Issue")

selects the highest value ie:

Title1.......... 003

But I want:

Title1.......... 003
Title2...........002

I guess I need some sort of criteria, but I can't figure it out.

Dave

 

[Alansidman]

Select the Group By Icon, its the greek letter Sigma. Then in the issues field, click on the drop down next to Group By and select Max.

 

[boblarson]

Select the Group By Icon, its the greek letter Sigma. Then in the issues field, click on the drop down next to Group By and select Max.

For a visual:

 

[DubaiDave]

For a visual:

OK, getting there. The above selects the Max by Group. The end result I am trying to get to is a Query By Form where a check box on my form (cbLatestIssues) will cause the latest issue for each title to be shown.

If Check Box ticked:

Title1.......... 003
Title2...........002

If Check Box NOT ticked:

Title1...........001
Title1...........002
Title1...........003
Title2...........001
Title2...........002

I think I have to enter an expression in 'Field' and a criteria (based on the checkbox value) and Not Show. But I have not sussed it yet.

Dave



Dave

 

[Alansidman]

Try using Last and not Max in your Group By selection.

Alan

 

[Brianwarnock]

Try using Last and not Max in your Group By selection.

Alan

Whatever you do do not use LAST. There have been many discussions on here but all come to one conclusion, First and Last are unreliable.

Brian

 

[Brianwarnock]

It seems to me that you are using the checkbox value to either run an aggregate query or a simple Select query, I think that this can only be done using code.

If you don't want to use code then 2 command buttons one to run each query would be a simple answer.

Brian

 

[ByteMyzer]

Oh, for crying out loud, this is so simple! You're running around in circles when you could simply have provided the following answer:

(substitute highlighted text with the appropriate table/field names)

SELECT T1.[B][I]TitleID[/I][/B], MAX(T1.[B][I]Issue[/I][/B]) AS [B][I]Issue[/I][/B]
FROM [B][I]MyTable[/I][/B] T1
GROUP BY T1.[B][I]TitleID[/I][/B];

DubaiDave, on behalf of Access World Forums, I'd like to apologize for the runaround you've been getting on this thread. We are generally more straightforward with our answers, and I hope that this has not been a complete excercise in frustration for you.

 

[Brianwarnock]

Get off your high horse ByteMyzer, he has been told how to do that bit, then he added a supplentary that you have not addressed.

Brian

 

[ByteMyzer]

Get off your high horse ByteMyzer, he has been told how to do that bit, then he added a supplentary that you have not addressed.

Brian

Yes, Brian, he has been told how to make an aggregate query. Pardon me for taking a shortcut and posting the actual SQL syntax.

As for "getting off" my "high horse", I was never on it; I was simply protesting the length this thread had grown to without a straightforward answer. I am sure I am not the only member of this forum who finds such a scenario frustrating, and I do not apologize for my remark.

However, you're right, I did not address the checkbox question, so here is the SQL syntax, accounting for the checkbox requirement:

(substitute highlighted text with the appropriate table/field names)

SELECT T1.[b][i]TitleID[/i][/b], T1.[b][i]Issue[/i][/b]
FROM MyTable T1
WHERE [Forms]![[b][i]MyForm[/i][/b]]![[b][i]cbLatestIssues[/i][/b]] = False
OR ([Forms]![[b][i]MyForm[/i][/b]]![[b][i]cbLatestIssues[/i][/b]] = True
AND T1.[b][i]Issue[/i][/b] =
(SELECT MAX(T2.[b][i]Issue[/i][/b])
 FROM MyTable T2
 WHERE T2.[b][i]TitleID[/i][/b] = T1.[b][i]TitleID[/i][/b]));

 

[Brianwarnock]

I didn't expect you to apologise, arrogant people rarely do. However Bob and Neil deserved better, they feel that it is better that they point people in the right direction and let them explore and learn rather than be spoonfed, especially with such basic skills, and as for Alan , as a newish member he must wonder if its worth the effort if he is only going to get insulted.

Get one fact straight, the thread did not go on long because the poster was not told what to do.

Brian

 

[DubaiDave]

Dear Posters one and all,

I appreciate all help and anybody that takes the time to reply. If its the wrong help and it dosn't work; I will have still learnt something.

I appologise if I have phrased my question poorly and led to a breaksdown of the US-UK 'special' relationship.

However, I still need to ask a further question as I am still unsure of the solution...

ByteMyzer posted an SQL query, but I am embaressed to say I am not entirely sure how to use it.

I have a query with many expressions built using Build facility that enable me to select various records based on date and author etc.

As expressed in my earlier post, I also want to be able to select only the latest Issue for each TitleID.

Do I paste ByteMyzer's SQL query into the Field part of the query and set Show to not show?

David

 

[MSAccessRookie]

Dear Posters one and all,

I appreciate all help and anybody that takes the time to reply. If its the wrong help and it dosn't work; I will have still learnt something.

I appologise if I have phrased my question poorly and led to a breaksdown of the US-UK 'special' relationship.

However, I still need to ask a further question as I am still unsure of the solution...

ByteMyzer posted an SQL query, but I am embaressed to say I am not entirely sure how to use it.

I have a query with many expressions built using Build facility that enable me to select various records based on date and author etc.

As expressed in my earlier post, I also want to be able to select only the latest Issue for each TitleID.

Do I paste ByteMyzer's SQL query into the Field part of the query and set Show to not show?

David

When you are in the Design view for a query, if you look across the top of the window at the menu list, the third menu is called "View" On this menu is an option named "SQL View". Choosing this option will show you the SQL Code that makes up the query. It is here that you would apply the code supplied by ByteMyzer.

 

[Brianwarnock]

You need not be embarassed, many newbies do not understand SQL.
When a query is built using the design grid, ACCESS builds the SQL. To see the SQL select View and then SQL from the dropdown or use the toggle arrow on the icon toolbar, far left usually.

BytMyzers SQL is a complete query, to use only that and no other fields you would copy and paste it over any code already there, or into a blank query, tho' you must select a record source before switching to SQL view.
Remember to replace the names he has used by the ones relative to your situation.

I hope the above makes sense atleast when you try it.

If you need to add the solution to an existing query then it may be worth copying and pasting your SQL onto the forum if you cannot work it out yourself.

Brian

Edit I see Accessrookie has already replied.

 

[DubaiDave]

Thanks MSAccessRookie,

If I look at the SQL view, there is a really complex query going on (built I guess by the multiple other expressions in my design view). I would not know where to insert ByteMyzer's SQL code. I am now wrestling with trying to build the expression in design view. Failing miserably so far.

Dave

 

[MSAccessRookie]

Thanks MSAccessRookie,

If I look at the SQL view, there is a really complex query going on (built I guess by the multiple other expressions in my design view). I would not know where to insert ByteMyzer's SQL code. I am now wrestling with trying to build the expression in design view. Failing miserably so far.

Dave

Can you show us the code that is driving your query? perhaps someone can assist you in the transition

 

[KenHigg]

I'd suggest staying away from the sql code until you get a bit more proficent with databases in general, unless you do have the time now to go off on that tangent (That stuff gets real criptic real fast). And instead use the query builder grid like you started off doing... Just my 2 cents

 

[DubaiDave]

Thanks Brian,

Yes I see what you and MSAccessRookie mean.

But there must be a way of putting this together using expression builder, isn't there?

For example to filter on the status of Titles (see first posting) and a status being open or closed, I did the following:

[RfiStatus]=[Forms]![fmRFI_QBF]![cboStatus] Or [Forms]![fmRFI_QBF]![cboStatus] Is Null

where cboStatus is a combo that can be open or closed.

Total is expression and Criteria is True

Is there not a similar method for what I am trying to achieve (that generates SQL and adds it to the rest of my query behind the scenes)?

Dave