Do I have to use VB???

[technesurge]

Hello all,



My field names are:
DateAndTime, TestNumber, Data1, Data2, Data3

The datalogger will generate data like this:

6/4/12 8:30:00 am, 1, 10, 11, 12
6/4/12 8:30:01 am, 1, 13, 14, 15
6/4/12 9:30:00 am, 2, 20, 21, 22
6/4/12 9:30:01 am, 2, 23, 24, 25

(In reality there would be thousands of tests and thousands of records per test)
I need to find the max values for Data1, Data2, Data3 for each test. The new table should look like this:

6/4/12 8:30:00 am, 1, 13, 14, 15
6/4/12 9:30:00 am, 2, 23, 24, 25

Do I have to use VB script?

Thanks to you all...

[Robeen]

The results you show are ambiguous.
If your data looked like this:
6/4/12 8:30:00 am, 1, 10, 16, 12
6/4/12 8:30:01 am, 1, 13, 14, 15
6/4/12 9:30:00 am, 2, 20, 21, 27
6/4/12 9:30:01 am, 2, 23, 24, 25
. . . what would you need to see as your results?

[recyan]

Will there be only Data1, Data2 and Data3 or will there be more of these ?

Thanks

[jzwp11]

Frankly, you are going to have a lot of trouble querying your data if you leave the data in its present format. I would recommend moving the data logger information into an appropriately normalized table structure. Could you please provide some background on the application you are trying to model?

[technesurge]

Robeen:

6/4/12 8:30:00 am, 1, 13, 16, 15
6/4/12 9:30:00 am, 2, 23, 24, 27

Recyan:

Yes, there will be many more DataX's

[technesurge]

jzwp11:

The application is temperature monitoring. There are many temperature sensors (Data1 ... DataX) and I'll be looking for the max temperatures in each test. The datalogger is writing through an ODBC driver to an Access database. I wish I could configure the datalogger to write each test to a separate database or table, but it is not possible. Somehow I have to use the TestNumber to find the max's.

[Robeen]

Will there always be only Data1, Data2, Data3 for each row of data?

[jzwp11]

You can keep leave the table that the datalogger creates alone, but you should copy the data into appropriately structured tables using append queries, then your job of querying will be much easier. Setting up the correct table structure is the key to any relational database application. From what you have said so far, you have a test number and associated with each are many temperature data points, so that describes a one-to-many relationship. What does the test number represent? Will you have the same test number occur over many dates? Are the test numbers associated with a sample or specific location?

[technesurge]

Robeen:

There are many temperature sensors (more than 3), but yes they will always be the same for each row.

jzwp11:

A test means running the equipment for 2 hours and recording temperatures once per second. Every test is the same. I created the TestNumber as simply a unique identifier for each test so later I can select the appropriate data with that number. The first test will be "Test 1" and the second - "Test 2" etc. Therefore no, the same test number will never occur.

[jzwp11]

OK, so each test is comprised of multiple sensors, so that describes a one-to-many relationship. Can a temperature sensor be used in multiple tests? I assume it can. That describes another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities that describes a many-to-many relationship for which you need a junction table. This is what the structure would look like


tblTests
-pkTestID primary key, autonumber
-TestNumber
-dteTest (date/time of test)


tblSensors
-pkSensorID primary key, autonumber
-SensorNumber

tblTestSensors (this is the junction table)
-pkTestSensorID primary key, autonumber
-fkTestID foreign key to tblTests
-fkSensorID foreign key to tblSensors
-result (your temp value goes here)

[technesurge]

Thank you for your advice.

Attempting...

[Robeen]

I just tried this:

SELECT TestData.TestDate, TestData.TestNumber, Max(TestData.Data1) AS MaxOfData1, Max(TestData.Data2) AS MaxOfData2, Max(TestData.Data3) AS MaxOfData3
FROM TestData
GROUP BY TestData.TestDate, TestData.TestNumber;

- with the four rows of data from my first post.
It gave me what you said you were expecting.
See screenshots.

Let me know if this works for what you're trying.

P.S. This might fail if a test runs into another day.
I only used Date in my Date field - with Date AND Time . . . the grouping might not work.
But - you could decide to either NOT use the date field at all - OR - just use the Date portion of the DateTime value to group on.

[technesurge]

Robeen:

In your query I removed the date portion. In a second query I found the min of the DateAndTime for each TestNumber and then related and combined the two. Thank you very much.

[Robeen]

You're welcome. I'm glad I could help.