# MS Access/SQL statement help needed **URL:** \ **Category:** Factual Questions\ **Created:** [November 18, 2004, 2:01pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459 "2004-11-18T14:01:34Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Stan\_Doubt](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@Stan\_Doubt](https://boards.straightdope.com/u/Stan_Doubt)\ **Post date:** [November 18, 2004, 2:01pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/1 "2004-11-18T14:01:34Z")
I need help with what I hope is a fairly easy Access question. I’ll give a very simplified example Of what I’m trying to do: I am a regional manager for 10 car dealerships that send me weekly inventory reports. I store the data in a table with 6 fields, **report\_ID, dealer, date, stock\_number, car\_make\_model, MSRP**. Each dealer uses a new **report\_ID** code each week and provides the info for each car in stock like so: report\_ID / Dealer / Date / stock\_number / car\_make\_model / Price 12345 / Fred / 110504 / 100001 / 2005 Ford Mustang /34,000 12345 / Fred / 110504 / 100002 / 2005 Ford Focus /16,000 24680 / Mike / 110504 / 970008 / 1998 Chevy Truck /11,000 and so on. Each **report\_ID** might have 20-100 cars associated with it, and there are duplicate values in the **Dealer** and **Date** fields. I want to create a query that will return **car\_make\_model** of the record with the maximum value for **price** for each **report\_ID** code. I can write a query with **report\_ID** and **Price** that gives the highest price by grouping by **report\_ID** and selecting Max in the total for **Price**. This returns the highest price for each **report\_ID**. How can I write a query that will return **car\_make\_model** associated with that record instead? Adding **car\_make\_model** to the above query returns every record, and I don’t know a simple way to specify, say, a criterion for **price** to select only those records with the maximum value for price, because the maximum value for price will change from **report\_ID** to **report\_ID**. Thanks in advance, Stan ---
**Author:** ![Metacom](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@Metacom](https://boards.straightdope.com/u/Metacom)\ **Post date:** [November 18, 2004, 2:25pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/2 "2004-11-18T14:25:25Z")
Can Access do nested queries? If so, you want something like: ```auto select report_id, car_make_model from your_table yt1 where price = (select max(price) from your_table yt2 where yt2.report_id = yt1.report_id) ``` That’ll work in Oracle, which is the RDBMS I know best. It has a few more features then access, though. ---
**Author:** ![TellMeI\_mNotCrazy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tellmei_mnotcrazy/32/3419_2.png) [@TellMeI\_mNotCrazy](https://boards.straightdope.com/u/TellMeI_mNotCrazy)\ **Post date:** [November 18, 2004, 3:06pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/3 "2004-11-18T15:06:01Z")
If you’re using the Query design interface, click on the Group by button, and for the Price field, change the value of the Group By drop-down to “Max” ---
**Author:** ![TellMeI\_mNotCrazy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tellmei_mnotcrazy/32/3419_2.png) [@TellMeI\_mNotCrazy](https://boards.straightdope.com/u/TellMeI_mNotCrazy)\ **Post date:** [November 18, 2004, 3:07pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/4 "2004-11-18T15:07:20Z")
Misunderstood the question, as usual. ---
**Author:** ![TellMeI\_mNotCrazy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tellmei_mnotcrazy/32/3419_2.png) [@TellMeI\_mNotCrazy](https://boards.straightdope.com/u/TellMeI_mNotCrazy)\ **Post date:** [November 18, 2004, 3:10pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/5 "2004-11-18T15:10:57Z")
Hopefully I’m understanding correctly now. Group first by report\_id, then group by car\_make\_model, which would give you the max of each car\_make\_model for each report ID ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [November 18, 2004, 5:07pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/6 "2004-11-18T17:07:32Z")
Method 1: two queries. Query1 should show the maximum price for each report ID. Then Query2 will join Query1 to the original table, linking on report ID and price. This will show each item in the table in which the price is the maximum price for that report ID. Method 2: one query. In the criteria field, use Access’s DMax function to filter out items with prices less than the maximum for that report ID: DMax("[price]", “table”, “[Report ID]=” & [Report ID]) If Method 2 sounds confusing, I highly recommend Method 1. 🙂 ---
**Author:** ![Iris](https://avatars.discourse-cdn.com/v4/letter/i/a5b964/32.png) [@Iris](https://boards.straightdope.com/u/Iris)\ **Post date:** [November 18, 2004, 5:34pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/7 "2004-11-18T17:34:01Z")
You probably know this already, but also make sure your Primary Key is not the Report ID since this field has duplicate records (let Access assign the Primary Key). I’ve made this mistake before. The queries suggested above should give you the results you’re looking for. ---
**Author:** ![Stan\_Doubt](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@Stan\_Doubt](https://boards.straightdope.com/u/Stan_Doubt)\ **Post date:** [November 18, 2004, 5:46pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/8 "2004-11-18T17:46:01Z")
Tell Me I’m not crazy, I think you still misunderstood what I need. If you are suggesting grouping by report\_ID then finding Max in car\_make\_model, it won’t work because the names of cars are text (string) values. Metacom, yes that sounds like exactly what I need to do. The SQL in Acess view looks a little different than what you’ve posted, but I’m working on it. It looks something like this (if the table with this data was called My\_Table): SELECT [My\_Table].record\_ID, Max([My\_Table].price) AS MaxOfprice, [My\_Table].car\_make\_model FROM [My\_Table] GROUP BY [My\_Table].record\_ID, [My\_Table].car\_make\_model HAVING ((“where [price]”=(SELECT [My\_Table].record\_ID, Max([My\_Table].price) AS MaxOfprice FROM [My\_Table] GROUP BY [My\_Table].record\_ID))); Access says my subquery can return more than one field without using EXISTS in the main query’s FROM clause. It wants me to revise the SELECT statement in the subquery to only select one field. If anyone else out there knows how to do this in Access or a SQL code that Access will recognize don’t be shy. Thanks again, Stan ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [November 18, 2004, 6:06pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/9 "2004-11-18T18:06:19Z")
**Metacom** ’s code should work in Access. Here’s my version (runs in Access2000): ```auto SELECT t1.reportid, t1.makemodel FROM My_Table AS t1 WHERE (((t1.price)=(select max(t2.price) from My_Table t2 where t2.reportid = t1.reportid))); ``` Change your query view to SQL with the same button you use to switch between Design and Datasheet, then paste it in. ---
**Author:** ![Stan\_Doubt](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@Stan\_Doubt](https://boards.straightdope.com/u/Stan_Doubt)\ **Post date:** [November 18, 2004, 6:15pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/10 "2004-11-18T18:15:36Z")
Thanks SCSimmons, your first method worked. I’d still like to learn how to do this without mucking up my DB with so many queries, though. So if some one could explain the DMax function or other nested/sub query functions it would be appreciated. Stan ---
**Author:** ![Stan\_Doubt](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@Stan\_Doubt](https://boards.straightdope.com/u/Stan_Doubt)\ **Post date:** [November 18, 2004, 6:42pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/11 "2004-11-18T18:42:39Z")
Thanks Redtail. Now I got it to work. I’m translating back and forth between this hypothetical example and my DB, so it’s taking me a minute to understand how this works in the big picture, but I think I am getting it. ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [November 18, 2004, 6:48pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/12 "2004-11-18T18:48:04Z")
**Stan** , (if you haven’t tried this already) you might try switching to the regular query view mode after pasting it in. If you’re used to doing everything in the Access query builder, that can make it a bit easier to understand what it’s doing. Good luck! ---
**Author:** ![Stan\_Doubt](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@Stan\_Doubt](https://boards.straightdope.com/u/Stan_Doubt)\ **Post date:** [November 18, 2004, 7:43pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/13 "2004-11-18T19:43:05Z")
OK, so t1 (and t2?) are kind of like “virtual tables” that are created for the purpose of picking the value? ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [November 18, 2004, 7:49pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/14 "2004-11-18T19:49:33Z")
> [@Stan Doubt](#): > > OK, so t1 (and t2?) are kind of like “virtual tables” that are created for the purpose of picking the value? Yes, this is called ‘aliasing’. You can do it in the Access query builder by right-clicking on the table/query in the upper window and changing the name in the properties. Access does this automatically if you add the same table/query twice to a query, giving the second copy an alias to tell them apart … ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [November 18, 2004, 8:07pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/15 "2004-11-18T20:07:03Z")
Right. It’s done that way so that you can link the table back to itself. You’re more-or-less running two queries against two separate copies of your table, and joining the results to get what you need. 1. Find the maximum price for a given reportID, then 2. Link that back to another copy of the table, so that you can pull the car\_make\_model for that price & reportID. Sorry about the change in column names between the other examples & mine - I _thought_ I’d fixed that before I posted it. **Metacom** did all the hard work - I just translated it a teeny bit for Access. I’m not very good at writing nested queries, I tend to get myself confused and tangled in knots. ---
**Author:** ![Stan\_Doubt](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@Stan\_Doubt](https://boards.straightdope.com/u/Stan_Doubt)\ **Post date:** [November 18, 2004, 9:11pm UTC](https://boards.straightdope.com/t/ms-access-sql-statement-help-needed/275459/16 "2004-11-18T21:11:30Z")
OK, so could I use aliasing to use the result of a query as part of an expression in another field of the same query? For example, to calculate the percentage of a dealer’s total inventory represented by the most expensive car? One part of the query would be to sum the price column for each record\_ID and the other part would be dividing the maximum price by said sum? Thanks again to all who posted to this thread. Does anyone have a recommendation for a SQL book for a novice? I’m kind of leery of those “Dummy” and “Idiot” books, but I’ll go with the flow if I hear that one is good.