Access Help required...
 

Help!!!

I cannot think how to do this at all - I'm sure it is simple but I just can't figure it out...

I have data such as:

Date abc
Reference abc
Amount abc
Product abc

Date abc
Reference abc
Amount abc
Product abc

Date abc
Reference abc
Amount abc
Product abc

And I want to translate so that Date, Reference, Amount and Product are column headers and abc are the values.

Cheers,

Posh

Edited to say, awww crud its in access, gimmie a few to think of something else, but you could just export to excel, do the stuff below then re-import :) 2 secs...

Quick way using excel without scripting:

Make sure your data is formatted thus in to columns:

Date abc
Reference abc
Amount abc
Product abc
Date abc
Reference abc
Amount abc
Product abc
Date abc
Reference abc
Amount abc
Product abc

Then go into Tools->Options->Custom Lists (tab)

Select NEW LIST from the "custom lists" list, in the "list entries" box type in the following one per line

Date
Reference
Amount
Product

Then press the Add button then OK

Highlight your whole data set in the sheet then go to Data-Sort

Leave as is but select the Options... button at the bottom and select your new entry from the list under "First key sort order", then press OK and OK

You will then end up with the following in the same order, so date entry 1 will line up with reference entry 1 still:

Date abc
Date abc
Date abc
Reference abc
Reference abc
Reference abc
Amount abc
Amount abc
Amount abc
Product abc
Product abc
Product abc

You can then cut and paste the data into a new sheet into the columns under headings

Date Reference Amount Product

Any Q's drop me a personal message, hope this helps,

Jules

So I guess you're using Forms in access to display the data? or are you using a field in the table to define the type of record, then another field to store the data for that record?

What format is the data actually stored in the Tables view? same as above?

J

Cheers Jules - but the data will be too big to use effectively in Excel (in excel I can get it to work though!).

The data is an extract from our financial system. It is in a table in precisely the format above - over 2 fields. I'm not using any forms, I need to get the data into the right format so I can run some queries.

Posh