Alternative to crosstab query if possible.

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.
Who is Participating?
GRayLConnect With a Mentor Commented:
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;
Show us several records of the data.  Then show us what you would like to see.
We really need to see the structure of the data source.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

YvesBoueleAuthor Commented:
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
YvesBoueleAuthor Commented:
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%

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?
Sorry, my last post was a mistake. I should have re-freshed.
As the Amount varies by the number purchased, shouldn't you be comparing Unit Price?
YvesBoueleAuthor Commented:
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.
YvesBoueleAuthor Commented:
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
GRayLConnect With a Mentor Commented:
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


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.
YvesBoueleAuthor Commented:
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
YvesBoueleAuthor Commented:
How will I apply the percentage format?
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.
YvesBoueleAuthor Commented:
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.
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??
YvesBoueleAuthor Commented:
yes it's 5, a typo error
YvesBoueleAuthor Commented:
yes it's 10 not 5
That's been tearing my hair out for the last day.  Now we're getting somewhere - but isn't this a new question?
YvesBoueleAuthor Commented:
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.
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.
YvesBoueleAuthor Commented:
Thank you so much !!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.