SQL [newbie]

[Jon]

The following query retrieves all prices from a table:

SELECT Price_Data.Date_Quote, Price_Data.[Model&Variant_ID],
Car_Model_Variants.Power, Car_Model_Variants.Options, Price_Data.Price,
Price_Data.Clerk
FROM Price_Data INNER JOIN Car_Model_Variants ON
Price_Data.[Model&Variant_ID] = Car_Model_Variants.[Model&Variant_ID]

I would like to retrieve the price where Price_Data.Quote =
MAX(Price_Data.Date_Quote)

Results
Model&Variant_ID | Power | Options | Price | Clerk

Any ideas?

 

[Jerry Whittle]

SELECT Price_Data.Date_Quote,
Price_Data.[Model&Variant_ID],
Car_Model_Variants.Power,
Car_Model_Variants.Options,
Price_Data.Price,
Price_Data.Clerk
FROM Price_Data INNER JOIN Car_Model_Variants
ON Price_Data.[Model&Variant_ID] =
Car_Model_Variants.[Model&Variant_ID]
WHERE Price_Data.Quote =
DMax("[Date_Quote]", "Price_Data") ;

 

[Jon]

When I tried this I am being prompted for the following parameters:

PD2.Model
Variant_ID
PD1.Model


Any idea why?

Do you mean the latest quote per model_variant? If so:

SELECT PD1.Date_Quote, PD1.[Model&Variant_ID],
Car_Model_Variants.Power, Car_Model_Variants.Options, PD1.Price,
PD1.Clerk
FROM Price_Data AS PD1 INNER JOIN Car_Model_Variants ON
PD1.[Model&Variant_ID] = Car_Model_Variants.[Model&Variant_ID]
WHERE PD1.Date_Quote =
(SELECT MAX(Date_Quote)
FROM Price_Data AS PD2
WHERE PD2.Model&Variant_ID = PD1.Model&Variant_ID);

Ken Sheridan
Stafford, England

The following query retrieves all prices from a table:

SELECT Price_Data.Date_Quote, Price_Data.[Model&Variant_ID],
Car_Model_Variants.Power, Car_Model_Variants.Options, Price_Data.Price,
Price_Data.Clerk
FROM Price_Data INNER JOIN Car_Model_Variants ON
Price_Data.[Model&Variant_ID] = Car_Model_Variants.[Model&Variant_ID]

I would like to retrieve the price where Price_Data.Quote =
MAX(Price_Data.Date_Quote)

Results
Model&Variant_ID | Power | Options | Price | Clerk

Any ideas?

 

[Douglas J. Steele]

Due to the special characters in the field names, you MUST put square
brackets around them:

WHERE PD2.[Model&Variant_ID] = PD1.[Model&Variant_ID]);

Most of us try and avoid using special characters (including space) in
names.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I tried this I am being prompted for the following parameters:

PD2.Model
Variant_ID
PD1.Model


Any idea why?

Do you mean the latest quote per model_variant? If so:

SELECT PD1.Date_Quote, PD1.[Model&Variant_ID],
Car_Model_Variants.Power, Car_Model_Variants.Options, PD1.Price,
PD1.Clerk
FROM Price_Data AS PD1 INNER JOIN Car_Model_Variants ON
PD1.[Model&Variant_ID] = Car_Model_Variants.[Model&Variant_ID]
WHERE PD1.Date_Quote =
(SELECT MAX(Date_Quote)
FROM Price_Data AS PD2
WHERE PD2.Model&Variant_ID = PD1.Model&Variant_ID);

Ken Sheridan
Stafford, England

The following query retrieves all prices from a table:

SELECT Price_Data.Date_Quote, Price_Data.[Model&Variant_ID],
Car_Model_Variants.Power, Car_Model_Variants.Options, Price_Data.Price,
Price_Data.Clerk
FROM Price_Data INNER JOIN Car_Model_Variants ON
Price_Data.[Model&Variant_ID] = Car_Model_Variants.[Model&Variant_ID]

I would like to retrieve the price where Price_Data.Quote =
MAX(Price_Data.Date_Quote)

Results
Model&Variant_ID | Power | Options | Price | Clerk

Any ideas?

 

[robin]

Due to the special characters in the field names, you MUST put square
brackets around them:

WHERE PD2.[Model&Variant_ID] = PD1.[Model&Variant_ID]);

Most of us try and avoid using special characters (including space) in
names.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I tried this I am being prompted for the following parameters:

PD2.Model
Variant_ID
PD1.Model


Any idea why?

Do you mean the latest quote per model_variant? If so:

SELECT PD1.Date_Quote, PD1.[Model&Variant_ID],
Car_Model_Variants.Power, Car_Model_Variants.Options, PD1.Price,
PD1.Clerk
FROM Price_Data AS PD1 INNER JOIN Car_Model_Variants ON
PD1.[Model&Variant_ID] = Car_Model_Variants.[Model&Variant_ID]
WHERE PD1.Date_Quote =
(SELECT MAX(Date_Quote)
FROM Price_Data AS PD2
WHERE PD2.Model&Variant_ID = PD1.Model&Variant_ID);

Ken Sheridan
Stafford, England

Jon wrote:
The following query retrieves all prices from a table:

SELECT Price_Data.Date_Quote, Price_Data.[Model&Variant_ID],
Car_Model_Variants.Power, Car_Model_Variants.Options, Price_Data.Price,
Price_Data.Clerk
FROM Price_Data INNER JOIN Car_Model_Variants ON
Price_Data.[Model&Variant_ID] = Car_Model_Variants.[Model&Variant_ID]

I would like to retrieve the price where Price_Data.Quote =
MAX(Price_Data.Date_Quote)

Results
Model&Variant_ID | Power | Options | Price | Clerk

Any ideas?