Solved: Horizantal Fields To Vertical Fields Conversion

My table has 3 fields: fruit, parameter name, parameter value.
The problem is, i want the parameter name to define the fields in a new table. Example.

[vba]
Fruit Parameter Name Parameter Value
Apple Color Red
Apple Taste 4
Apple Quantity 3
Orange color Orange
Orange Taste 3
Orange Quantity 5
Orange Price Code 4

needs to look like

Fruit Color Taste Quantity Price Code
Apple Red 4 3
Orange Orange 3 5 4
[/vba]

I"ve been trying to use an append query to append to a new table with predefined fields (i already know what parameters i need to extract) but have found it difficult:help . For example, even though the parameter name includes color, quantity, taste, but sometimes i only want to use color and quantity as the vertical fields. Attached is a sample to test with. Thanks!

Hi akn112 :hi:

I'm not sure I understand your request. In my interpretation it sounds like you want to have a table that has two fields for some records, five fields for other records, four fields for yet anothet records, etc. This is not possible. A field either does exist in a table or it doesn't. There is no middle way.

I guess I got it all wrong, and this is not what you want. Then what is it? Could you explain it a bit more?

Jimmy

EDIT:
In the meantime you might wat to try this code. Maybe I have found out what you wanted after all.

[vba]Sub AddTable()
Dim Rst As DAO.Recordset
Dim Tbl As DAO.TableDef
Dim Fld As DAO.Field

Set Rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Parameter Name] FROM Table1;")
Set Tbl = New DAO.TableDef

Tbl.Name = "Table2"

With Rst
.MoveFirst
Do
Set Fld = New DAO.Field
Fld.Name = ![Parameter Name]
Fld.Type = dbLong
Fld.Size = 50
Tbl.Fields.Append Fld
Set Fld = Nothing
.MoveNext
Loop Until .EOF
.Close
End With
CurrentDb.TableDefs.Append Tbl
CurrentDb.TableDefs.Refresh

End Sub
[/vba]

I think you could do this with a Crosstab, use the following SQL

TRANSFORM Max(Table1.[Parameter Value]) AS [MaxOfParameter Value]
SELECT Table1.Fruit
FROM Table1
WHERE (((Table1.[Parameter Name]) In ('price code','quantity')))
GROUP BY Table1.Fruit
PIVOT Table1.[Parameter Name];

- add or remove parameter names from the WHERE clause to get additional fields returned.


Or to use a more standard SQL statement, more complicated but closer to standard SQL:

SELECT Table1.Fruit,
Max(IIf([Parameter Name]='COLOR',[Parameter Value],Null)) AS COLOR, Max(IIf([Parameter Name]='QUANTITY',[Parameter Value],Null)) AS QUANTITY,
Max(IIf([Parameter Name]='TASTE',[Parameter Value],Null)) AS TASTE, Max(IIf([Parameter Name]='Price Code',[Parameter Value],Null)) AS [Price Code]
FROM Table1
GROUP BY Table1.Fruit;

each field follows the same pattern, so it's easy to add or remove them as required.


hth

hi all, sorry for my late reply, ive actually been quite sick for the past couple days. I guess what i wanted was to set up (except as an append not a cross tab) a cross tab with: Fruit as a row header; parameter as a column header (except chose only the parameters i want); and value as value. Hope thats not too confusing.. =P

Edit:

JimmyTheHand: Hey there, i tried out ur code, and i got the fields, but the entries were empty. Hehe, but with some minor modifications it could work nicely thanks

Alimcpill: I was thinking about doing it this way, but i was not quite as familiar with cross tabs when i started and i didnt like having to export the table (it would take 5 minutes to run the query to see if i got it right and then 5 minutes to export since it always reruns the query =S) but ill start off by using this query.

Thanks you 2