Full Version: Converting Crosstab To 3-fieldtable
UtterAccess Forums > Microsoft® Access > Access Forms
I have a table that was imported from excel. It has three rows and four columns. I would like to create a new Access table with three fields...Field1 would be the row headings, Field2 would be the column headings, and Field 3 would be the values where column and row intersect.
Here is spread sheet....
Property 1/ Property 2/ Property 3/ Property 4/
asset 1: 15/ xyz/ POB/ 66po/
asset 2: 25/ abc/ bro/ 58pp/
asset 3: 65/ lmn/ yyi/ QQ09/
I want to create a 3-field table as such...
Asset/ Property/ Value
asset 1/ 1/ 15
asset 1/ 2/ xyz
asset 1/ 3/ POB
asset 1/ 4/ 66po
asset 2/ 1/ 25
asset 2/ 2/ abc
asset 2/ 3/ bro
asset 2/ 4/ 58pp
asset 3/ 1/ 65
on post the row/columns didn't come out visually correct so I added the / between columns
Thank you
Doug Steele
Does Excel actually label the 3 rows Asset 1, Asset 2 and Asset 3? If not, there's no easy way to do what you're trying to do.
On the chance that your table actually has 5 fields, not 4 (named Asset, Property 1, Property 2, Property 3 and Property 4), you'd use a Union query along the lines of:
SELECT Asset, "1" AS Property, [Property 1] AS PropertyValue
FROM ExcelTable
SELECT Asset, "2", [Property 2]
FROM ExcelTable
SELECT Asset, "3", [Property 3]
FROM ExcelTable
SELECT Asset, "4", [Property 4]
FROM ExcelTable
OMarliebone: I noticed you edited your original post to indicate that the asset number is not actually part of the table (brought from Excel) and that the records contain four fields only, corresponding to the properties. This makes the creation of the records you want a bit more difficult - and you probably can't do it now with a UNION query as Doug suggested (assuming the asset number was there, as I did initially).
See the attached demo. First, it uses Doug's UNION query approach for comparison (I actually created it at the same time Doug did, but after seeing his post did not post it). But it does include the MakeTable part of the query. See MyTable and the query. This assumes the asset number is a field - not assumed by the next approach.
Second, it uses a module (see in "Create Records" to create all the records you need, given that the asset number is not present - you can run this with F5 from before the Public...) It empties the NewTable, then fills it with records split from the original format (MyTable2), assigning asset numbers as it goes. This assumes that there are always four properties. This can be adjusted in the code if this is not the case (you can give it a try). This, of course, means that the asset numbers don't really have any specific meaning, doesn't it?
Thought this might give you some ideas...
Thank you Doug and RJD both. As it turns out my Excel table actually does contain a column of Asset names. I had figured out your second way, RJD, but was hoping I could find a way without using the .AddNew and .Update for each record (some of the projects we do have hundreds of assets and up to a dozen properties each). I thought one-at-a-time appending would be slow. I am not very SQL literate, so the union query is new to me - I usually use the Access grid designer - but I think I can figure this one out.
Again, thanks both!
I'm sure Doug joins me in saying...

Good that the asset number is actually present as a field in each record, making Doug's UNION response appropriate. Makes it easier to set up than a function (don't know about speed, if that is even an issue).
Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.