Query Help

[Jay]

I have a summary query where the records are grouped on the following
fields:

-SaleMonth
-Car
-MileageBand
-RegPlate

And the only other field, 'Price', is averaged.

The 'RegPlate' field can be one of two vales: 2002 52 or 2003 52

So what I'm trying to achieve is have a query which calculates the
difference between the 2002 52 average price and the 2003 52 average price
(for all records where the SaleMonth, Car & Mileage Band are the same)

For example let's say I have the following two records in my query as it
stands at the moment:

SaleMonth Car MileageBand RegPlate AvgPrice
-----------------------------------------------------------
January Ford Focus MileageBandA 2002 52 £5000
January Ford Focus MileageBandA 2003 52 £5500

I want my new query to calculate the % difference between the 2002 52 value
(£5000) and the 2003 52 vale (£5500), which is 10%.

If anyone could help I'd be extremely grateful.

Many thanks

Jason

 

[Ken Snell \(MVP\)]

Did you give up on our earlier thread?

 

[Jay]

Did you give up on our earlier thread?

Far from it Ken, I've been trying to find it, but can only find a response
from a 'Yaucana en Alaska' about something totally unrelated. I saved your
last response in a folder I keep for useful NG responses.

I tried out Thunderbird recently as a possible replacement for Entourage for
usenet and can only think that's a factor why I couldn't find your response.
(I have since deleted it). And I didn't want to presume your help by
mentioning you in my re-post or its title.



-Jay-

 

[Ken Snell \(MVP\)]

< g > I understand about "now where did that post go?"....

Here is my most recent reply in that thread:

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?

From what you've posted here, it would seem that they won't be fixed values
at all times....?

 

[Jay]

Hi Ken,

The Reg Plate field only contains 4 different values (in >25,000 records)
and I'm only interested in 2003 52 and 2003 03 (& the % diff, of course).
So I didn't want to get into parameter queries because even if I was
interested in querying based on the difference between the other Reg Plate
values, it's be just as easy to change the query (as there are only 4).

I hope that makes it clearer.

Any help you could provide with the query would be extremely welcome.

-Jay-

 

[Ken Snell \(MVP\)]

So, perhaps this type of complicated query (doing it all as one query here,
though you can split the subqueries into their own separate queryies and
then use those queries as source tables in the final, third query):

SELECT A.[Month], A.CarID, A.MileageBand,
(SELECT Avg(T.[Sale Price] AS AOne)
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") AS 2003_52_AvgPrice,
(SELECT Avg(W.[Sale Price] AS ATwo)
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03") AS 2003_03_AvgPrice,
((((SELECT Avg(T.[Sale Price] AS AOne)
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") /
(SELECT Avg(W.[Sale Price] AS ATwo)
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03"))-1)*100) AS PctPriceDiff
FROM TableName AS A;


What this query is doing is getting the average sale price for the records
where Month, CarID, and MileageBand stay the same for a value of Reg Plate =
"2003 52"; and then does the same for Reg Plate = "2003 03", and then it
calculates the percent price differential.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell \(MVP\)]

Typo in the query -- left out some closing parentheses:

SELECT A.[Month], A.CarID, A.MileageBand,
(SELECT Avg(T.[Sale Price]) AS AOne)
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") AS 2003_52_AvgPrice,
(SELECT Avg(W.[Sale Price]) AS ATwo)
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03") AS 2003_03_AvgPrice,
((((SELECT Avg(T.[Sale Price]) AS AOne)
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") /
(SELECT Avg(W.[Sale Price]) AS ATwo)
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03"))-1)*100) AS PctPriceDiff
FROM TableName AS A;
--

Ken Snell
<MS ACCESS MVP>

So, perhaps this type of complicated query (doing it all as one query
here, though you can split the subqueries into their own separate queryies
and then use those queries as source tables in the final, third query):

SELECT A.[Month], A.CarID, A.MileageBand,
(SELECT Avg(T.[Sale Price] AS AOne)
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") AS 2003_52_AvgPrice,
(SELECT Avg(W.[Sale Price] AS ATwo)
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03") AS 2003_03_AvgPrice,
((((SELECT Avg(T.[Sale Price] AS AOne)
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") /
(SELECT Avg(W.[Sale Price] AS ATwo)
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03"))-1)*100) AS PctPriceDiff
FROM TableName AS A;


What this query is doing is getting the average sale price for the records
where Month, CarID, and MileageBand stay the same for a value of Reg Plate
= "2003 52"; and then does the same for Reg Plate = "2003 03", and then it
calculates the percent price differential.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

The Reg Plate field only contains 4 different values (in >25,000 records)
and I'm only interested in 2003 52 and 2003 03 (& the % diff, of
course).
So I didn't want to get into parameter queries because even if I was
interested in querying based on the difference between the other Reg
Plate
values, it's be just as easy to change the query (as there are only 4).

I hope that makes it clearer.

Any help you could provide with the query would be extremely welcome.

-Jay-

 

[Jay]

Hi Ken,

Thanks a lot for this - I do appreciate it.

As I'm a bit of an SQL-novice (most of my queries have been built in Design
View) - am I right in thinking that to use the SQL you've posted I just
start a new query (design view), switch to SQL view & copy the statement as
you posted it?

Well anyway, that's what I did.

But first I copied my table & changed the field names to reflect the field
names you used in the query (that way I could use your statement with no
need to alter it in any way. I even changed the name of the table to
TableName

Well, anyway, I entered the statement and tried running it & got the
following error:

Syntax error. In query expression '(SELECT Avg(T.[Sale Price]) AS Aone)

I copied & pasted the statement direct so am not sure what to do.

Please could you advise about the error. And am in right in just pasting it
into SQL view of a new query (after adding the relevant table to the design
grid?).

If I can get this working it'd be a great boon as a lot of my query needs
are similar & if I can understand how this is done it would improve my
efficiency enormously (You wouldn't believe the hoops I've been through in
Excel to get the same results

Many thanks

Jason

 

[Ken Snell \(MVP\)]

You did the right things... I messed up with more typos... sorry!

SELECT A.[Month], A.CarID, A.MileageBand,
(SELECT Avg(T.[Sale Price]) AS AOne
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") AS 2003_52_AvgPrice,
(SELECT Avg(W.[Sale Price]) AS ATwo
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03") AS 2003_03_AvgPrice,
((((SELECT Avg(T.[Sale Price]) AS AOne
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") /
(SELECT Avg(W.[Sale Price]) AS ATwo
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03"))-1)*100) AS PctPriceDiff
FROM TableName AS A;
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks a lot for this - I do appreciate it.

As I'm a bit of an SQL-novice (most of my queries have been built in
Design
View) - am I right in thinking that to use the SQL you've posted I just
start a new query (design view), switch to SQL view & copy the statement
as
you posted it?

Well anyway, that's what I did.

But first I copied my table & changed the field names to reflect the field
names you used in the query (that way I could use your statement with no
need to alter it in any way. I even changed the name of the table to
TableName

Well, anyway, I entered the statement and tried running it & got the
following error:

Syntax error. In query expression '(SELECT Avg(T.[Sale Price]) AS Aone)

I copied & pasted the statement direct so am not sure what to do.

Please could you advise about the error. And am in right in just pasting
it
into SQL view of a new query (after adding the relevant table to the
design
grid?).

If I can get this working it'd be a great boon as a lot of my query needs
are similar & if I can understand how this is done it would improve my
efficiency enormously (You wouldn't believe the hoops I've been through in
Excel to get the same results

Many thanks

Jason

Typo in the query -- left out some closing parentheses:

SELECT A.[Month], A.CarID, A.MileageBand,
(SELECT Avg(T.[Sale Price]) AS AOne)
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") AS 2003_52_AvgPrice,
(SELECT Avg(W.[Sale Price]) AS ATwo)
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03") AS 2003_03_AvgPrice,
((((SELECT Avg(T.[Sale Price]) AS AOne)
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]="2003 52") /
(SELECT Avg(W.[Sale Price]) AS ATwo)
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]="2003 03"))-1)*100) AS PctPriceDiff
FROM TableName AS A;

 

[Jay]

Just wanted to post a *quick* thank you. I've forwarded your last post to my
work email a/c to try the query on my full database. I'll post how I got on
when I get home (Can't access usenet from work, miserable sods

MANY thanks

Jay

 

[Ken Snell \(MVP\)]

You're welcome; hope it goes well.

 

[Jay]

WOW!! I can't thank you enough Ken. The query worked perfectly. I had to
change the PctPriceDiff calculation slightly, as I wanted the % difference
*from* 2003 52 to 2003 03 i.e

2003 52 2003 03 PctPriceDiff
-----------------------------------
5000.00 5275.00 5.5%

Anyway, it was just a case of swapping them around in the calculated field.

You wouldn't believe how much time this will save me. And it's made me
realise - that, at work, to do the type of analysis required in the
time-scales given I NEED this level of SQL knowledge. I was doing the same
thing using 1000's of Concatenated lookuo functions in Excel, which,
considering the speed of excel (50 minutes to calculate a sheet!). My next
step is figuring how to have the user choose the two Reg Plate values (as
you touched upon previously).

Anyway I have so many questions - What are the T. A. W. AOne etc. Why does
the table used in the query in design view show as A. But I don't want to
impose on you or your time anymore but learn myself. I've tried parsing the
statement for my understanding but just don't have the knowledge yet.

So, my next question is: Can you recommend any good reference
books/training systems be it online, books whatever, to give me a solid
grounding in SQL to a level where I would be able to write this type of
query myself.

Gratefully yours,

Jay

 

[Ken Snell \(MVP\)]

A good starting point for SQL is "SQL Queries for Mere Mortals" by Hernandez
and Viescas. That will give you a good headstart. Those items that you
question are aliases, allowing you to give unique names to calculated fields
(otherwise, ACCESS / Jet will assign generic names to them).

As for asking the user for the plate values, this query will ask the user
for the first and second plate values:

SELECT A.[Month], A.CarID, A.MileageBand,
(SELECT Avg(T.[Sale Price]) AS AOne
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]=[Enter second plate value:]) AS 2ndPlate_AvgPrice,
(SELECT Avg(W.[Sale Price]) AS ATwo
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]=[Enter first plate value:]) AS 1stPlate_AvgPrice,
((((SELECT Avg(W.[Sale Price]) AS ATwo
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]=[Enter first plate value:]) /
(SELECT Avg(T.[Sale Price]) AS AOne
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]=[Enter second plate value:]))-1)*100) AS PctPriceDiff
FROM TableName AS A;


You can modify this setup to let the user select the plate values from combo
boxes on a form, and then let the query read the values from that form. Just
another way to approach it.

You're welcome!

 

[Jay]

Hi Ken,

I can't thank you enough. I've just ordered the book from Amazon. Can I just
ask you one more question Ken.

Am I right in thinking that you would have written that query as just one
SQL statement and not in Design View. Does design view then just translate
the SQL into the relevant columns with the apportioned section of SQL for
that column/field and that for queries of that complexity Design View would
rarely be used as such for the initial writing of the query.

Best Regards

Jay

PS. How does the MVP thing work? Can users feedback to Microsoft regarding
help they've received as I like to do so when someone has gone the extra
mile like you kindly have.

 

[Ken Snell \(MVP\)]

Acutally, I probably would have used both SQL and design view to create this
query if I were doing it in a database (don't tell anyone, but I wrote it
entirely in SQL without using the design window).

I probably would design one of the Avg subqueries in the design view so that
I could get the result I want. I then would go to SQL view and copy the
statement so that I could paste into a field for another query that is in
design view. And so on.

Otherwise, one could write this entirely in the SQL window, and then switch
back to design view. ACCESS will put things in the proper columns in that
view (may look quite a bit different).

Some queries cannot be done in design view (e.g., Union queries, nonequijoin
queries, and scuh), and must be built entirely in SQL view.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I can't thank you enough. I've just ordered the book from Amazon. Can I
just
ask you one more question Ken.

Am I right in thinking that you would have written that query as just one
SQL statement and not in Design View. Does design view then just
translate
the SQL into the relevant columns with the apportioned section of SQL for
that column/field and that for queries of that complexity Design View
would
rarely be used as such for the initial writing of the query.

Best Regards

Jay

PS. How does the MVP thing work? Can users feedback to Microsoft regarding
help they've received as I like to do so when someone has gone the extra
mile like you kindly have.

A good starting point for SQL is "SQL Queries for Mere Mortals" by
Hernandez
and Viescas. That will give you a good headstart. Those items that you
question are aliases, allowing you to give unique names to calculated
fields
(otherwise, ACCESS / Jet will assign generic names to them).

As for asking the user for the plate values, this query will ask the user
for the first and second plate values:

SELECT A.[Month], A.CarID, A.MileageBand,
(SELECT Avg(T.[Sale Price]) AS AOne
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]=[Enter second plate value:]) AS 2ndPlate_AvgPrice,
(SELECT Avg(W.[Sale Price]) AS ATwo
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]=[Enter first plate value:]) AS 1stPlate_AvgPrice,
((((SELECT Avg(W.[Sale Price]) AS ATwo
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]=[Enter first plate value:]) /
(SELECT Avg(T.[Sale Price]) AS AOne
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]=[Enter second plate value:]))-1)*100) AS PctPriceDiff
FROM TableName AS A;


You can modify this setup to let the user select the plate values from
combo
boxes on a form, and then let the query read the values from that form.
Just
another way to approach it.

You're welcome!

 

[Ken Snell \(MVP\)]

Hi Ken,

I can't thank you enough. I've just ordered the book from Amazon. Can I
just
ask you one more question Ken.

Am I right in thinking that you would have written that query as just one
SQL statement and not in Design View. Does design view then just
translate
the SQL into the relevant columns with the apportioned section of SQL for
that column/field and that for queries of that complexity Design View
would
rarely be used as such for the initial writing of the query.

Best Regards

Jay

PS. How does the MVP thing work? Can users feedback to Microsoft regarding
help they've received as I like to do so when someone has gone the extra
mile like you kindly have.

See for info about the MVP award. If you'd
like to provide feedback to my MVP lead, the easiest way would be for you to
post a reply to this thread, and then I'll forward it to him (I don't want
to give his email address in the public domain). OK? Thanks.

 

[Jay]

Hi Ken,

Thanks again for your invaluable help - the parameter aspect of the query
has greatly helped.

I don't want to be pest but just wanted to ask something. The query appears
to be returning the same record more than once. So the PctPriceDiff
calculates perfectly but the record will be duplicated (often more than
twice.)

I drilled into the data behind it to see if I could understand why, but
couldn't. I focused on one example.

I found a CarID with 4 SalePrice values on one Reg Plate & two values on the
other.(same MileageBand)
The averages were exactly as given in the query (as expected) but the record
was returned 5 times in the query output (exactly the same record).

If you could enlighten me that would be fantastic.

Jay

 

[Ken Snell \(MVP\)]

I think we just need to add a GROUP BY clause to the query:

SELECT A.[Month], A.CarID, A.MileageBand,
(SELECT Avg(T.[Sale Price]) AS AOne
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]=[Enter second plate value:]) AS 2ndPlate_AvgPrice,
(SELECT Avg(W.[Sale Price]) AS ATwo
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]=[Enter first plate value:]) AS 1stPlate_AvgPrice,
((((SELECT Avg(W.[Sale Price]) AS ATwo
FROM TableName AS W
WHERE W.[Month]=A.[Month] And W.CarID=A.CarID
And W.MileageBand=A.MileageBand
And W.[Reg Plate]=[Enter first plate value:]) /
(SELECT Avg(T.[Sale Price]) AS AOne
FROM TableName AS T
WHERE T.[Month]=A.[Month] And T.CarID=A.CarID
And T.MileageBand=A.MileageBand
And T.[Reg Plate]=[Enter second plate value:]))-1)*100) AS PctPriceDiff
FROM TableName AS A
GROUP BY A.[Month], A.CarID, A.MileageBand;

By adding the GROUP BY, we tell the query to just return one record for each
unique combination of values from Month, CarID, and MileageBand fields.
Previously, the query returned every record with that unique combination.
This should work for you now.

 

[Jay]

Thanks Ken, that's great. Did I mention I ordered the SQL for Mortals
book. I've also used this query as an example supporting a
rationale/request for SQL training at work - identifying the amount of
laborious Excel work which is saved through intelligent querying. And as
the data-sets we have to work with get bigger & bigger, Excel becomes
less of an option. So, you've been more help than you could imagine

Best Regards

Jay

 

[Ken Snell \(MVP\)]

Glad to hear it! Good luck!