Update Two Records from One Record? 1

[MattGreer]

I have an import table, set up like this:

[tt]Device--Location--Date--Flow--Temperature
-----------------------------------------[/tt]

The data from this table gets appended to another table, called RunData, so we can display the historical data from an Instrument as a line graph in Excel.

I need to separate the Flow and Temperature into two separate records in "RunData". The InstrumentID would be, essentially, "Flow" or "Temperature". A device can report more than one value so that's how I'm separating them. The RunData table is set up like this:

[tt]RunDataID--TimeStamp--InstrumentID--FieldValue
----------------------------------------------[/tt]

Is there a way to do this with a query?

Thanks!!


Matt

 

[PHV]

I'd use an UNION query, eg:
SELECT [Date] AS TimeStamp, "Flow" AS InstrumentID, Flow AS FieldValue
FROM yourImportTable WHERE Flow IS NOT NULL
UNION ALL SELECT [Date], "Temperature", Temperature
FROM yourImportTable WHERE Temperature IS NOT NULL

BTW, you don't care the Location ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MattGreer]

I do care about the Location, but the Location doesn't go in the RunData table. Here's a screenshot of the Relationships:

Direct link if that is too fuzzy:

http://i.imgur.com/Z920PhF.png

Basically, there are six Locations and they have the same Instrument names, and each of those Instruments can report more than one piece of data. I differentiated between separate Instrument measurements by specifying what it was reporting, so you have a unique combination of "InstName" and "TypeID".

I'm still not sure if I should be using ID numbers, like, TypeID, versus the actual names as they are unique. What are your thoughts?

It's ugly, I think, but I think I have it working. I'll give your query a try in a moment here.

Thanks!!


Matt

 

[MattGreer]

PHV, hope you're still there. I tried to get a simple version working but I'm getting a syntax error and I'm not sure what I'm doing wrong. I'd appreciate your help one more time!

INSERT INTO tblRunData ( [TimeStamp], InstrumentID, FieldValue )
SELECT ImportTable.DateTime, ImportTable.TransmitterID, ImportTable.Gas
FROM ImportTable
UNION ALL SELECT ImportTable.DateTime, ImportTable.TransmitterID, ImportTable.Fluid
FROM ImportTable;

I get the error: "Syntax error in FROM clause" and when I click "OK" the word "UNION" is highlighted. What am I doing wrong?



Thanks!!


Matt

 

[MattGreer]

Yeah, it's always after a few more minutes of searching that I find the answer... Needed an extra "SELECT" statement.

INSERT INTO tblRunData SELECT * FROM (
SELECT ImportTable.DateTime AS [TimeStamp], ImportTable.TransmitterID AS InstrumentID, ImportTable.Gas AS FieldValue
FROM ImportTable
WHERE ImportTable.Gas IS NOT NULL
UNION ALL SELECT ImportTable.DateTime AS [TimeStamp], ImportTable.TransmitterID AS InstrumentID, ImportTable.Fluid AS FieldValue
FROM ImportTable
WHERE ImportTable.Fluid IS NOT NULL);

Star for you, PHV. Thanks.

Thanks!!


Matt