Can't figure out a Query

[Jay]

Hi

I have a table with the following fields
- Month
- CarID
- Reg Plate
- Mileage Band
- Sale Price

And I am trying desperately to achieve something in a query but can't.

The table contains car sales data, as you may have gathered. What I am
trying to do is arrange the query so that car sales in the same month for
the same CarID, same mileage Band and consecutive plates are returned, along
with a calculated field giving the % difference in Price.

The 2 plates I am interested in are 2003 52 and 2003 03. I want to extract
all instances where there are sales for the same car/same month/same mileage
band on these plates & calculate the difference in Sale Price.

SO let's say 3 records have same Month,CarID,RegPlate & MileageBand with a
Reg Plate value of 2003 52.
And there are 2 records with same month,ID,RegPlate, & MileageBand with a
Reg Plate value of 2003 03.

I need the query to group & average the three 2003 52 Prices, group &
average the two 2003 03 price and give the % Price Difference in a
calculated field.

I'm afraid my query skills are just not up to this at the moment so would
really appreciate any help anyone could offer. It would save me so much
time doing it manually in Excel . As well as being an invaluable learning
opportunity.

Many thanks

Jason

I hope I've given enough details. If not please let me know. Cheers

 

[Ken Snell \(MVP\)]

Not sure I'm following you entirely here, but the base of the query would be
something like this, I believe:

SELECT Avg([Sale Price])
FROM TableName
GROUP BY [Month], CarID, RegPlate, MileageBand, [Reg Plate];

This query will group all records together where the records share the same
Month value, same CarID value, same RegPlate value, same MileageBand value,
and same Reg Plate value.

As for %Price difference, you'll need to explain how you want to calculate
that before we can suggest how to add that to the query.

Also, I note that you're using Month as the name of a field in a table. It
and many other words are reserved words in ACCESS and should not be used for
field names, etc. See these Knowledge Base articles for more information
about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

 

[Jay]

Many thanks for this Ken - I appreciate it. I know about reserved names &
the field name is actually SaleDateMonth, I was just trying to simplify me
explanation.

Regarding the price difference, I want the difference of the averages where
the Reg Plates are 2003 52 and 2003 03. SO if I was doing it in Excel would
use something like:

((2003 52_value/2003 03_value)-1)*100

Basically 2003 52 is the older car & 2003 03 is the newer car. And I'm
trying to calculate what % premium people are paying for the newer reg
plate.

My problem is I can figure out the grouping & average as you've detailed but
not how to select the averages for those reg plates & do the % difference
calculation.

I hope I've made a bit more sense

Thanks

Jason

 

[Ken Snell \(MVP\)]

Are the values 2003 52 and 2003 03 going to be fixed and always the same for
every time you run the query? If not, how will the query know which values
to use for the comparison? Or do you want the query to ask the user for the
two values?

--

Ken Snell
<MS ACCESS MVP>

Many thanks for this Ken - I appreciate it. I know about reserved names &
the field name is actually SaleDateMonth, I was just trying to simplify me
explanation.

Regarding the price difference, I want the difference of the averages
where
the Reg Plates are 2003 52 and 2003 03. SO if I was doing it in Excel
would
use something like:

((2003 52_value/2003 03_value)-1)*100

Basically 2003 52 is the older car & 2003 03 is the newer car. And I'm
trying to calculate what % premium people are paying for the newer reg
plate.

My problem is I can figure out the grouping & average as you've detailed
but
not how to select the averages for those reg plates & do the % difference
calculation.

I hope I've made a bit more sense

Thanks

Jason

Not sure I'm following you entirely here, but the base of the query would
be
something like this, I believe:

SELECT Avg([Sale Price])
FROM TableName
GROUP BY [Month], CarID, RegPlate, MileageBand, [Reg Plate];

This query will group all records together where the records share the
same
Month value, same CarID value, same RegPlate value, same MileageBand
value,
and same Reg Plate value.

As for %Price difference, you'll need to explain how you want to
calculate
that before we can suggest how to add that to the query.

Also, I note that you're using Month as the name of a field in a table.
It
and many other words are reserved words in ACCESS and should not be used
for
field names, etc. See these Knowledge Base articles for more information
about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18