1) Change your tables to the following:
Code:
tblReadings
ReadID Autokey
ReadDate Date/Time
ReadTime Date/Time
ReadNotes Memo
tblMole
MoleID AutoKey
ReadID Number (FK to tblData)
C2 Currency (Format Standard, 2 decimals)
C3 Currency (Format Standard, 2 decimals)
C4 Currency (Format Standard, 2 decimals)
C5 Currency (Format Standard, 2 decimals)
C6 Currency (Format Standard, 2 decimals)
tblSPG
SPGID AutoKey
ReadID Number (FK to tblData)
SPGR Currency (Format Standard, 2 decimals)
RVP Currency (Format Standard, 2 decimals)
Corrosion Currency (Format Standard, 2 decimals)
TestC Currency (Format Standard, 2 decimals)
Sulphur Currency (Format Standard, 2 decimals)
Create two tables Like this:
Code:
tblCrossAvg
RecID Number
RecType Text
tblCrossComp
CompNumber Number
Composition Text
Add these records to the tables
Code:
tblCrossAvg
RecID RecType
1 Detail
2 Average
tblCrossComp
CompNumber Composition
1 C2%(Mole)
2 C3%(Mole)
3 C4%(Mole)
4 C5%(Mole)
5 C6%(Mole)
6 Total
7 SPGR
8 RVP
9 CU Corrosion
10 TestC
11 Sulphur
Create and test a Query Like this:
Code:
qryReads:
SELECT
T0.ReadID,
T0.ReadDate,
T0.ReadTime,
T1.C2,
T1.C3,
T1.C4,
T1.C5,
T1.C6,
(T1.C2+T1.C3+T1.C4+T1.C5+T1.C6) AS CTotal
FROM
tblReadings AS T0
INNER JOIN
tblMole AS T1
ON T0.ReadID = T1.ReadID
WHERE T0.ReadDate Between #10/11/2013# And 29/11/2013#;
The purpose of that query is to get all the required data on one record. The hard-coded starting and ending dates can be replaced later with tempVars or references to controls on a form, after testing is complete.
This next query will take those records and sort the data fields from that query into simple records that contain only three fields - the value for the row ("Composition" - C2, C3 etc) , the value for the column ("ReadPoint" - date or average), and the value that goes in that intersection ("TheValue").
Code:
qryReadsH:
SELECT
TC.Composition AS Composition,
Switch(TA.RecID=1, Format(QR.ReadDate,"DD/MM ") & Format(QR.ReadTime,"HH:NNAM/PM"),
TA.RecID=2, "Average") AS ReadPoint,
Switch(TC.CompNumber=1,QR.C2,
TC.CompNumber=2,QR.C3,
TC.CompNumber=3,QR.C4,
TC.CompNumber=4,QR.C5,
TC.CompNumber=5,QR.C6,
TC.CompNumber=6,QR.CTotal
) AS TheValue
FROM tblCrossAvg AS TA, tblCrossComp AS TC, qryReads AS QR
WHERE TC.CompNumber Between 1 and 6
AND TA.RecID Between 1 and 2;
Now this query will get you the top half of your report:
Code:
qryCrossAvgH:
TRANSFORM Avg(QH.TheValue) As Stuff
SELECT QH.Composition
FROM qryReadsH AS QH
GROUP BY QH.Composition
PIVOT QH.ReadPoint;
If you wanted to average by day instead of showing each read, you would change the first line of qryReadsH to remove the time and just have each column by date. You could do another one averaged by week or month if you wanted. Here's the version by Day:
Code:
qryReadsD:
SELECT
Switch(TA.RecID=1, Format(QC.ReadDate,"DD/MM"),
TA.RecID=2, "Average") AS ReadPoint,
TC.Composition AS Composition,
Switch(TC.CompNumber=1,QR.C2,
TC.CompNumber=2,QR.C3,
TC.CompNumber=3,QR.C4,
TC.CompNumber=4,QR.C5,
TC.CompNumber=5,QR.C6,
TC.CompNumber=6,QR.CTotal
) AS TheValue
FROM tblCrossAvg AS TA, tblCrossComp AS TC, qryReads AS QR
WHERE TC.CompNumber Between 1 and 6
AND TA.RecID Between 1 and 2;
qryCrossAvgD:
TRANSFORM Avg(QD.TheValue) As Stuff
SELECT QD.Composition
FROM qryReadsD AS QD
GROUP BY QD.Composition
PIVOT QD.ReadPoint;
When you've verified and tested that in your database, then you can use the same method to create a DIFFERENT query for the bottom half. I emphasize "different", because the data is coming from a different table, and while you could union them together to get your report, or join them together with a double-left-join, it's better practice to just make two different subreports that appear on the same report.
Notes - Do not enter the text word Null - it will give you the mother of all headaches. Just leave the data Null/blank/zero. If there is no difference between zero and null, then entering zero would be preferable.
Windows 7 32bit 
Access 2010 32bit
Reply With Quote
