Need top value for each group

[Mari]

Hi,

I have two linked tables with a one-to-many relationship.

TABLE1
Model Number (primary key)
type
Comments


TABLE2
Model Number (foreign key)
Order Number
Order Date
Projected Delivery Date

I would like to run a query that will return the records of only the nearest
upcoming projected delivery date for each model number.

Any suggestions?

Thanks in advance.

m-

 

[Douglas J. Steele]

SELECT Table1.[Model Number],
Max(Table2.[Projected Delivery Date]) AS LatestDate
FROM Table1 INNER JOIN Table2
ON Table1.[Model Number] = Table2.[Model Number]
GROUP BY Table1.[Model Number]

Either create a new query, go into its SQL View (on the View menu) and type
that, or else build a query the normal way, joining the two tables, then
convert it to a Totals query (also on the View menu)

 

[John Spencer]

Since you said nearest UPCOMING delivery date, you might need to modify
Douglas Steele's suggested query a bit.

Assumptions:
Upcoming date means date after today.
Nearest means the earliest upcoming date after today.

SELECT Table1.[Model Number],
Min(Table2.[Projected Delivery Date]) AS NextDeliveryDate
FROM Table1 INNER JOIN Table2
ON Table1.[Model Number] = Table2.[Model Number]
WHERE Table2.[Projected Delivery Date] > Date()
GROUP BY Table1.[Model Number]


And since Model Number is in Table2, you could probably drop the JOIN and
end up with

SELECT Table2.[Model Number],
Min(Table2.[Projected Delivery Date]) AS NextDeliveryDate
FROM Table2
WHERE Table2.[Projected Delivery Date] > Date()
GROUP BY Table2.[Model Number]

SELECT Table1.[Model Number],
Max(Table2.[Projected Delivery Date]) AS LatestDate
FROM Table1 INNER JOIN Table2
ON Table1.[Model Number] = Table2.[Model Number]
GROUP BY Table1.[Model Number]

Either create a new query, go into its SQL View (on the View menu) and
type that, or else build a query the normal way, joining the two tables,
then convert it to a Totals query (also on the View menu)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I have two linked tables with a one-to-many relationship.

TABLE1
Model Number (primary key)
type
Comments


TABLE2
Model Number (foreign key)
Order Number
Order Date
Projected Delivery Date

I would like to run a query that will return the records of only the
nearest
upcoming projected delivery date for each model number.

Any suggestions?

Thanks in advance.

m-

 

[Douglas J. Steele]

Valid points, John.

Thanks for the assist.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Since you said nearest UPCOMING delivery date, you might need to modify
Douglas Steele's suggested query a bit.

Assumptions:
Upcoming date means date after today.
Nearest means the earliest upcoming date after today.

SELECT Table1.[Model Number],
Min(Table2.[Projected Delivery Date]) AS NextDeliveryDate
FROM Table1 INNER JOIN Table2
ON Table1.[Model Number] = Table2.[Model Number]
WHERE Table2.[Projected Delivery Date] > Date()
GROUP BY Table1.[Model Number]


And since Model Number is in Table2, you could probably drop the JOIN and
end up with

SELECT Table2.[Model Number],
Min(Table2.[Projected Delivery Date]) AS NextDeliveryDate
FROM Table2
WHERE Table2.[Projected Delivery Date] > Date()
GROUP BY Table2.[Model Number]

SELECT Table1.[Model Number],
Max(Table2.[Projected Delivery Date]) AS LatestDate
FROM Table1 INNER JOIN Table2
ON Table1.[Model Number] = Table2.[Model Number]
GROUP BY Table1.[Model Number]

Either create a new query, go into its SQL View (on the View menu) and
type that, or else build a query the normal way, joining the two tables,
then convert it to a Totals query (also on the View menu)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I have two linked tables with a one-to-many relationship.

TABLE1
Model Number (primary key)
type
Comments


TABLE2
Model Number (foreign key)
Order Number
Order Date
Projected Delivery Date

I would like to run a query that will return the records of only the
nearest
upcoming projected delivery date for each model number.

Any suggestions?

Thanks in advance.

m-

 

[Ken Sheridan]

If you want to return additional columns other than the model number and
projected delivery date you can do so by using a correlated subquery to
restrict the outer query, e.g.

SELECT Table1.[Model Number], Type, Comments,
[Order Number], [Order Date], [Projected Delivery date]
FROM Table1 INNER JOIN Table2
ON Table1.[Model Number] = Table2.[Model Number]
WHERE [Projected Delivery Date] =
(SELECT MIN([Projected Delivery Date])
FROM Table2
WHERE Table2.[Model Number] = Table1.[Model Number]
AND [Projected Delivery Date] > DATE());

You'll see that the subquery is correlated with the outer query on the model
number. The rows returned by the outer query are thus restricted to those
where the projected delivery date equals the date returned by the subquery,
i.e. the earliest projected delivery date for the model number after the
current date.

Ken Sheridan
Stafford, England

 

[Mari]

Thanks to all who replied. As soon as I can wrap my brain around the
answers, I will try it out and post my success.

Thanks!

m-

If you want to return additional columns other than the model number and
projected delivery date you can do so by using a correlated subquery to
restrict the outer query, e.g.

SELECT Table1.[Model Number], Type, Comments,
[Order Number], [Order Date], [Projected Delivery date]
FROM Table1 INNER JOIN Table2
ON Table1.[Model Number] = Table2.[Model Number]
WHERE [Projected Delivery Date] =
(SELECT MIN([Projected Delivery Date])
FROM Table2
WHERE Table2.[Model Number] = Table1.[Model Number]
AND [Projected Delivery Date] > DATE());

You'll see that the subquery is correlated with the outer query on the model
number. The rows returned by the outer query are thus restricted to those
where the projected delivery date equals the date returned by the subquery,
i.e. the earliest projected delivery date for the model number after the
current date.

Ken Sheridan
Stafford, England

Hi,

I have two linked tables with a one-to-many relationship.

TABLE1
Model Number (primary key)
type
Comments


TABLE2
Model Number (foreign key)
Order Number
Order Date
Projected Delivery Date

I would like to run a query that will return the records of only the nearest
upcoming projected delivery date for each model number.

Any suggestions?

Thanks in advance.

m-