Columns to rows (1 Viewer)

[PeterWieland]

Not sure if this is the best way to phrase the title, but I couldn't think of any other way.

I have 2 tables; 1st table is automatically recorded data from a scientific instrument that has columns for date, record number and then around 50 measured parameters. Each new day starts the record number back at 1, so the only unique identification of a record is a combination of date and record number.

I also have a table of data entered from a paper record. This has columns for date, plant number, several manually measured parameters and three columns for record numbers relating to those in table 1; the idea is that the instrument takes 3 recordings 20 seconds apart for each plant on each day, and the paper records show which instrument records appertain to which plant.

What I need is a query that takes the 3 readings for an individual parameter for an individual plant on a particular day and gives me the average.

I can't even begin to work out how to automate this in Access (or Excel for that matter) so I have attached a spreadsheet that shows how it could be done manually, but with tens of thousands of records, that isn't an option.

I realise now that the best way to get to where I need to be is not to start from here, but I am somewhat stuck with the data I have. The instrument records are fixed as they are hard coded, and the paper records have already been entered by someone which has taken many hours.

Thanks in advance for any ideas

 

[Peter Reid]

It probably wouldn't be very fast but you could use the DAvg function for this

Create a query with Table2 as the source, add fields Date, PlantNo, ParamA, ParamB, ParamC and then create calculated fields for the Mean fields.

'Param1 Mean' would be (something like): DAvg("Param1","Table 1","Date=#" & [Date] & "# And RecordNo=" & [Record1])

Note that I'm not sure this is the correct syntax for the date value or if the field name of Date would cause a problem. I'm also assuming that RecordNo's 1 and 4 only appear in field Record1 in Table 2 etc as per your example