Solved

Alternative to crosstab query if possible.

Posted on 2007-11-27
22
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 11
22 Comments
 
LVL 44

Expert Comment

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

Expert Comment

by:GRayL
ID: 20362628
We really need to see the structure of the data source.
0
 

Author Comment

by:YvesBouele
ID: 20367505
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:YvesBouele
ID: 20367609
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
ID: 20367846
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
ID: 20367852
Sorry, my last post was a mistake. I should have re-freshed.
0
 
LVL 44

Expert Comment

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

Author Comment

by:YvesBouele
ID: 20368452
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
ID: 20368543
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
ID: 20369601
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
ID: 20369758
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
 

Author Closing Comment

by:YvesBouele
ID: 31411317
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
ID: 20374828
How will I apply the percentage format?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20376193
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
ID: 20378038
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
ID: 20384432
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
ID: 20385032
yes it's 5, a typo error
0
 

Author Comment

by:YvesBouele
ID: 20385033
yes it's 10 not 5
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20385113
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
ID: 20398454
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
ID: 20399502
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
ID: 20407656
Thank you so much !!!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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