Access Help required...
#1
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
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
#2
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
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
Last edited by judgejules; 19 February 2004 at 01:05 AM.
#3
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
What format is the data actually stored in the Tables view? same as above?
J
#4
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
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
Thread
Thread Starter
Forum
Replies
Last Post