Tough one (crosstab?)

[Guest]

I am a pretty experienced Access/SQL/VB user but I am not sure how to
approach this problem. I want to take data that looks like this:

Style Price Date
1111 $40 3/20
1111 $30 3/28
1111 $20 4/2
1111 $10 5/1

And turn it into this:
Style March April May
1111 $40 $20 $10
1111 $30

It's similar to a cross tab process, but I don't want to aggregate the data.
The only other thing I can think of is a union, but those are so inefficient
that they are a means of last resort. Anyone have any ideas how I could
accomplish this?

 

[cpnet]

And turn it into this:

Style March April May
1111 $40 $20 $10
1111 $30

What does the desired output mean? Does the $40 in the March column have
any
relation to the $20 in the April column? The fact that those are both in
the same row implies there is some relationship. It also implies that the
$30 in March is somehow different from the other $40 in March since it's in
a different row. But, what is the difference?

 

[Guest]

$40 in March is the first price that month and $20 is the first price in
April. Essentially I want to place every price in a list under the month in
which it occurs. Eventually I will tie other related data to each row
(price) such as units sold.

 

[cpnet]

So, you're saying that the first row holds the first price (based on the
date column in your original table) for each respective month. The second
row will display the 2nd price used in each respective month (based on the
date column).

It sounds like you'll need to take your original table:

Style Price Date
1111 $40 3/20
1111 $30 3/28
1111 $20 4/2
1111 $10 5/1

And run a query on it something like the one below (assumes the table name
is MyTable):

SELECT
t1.Style,
t1.Price,
Year( t1.Date) AS [Year],
Month(t1.Date) AS [Month]
COUNT( t2.*) + 1 AS Sort
FROM
MyTable t1
LEFT JOIN MyTable t2 ON
t1.Style = t2.Style AND
Year(t1.Date) = Year(t2.Date) AND
Month(t1.Date) = Month(t2.Date) AND
t1.Date > t2.Date
GROUP BY
t1.Style,
t1.Price,
Year( t1.Date),
Month(t1.Date)


*This requires that you don't have multiple prices for the same Style on the
same day.

The query will give you something like:

Style Price Year Month Sort
1111 $40 2005 March 1
1111 $30 2005 March 2
1111 $20 2005 April 1
1111 $10 2005 May 1


You can then use this info to construct the type of crosstab you want. The
Sort column can be used to place each price/month combo on the correct row.

However, it would seem to me that it will be extremely difficult to link
other data to a cross tab like the one you want to build. I can't help but
think that you're taking the wrong approach with the crosstab you're trying
to build. If you think about:

Style March April May
1111 $40 $20 $10
1111 $30

How are you going to link to this info? You can really only use the [Style]
column for linking, because the Month columns are dynamically created by
your cross tab, and may not always be present. Does it make sense to link
the same units sold info to both of the above rows? (Both rows have the
same value for [Style], so both rows will have the same units sold info
linked to them - won't they)?

 

[Duane Hookom]

I don't usually reply in threads with comments like "I agree" or "good
advice" however, I this response is excellent. It is both well designed and
explained.

 

[Guest]

Thanks! I was able to adapt this basic approach to be just what I needed.
As far as linking I eventually connected sales by month and price to connect
that "rank" value and it allowed me to align prices with sales side by side
with months going across. I had to do this a I was creating a query to link
to Excel to populate some complex sales spreadsheets and needed this basic
format:

Style MarchPrices MarchSales AprilPrice AprilSales