Alternative to crosstab query if possible.
Posted on 2007-11-27
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.