MS Access/SQL statement help needed

Factual Questions
1

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

2

Can Access do nested queries? If so, you want something like:



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.

3

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”

4

Misunderstood the question, as usual.

5

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

6

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. :slight_smile:

7

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.

8

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

9

Metacom’s code should work in Access. Here’s my version (runs in Access2000):



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.

10

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

11

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.

12

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!

13

OK, so t1 (and t2?) are kind of like “virtual tables” that are created for the purpose of picking the value?

14

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 …

15

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.

16

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.