Solved: Horizantal Fields To Vertical Fields Conversion

[akn112]

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 . 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!

[JimmyTheHand]

Hi akn112

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]

[alimcpill]

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

[akn112]

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