Solved

Alternative to crosstab query if possible.

Posted on 2007-11-27
22
259 Views
Last Modified: 2013-12-25
Hi Experts,
I have a query that gives me a variable set of data based on the date range the user entered.  Each record returns a price that I need to compare(or rather do some calculations) with previous records prices if they exist.  
1. I created a crosstab query with row headings: CustomerName, Account, Product, datePurchased and column heading with the prices.  The problem here is that I am getting headings such as $10.02, $40.25, $30.15... corresponding on the amount the customer spent on that date.  How do I change the headings to say Price1, Price2, Price3 dynamically corresponding to the number of records for the customers.  I would like to compare the records 2 at a time; finding the rate between price2/price1 then price3/price2 etc...

2. The other scenario I thought about would be to create a table from my resultset then add a last column that will allow me to run some calculation between the prices (current record vs previous record) if there is more than one record.  I keep seeing example of how to add a new field to an existing table but I don't see any example with adding new field and data.

I'd appreciate any help.
0
Comment
Question by:YvesBouele
  • 11
  • 11
22 Comments
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Show us several records of the data.  Then show us what you would like to see.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
We really need to see the structure of the data source.
0
 

Author Comment

by:YvesBouele
Comment Utility
CustomerName           Account             Product      DatePurchased       Prices                   Amount      
ACME 1                   000000004      20031231      3/29/2004      2747.288136      $16,209.00
ACMCTWOHCY      00000000420031231      3/29/2004      2747.288136      $16,209.00
ACMCTHREECY       000000004      20031231      3/29/2004      2747.288136      $16,209.00
ACMCFOURCY4     000000004      20031231      3/29/2004      2747.288136      $16,209.00
0
 

Author Comment

by:YvesBouele
Comment Utility
Sorry, the last post was a mistake
This is a sample of the structure i am dealing with.

CustomerName         Account             Product      DatePurchased       Unit Price               Amount      
ACMC1ONECY       000000004          PRODUCT1      3/29/2004              1.08                     $10.02
ACMC1ONECY       000000004          PRODUCT1      2/20/2005              2.03                     $40.25
ACMC1ONECY       000000004          PRODUCT1      8/28/2006               1.14                     $30.15
ACMC1ONECY       000000004          PRODUCT1      12/21/2007            2.06                     $15.30

When I do the crosstab query , I get the following

CustomerName         Account             Product            $10.02       $40.25   $30.15      $55.30
ACMC1ONECY       000000004          PRODUCT1       $10.02       $40.25   $30.15      $55.30

Eventually i would like something like this:
CustomerName         Account             Product            Rate      
ACMC1ONECY       000000004          PRODUCT1       40.25/10.02 =~401%
ACMC1ONECY       000000004          PRODUCT1       30.15/40.25 =~75%
ACMC1ONECY       000000004          PRODUCT1       15.30/30.15 =~50.7%


0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
So you have four different customers all using the same account (?) who purchased the same product on the same date for the same prices and amount.  So what do you want to see?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Sorry, my last post was a mistake. I should have re-freshed.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
As the Amount varies by the number purchased, shouldn't you be comparing Unit Price?
0
 

Author Comment

by:YvesBouele
Comment Utility
Hi GRayL,
it's all one customer ACMC1ONECY.  I need to track all transactions made by one customer at a time.
you made one good point though, I should be comparing the unit prices instead.  But the issue still remaining that I need to compare each subsequent events on the customer.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
Comment Utility
This worked in a similar table I had.  I hope I didn't make any errors translating it over.  Substitute your actual table name for 'myTable':

SELECT a.CustomerName, a.Account, a. Product, ((Select c.Amount, FROM myTable AS c WHERE c.CustomerName=a.CustomerName AND c.Account=a.Account AND c.Product=a.Product AND c.DatePurchased IN (SELECT Min(b.DatePurchased) FROM myTable AS b WHERE b.DatePurchased>a.DatePurchased AND b.CustomerName=a.CustomerName AND b.Account=a.Account AND b.Product=a.Product))-a.Product)/a.Product) AS RateChange FROM myTable AS a;
0
 

Author Comment

by:YvesBouele
Comment Utility
Please review your syntax for me.  There are 4 open parentheses and 5 closing ones.  also I don't understand the sign - in front of a.Product : ... AND b.Product=a.Product))-a.Product)/a.Product).  Other than that I am getting quite confident in your solution
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 500 total points
Comment Utility
I was just trying to give you exactly what you asked for in terms of rows and fields.  The single letters in front of the field names are aliases for the tablenames. 'a' is a lot easier to type than 'Thisisthesecondfieldfromtable1' which you only have to type once, shortens the statement and for me makes it a lot easier to absorb.  The SubQuery needs an opening and closing parenthesis. The RateChange formula (a-c)/a requires 2.  I see the error - change:

AND b.Product=a.Product))-a.Product)/a.Product) AS

to:

AND b.Product=a.Product)-a.Product)/a.Product) AS

Once we get that working we can then wrap the RateChange in Format(RateChange,"Percent") and get .76989546 changed to 76.99%.  But that involves more parentheses, and I like to go one step at a time.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Closing Comment

by:YvesBouele
Comment Utility
there were few syntax error that I was able to clean up such as comma in "Select c.Amount, FROM" and space in "a. Product,".  Also the result RateRange comes on the first record instead of the record of the subsequent occurrence.  For example the first record should have 100% all the time but the second should show the percentage of the second purchase in relation to the first purchase
0
 

Author Comment

by:YvesBouele
Comment Utility
How will I apply the percentage format?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
This do it?  I used Format(<long long sub query>,"Percent")

SELECT a.CustomerName, a.Account, a.Product, Format(((Select c.Amount FROM myTable AS c WHERE c.CustomerName=a.CustomerName AND c.Account=a.Account AND c.Product=a.Product AND c.DatePurchased IN (SELECT Min(b.DatePurchased) FROM myTable AS b WHERE b.DatePurchased>a.DatePurchased AND b.CustomerName=a.CustomerName AND b.Account=a.Account AND b.Product=a.Product))-a.Product)/a.Product),"Percent") AS RateChange FROM myTable AS a;

I can give you the first record always 100% but that is not what you showed at http:#a20367609.  When you get the percent working, paste the whole SQL back here and I'll change the order as per your grading comments.
0
 

Author Comment

by:YvesBouele
Comment Utility
We have come very far already.  I will try to be as clear as possible as to what i am looking for. This is the end result
CustomerName       Product      DatePurchased    Unit Price  Qty  NetDiff  Rate       Amount     NewAmount
ACMC1ONECY     PRODUCT1    3/29/2004              1.08       80      80       100%      $86.40     1 * 86.40
ACMC1ONECY     PRODUCT1    2/20/2005              2.03       35      45       43.75%   $71.05   0.437 * 71.05
ACMC1ONECY     PRODUCT1    8/28/2006              1.14       20      15       44.44%   $22.8     0.44 * 22.8
ACMC1ONECY     PRODUCT1   12/21/2007             2.06       10       5        66.67%   $20.60   0.667 * 20.60

ACMC1TWOCY     PRODUCT2    3/29/2004              1.08      90      90      100%       $97.2
ACMC1TWOCY     PRODUCT2    2/20/2005              2.03      40      50      44.44%    $81.2
ACMC1TWOCY     PRODUCT2    8/28/2006              1.14      20      30      40%         $22.8
ACMC1TWOCY     PRODUCT2    12/21/2007             2.06     10      20     33.33%     $20.60

Originally I don't have the Net, Rate and Amount fields
How did I calculate these numbers?
NetDiff:      Previous Qty - Actual Qty
Rate:         Actual Qty / Previous Qty
Amount:   Qty * Unit Price
New Amount: Rate * Amount

when I run your code I get (for example) 43.75% on the first line and the rest is just as confusing
CustomerName       Product      DatePurchased    Unit Price  Qty  NetDiff  Rate      
ACMC1ONECY     PRODUCT1    3/29/2004              1.08       80      80       43.75%    
i am sure we're on the right path I just need to sort out all this logic.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Yves.  I am having a difficult time sorting out the logic of the steps required to complete this task.  I can generate this series

Qty  PrevQty
80      0        
35    80
20    35
10    20

It would appear to get the NetDiff I need this -(PrevQty-Qty) which fails for the fourth line, I get 10, you show 5??
0
 

Author Comment

by:YvesBouele
Comment Utility
yes it's 5, a typo error
0
 

Author Comment

by:YvesBouele
Comment Utility
yes it's 10 not 5
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
That's been tearing my hair out for the last day.  Now we're getting somewhere - but isn't this a new question?
0
 

Author Comment

by:YvesBouele
Comment Utility
If that a new question, that's the issue I have had all along.  Are you suggesting I add more points to this already maxed out request ticket?  I will do whatever is necessary to help me get to a solution.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
You state netdiff = prev qty-actual qty.  By my calcs, when you are on the first record, the previous value is Null.  I cannot generate a scenario that gives you the numbers you are looking for mathematically.  Rate appears to be netDiff/prev qty - not as you stated.  Here's what I get:

Running this query:

SELECT a.CustomerName, a.Account, a.Product, a.Qty, (
SELECT c.Qty FROM Sales AS c WHERE c.CustomerName=a.CustomerName AND c.Product = a.Product AND c.DatePurchased IN (
SELECT Max(b.DatePurchased) FROM Sales AS b WHERE b.DatePurchased<a.DatePurchased AND b.CustomerName=a.CustomerName AND b.Account=a.Account AND b.Product=a.Product))  AS PrevQty,PrevQty-A.Qty as NetDiff,Format(NetDiff/PrevQty,"Percent") As Rate
FROM Sales AS a;

I get this result:

CustomerName      Account      Product      Qty      PrevQty      NetDiff      Rate
ACMC1ONECY      000000004      PRODUCT1      80                  
ACMC1ONECY      000000004      PRODUCT1      35      80      45      56.25%
ACMC1ONECY      000000004      PRODUCT1      20      35      15      42.86%
ACMC1ONECY      000000004      PRODUCT1      10      20      10      50.00%
ACMC1TWOCY      000000004      PRODUCT2      90                  
ACMC1TWOCY      000000004      PRODUCT2      40      90      50      55.56%
ACMC1TWOCY      000000004      PRODUCT2      20      40      20      50.00%
ACMC1TWOCY      000000004      PRODUCT2      10      20      10      50.00%

To move on from here I think you should open a new question.
0
 

Author Comment

by:YvesBouele
Comment Utility
Thank you so much !!!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now