Changing records into fields

  • Thread starter Thread starter Alex Reed
  • Start date Start date
A

Alex Reed

Hi,

I'm trying to change a table that is structured as follows:

TagName PropName PropValue <== Field Names
Tank1 HiAlarm 100
Tank1 LoAlarm 10
Tank1 Level 55
Tank2 HiAlarm 95
Tank2 LoAlarm 10
Tank2 Level 36

It has to be changed to the following structure:

TagName HiAlarm LoAlarm Level <== Field Names
Tank1 100 10 55
Tank2 95 10 36

What is the best way of doing this using
either a query or VBA? (Access97 by the way)

I have gotten as far as creating the new table with
the correct field names, but I can't figure out how
put the data into it.

Thanks,

Alex
 
Dear Alex:

SELECT T1.TagName, T1.HiAlarm, T2.LoAlarm, T3.Level
FROM (SELECT TagName, PropValue AS HiAlarm
FROM YourTable
WHERE PropName = "HiAlarm") T1
INNER JOIN (SELECT TagName, PropValue AS LoAlarm
FROM YourTable
WHERE PropName = "LoAlarm") T2
ON T2.TagName = T1.Tagname
INNER JOIN (SELECT TagName, PropValue AS Level
FROM YourTable
WHERE PropName = "Level") T3

The above SELECT query is an attempt to produce the recordset
required. It presumes that, for every "Tank" all 3 rows exist
(HiAlarm, LoAlarm, Level). If any or all of these may be missing then
some changes would be needed, depending on what could be missing.
This could get quite complicated, but there's another approach to this
based on separate append/update from the 3 "subqueries" for
Hi/Lo/Level shown above.

If there is no such problem, then you can base an append query on this
and it should put the rows you need into the new table.

Hi,

I'm trying to change a table that is structured as follows:

TagName PropName PropValue <== Field Names
Tank1 HiAlarm 100
Tank1 LoAlarm 10
Tank1 Level 55
Tank2 HiAlarm 95
Tank2 LoAlarm 10
Tank2 Level 36

It has to be changed to the following structure:

TagName HiAlarm LoAlarm Level <== Field Names
Tank1 100 10 55
Tank2 95 10 36

What is the best way of doing this using
either a query or VBA? (Access97 by the way)

I have gotten as far as creating the new table with
the correct field names, but I can't figure out how
put the data into it.

Thanks,

Alex
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
The quickest Access method is to create a crosstab query. TagName will be
the Row Heading, PropName is the Column Heading, and Sum of PropValue is the
Value. If PropValue is a text field, then use "First".
 
Back
Top