Data reformat

[Stephen Cannell]

I would massively appreciate advice on the following

I have data in a large (over 80,000 records) Access table like this:

stkcode price start-date
10601747 1.49 01/05/2006
10601747 0.37 13/01/2006
10601747 0.74 23/12/2005
10601747 1.49 27/10/2005
10601815 1.49 01/05/2006
10601815 0.37 13/01/2006
10601815 0.74 23/12/2005
10601815 1.49 27/10/2005
10601983 0.99 01/05/2006
10601983 0.25 13/01/2006
10601983 0.49 23/12/2005
10601983 0.99 27/10/2005
10602041 1.49 01/05/2006
10602041 0.37 13/01/2006
10602041 0.74 23/12/2005
10602041 1.49 27/10/2005
10602287 1.49 01/05/2006
10602287 0.37 13/01/2006
10602287 0.74 23/12/2005
10602287 1.49 27/10/2005
etc

It shows historic prices of Stock Items.

I need to reformat this into a spreadsheet so that stkcode appears only
once for each item, and with each price lined up next to it in a
separate field, like this:

10601747 1.49 0.37 0.74 1.49
10601815 1.49 0.37 0.74 1.49
etc


It a kind of reversed normalization I think.

The start-date field is not necessary on the export, it is there to
sort the data into descending date order so that the latest price comes
first in the list.

My problem is how to write a query to export from. There are a variable
number of price for each stkcode so some could have, say, ten prices
next to them and others only one.

I am pulling my hair out.

This is needed so I can have an extra three fields on a separate report
showing the three previous prices for each item.

I have even tried using a report grouped on stkcode set to multi-column
so that all the price field appear in their own columns. This actually
works on the screen and printed report itself but when I export the
report to Excel it loses the columns (I know that exporting reports is
flakey at the best of times).

Thanks you in advance for any help.

 

[Gary Walter]

If I knew that new data for a specific
stkcode was going to have a start-date
that was always later than the oldest
start-date in the current data for that
stkcode...

I'd just add a Long field
(say "DateRank") to my table.

I'd then bite the bullet and "one time"
run an update query to set this rank
based on start-dates...you'd only do
this once, then every time a new record
was added, look up the max rank for
that stkcode, add one, and set this value
for the rank of the new record.

Actually, you could test for old dates
later than new date, and make adjustments
if that was the case when you add new record.
More complicated, but doable....
and a lot faster than computing all ranks
anew with large data.

Then, all you need to do is run a crosstab,
Group By on stkcode, Transform First(price),
Pivot on DateRank.

 

[Stephen Cannell]

Gary -

Thanks ever so much for the reply. Unfortunately I omitted to mention
that my Access table has been made from a Progress database table via
an ODBC connection. I have no update control on the original data.

I will try your ideas on my table,though, and see what I get.

Steve

 

[Stephen Cannell]

Gary -

How would the ranking update query work?

Sorry to be so backward!

Steve

 

[Gary Walter]

How would the ranking update query work?

if you do not expect any ties
(2 or more records for some
stkcode w/ same start-date):

UPDATE yurtable
SET DateRank =
DCount("*","yurtable",
"[stkcode]=" & [stkcode]
& " AND [start-date] <= #"
& [start-date] & "#")

 

[Stephen Cannell]

Thanks, Gary - appreciated