 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006Query Help
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 Did you give up on our earlier thread?  SignatureKen Snell <MS ACCESS MVP> >I have a summary query where the records are grouped on the following > fields: [quoted text clipped - 29 lines] > > Jason On 9/7/06 12:59, in article eC7PV80oGHA.4184@TK2MSFTNGP04.phx.gbl, "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote: > 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- < 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....? SignatureKen Snell <MS ACCESS MVP> > On 9/7/06 12:59, in article eC7PV80oGHA.4184@TK2MSFTNGP04.phx.gbl, "Ken > Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote: [quoted text clipped - 15 lines] > > -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- > < g > I understand about "now where did that post go?".... > [quoted text clipped - 7 lines] > From what you've posted here, it would seem that they won't be fixed values > at all times....? 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. SignatureKen Snell <MS ACCESS MVP> > Hi Ken, > [quoted text clipped - 25 lines] >> values >> at all times....? 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; SignatureKen 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 [quoted text clipped - 58 lines] >>> values >>> at all times....? 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 On 9/7/06 22:39, in article OQEOTA6oGHA.524@TK2MSFTNGP05.phx.gbl, "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote: > Typo in the query -- left out some closing parentheses: > [quoted text clipped - 20 lines] > And W.[Reg Plate]="2003 03"))-1)*100) AS PctPriceDiff > FROM TableName AS A; 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; SignatureKen Snell <MS ACCESS MVP> > Hi Ken, > [quoted text clipped - 64 lines] >> And W.[Reg Plate]="2003 03"))-1)*100) AS PctPriceDiff >> FROM TableName AS A; 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 On 11/7/06 00:11, in article #sXviYHpGHA.2452@TK2MSFTNGP03.phx.gbl, "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote: > You did the right things... I messed up with more typos... sorry! > [quoted text clipped - 20 lines] > And W.[Reg Plate]="2003 03"))-1)*100) AS PctPriceDiff > FROM TableName AS A; You're welcome; hope it goes well. SignatureKen Snell <MS ACCESS MVP> > Just wanted to post a *quick* thank you. I've forwarded your last post to > my [quoted text clipped - 5 lines] > > 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 On 11/7/06 14:25, in article #tKBT1OpGHA.3820@TK2MSFTNGP05.phx.gbl, "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote: > You're welcome; hope it goes well. 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! SignatureKen Snell <MS ACCESS MVP> > 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 [quoted text clipped - 36 lines] > >> You're welcome; hope it goes well. 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. On 11/7/06 19:58, in article eaEHyvRpGHA.4408@TK2MSFTNGP04.phx.gbl, "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote: > 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 [quoted text clipped - 32 lines] > > You're welcome! 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. SignatureKen Snell <MS ACCESS MVP> > Hi Ken, > [quoted text clipped - 61 lines] >> >> You're welcome! > Hi Ken, > [quoted text clipped - 17 lines] > help they've received as I like to do so when someone has gone the extra > mile like you kindly have. See http://mvp.support.microsoft.com/ 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. SignatureKen Snell <MS ACCESS MVP> 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 On 11/7/06 22:41, in article e94JDMTpGHA.2292@TK2MSFTNGP05.phx.gbl, "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote: >> Hi Ken, >> [quoted text clipped - 22 lines] > 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. 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. SignatureKen Snell <MS ACCESS MVP> > Hi Ken, > [quoted text clipped - 56 lines] >> want >> to give his email address in the public domain). OK? Thanks. 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 > I think we just need to add a GROUP BY clause to the query: > [quoted text clipped - 26 lines] > Previously, the query returned every record with that unique combination. > This should work for you now. Glad to hear it! Good luck! SignatureKen Snell <MS ACCESS MVP> > 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 [quoted text clipped - 6 lines] > > Jay |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.