A rookie needs a patient teacher

D

Denise

Assigned to correct glitches showing themselves in an Access application
designed to track average results from a wellness screening.

I have two queries that are connected to a report that shows averages for
each of the 2 class screenings (cholesterol, glucose, etc.).

I cleaned up the associated table and the Screening #1 report looks great.
The query assigned to it has 10 records (one for each session we have held).

But the Screening #2 report is a mess and clearly the biggest problem is
that when you open the associated query - there are 86 records!! There
should only be ten.

It has taken each of the sessions and assigned only 1 or 2 students to each
record instead of all of them at once.

Would someone be willing to interpret for me the reason this may have
happened and some possibilities for cleaning up this query?

Gratefully,
 
J

Jerry Whittle

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
 
D

Denise

Here's the SQL

Thanks for being willing!

SELECT tblResults.SesID, Count(tblResults.ResID) AS n,
Avg(tblResults.Tchol2) AS TChol1Av, Count(tblResults.Tchol2) AS nChol1,
Min(tblResults.Tchol2) AS TChol1min, Max(tblResults.Tchol2) AS Tchol1max,
Avg(tblResults.HDL2) AS HDL1Av, Count(tblResults.HDL2) AS nHDL1,
Min(tblResults.HDL2) AS HDL1min, Max(tblResults.HDL2) AS HDL1max,
Avg(qryRatioResults.Ratio2) AS Ratio1Av, Count(qryRatioResults.Ratio2) AS
nRatio1, Min(qryRatioResults.Ratio2) AS Ratio1min,
Max(qryRatioResults.Ratio2) AS Ratio1max, Avg(tblResults.LDL2) AS LDL1Av,
Count(tblResults.LDL2) AS nLDL1, Min(tblResults.LDL2) AS LDL1min,
Max(tblResults.LDL2) AS LDL1max, Avg(tblResults.TG2) AS TG1Av,
Count(tblResults.TG2) AS nTG1, Min(tblResults.TG2) AS TG1min,
Max(tblResults.TG2) AS TG1max, Avg(tblResults.Glu2) AS GLu1Av,
Count(tblResults.Glu2) AS nGlu1, Min(tblResults.Glu2) AS Glu1min,
Max(tblResults.Glu2) AS glu1max, Avg(tblResults.Wt2) AS Wt1Av,
Count(tblResults.Wt2) AS nWT1, Min(tblResults.Wt2) AS Wt1min,
Max(tblResults.Wt2) AS Wt1max, Avg(tblResults.BMI2) AS BMI1Av,
Count(tblResults.BMI2) AS nBMI1, Min(tblResults.BMI2) AS BMI1min,
Max(tblResults.BMI2) AS BMI1max, Avg(tblResults.BPsys2) AS BPsys1Av,
Count(tblResults.BPsys2) AS nBPsys1, Min(tblResults.BPsys2) AS BPsys1min,
Max(tblResults.BPsys2) AS BPsys1max, Avg(tblResults.BPdia2) AS BPdia1Av,
Count(tblResults.BPdia2) AS nBPdia1, Min(tblResults.BPdia2) AS BPdia1min,
Max(tblResults.BPdia2) AS BPdia1max, Avg(tblResults.Pulse2) AS Puls1Av,
Count(tblResults.Pulse2) AS nPuls1, Min(tblResults.Pulse2) AS Pulse1min,
Max(tblResults.Pulse2) AS Pulse1max, Avg(tblResults.ResInfo02) AS BFP2Av,
Count(tblResults.ResInfo02) AS nBFP2, Min(tblResults.ResInfo02) AS BFP2Min,
Max(tblResults.ResInfo02) AS BFP2max, Avg(tblResults.ResInfo04) AS Ins2Av,
Count(tblResults.ResInfo04) AS nIns2, Min(tblResults.ResInfo04) AS Ins2Min,
Max(tblResults.ResInfo04) AS Ins2max, Avg(tblResults.ResInfo06) AS HgA1c2Av,
Count(tblResults.ResInfo06) AS nHgA1c2, Min(tblResults.ResInfo06) AS
HgA1c2Min, Max(tblResults.ResInfo06) AS HgA1c2max, Avg(tblResults.ResInfo08)
AS Waist2Av, Count(tblResults.ResInfo08) AS nWaist2,
Min(tblResults.ResInfo08) AS Waist2Min, Max(tblResults.ResInfo08) AS
Waist2max, Avg(tblResults.ResInfo10) AS Hip2Av, tblResults.ResInfo10 AS
nHip2, Min(tblResults.ResInfo10) AS Hip2Min, Max(tblResults.ResInfo10) AS
Hip2max, Avg(tblResults.ResInfo12) AS WHRatio2Av, Count(tblResults.ResInfo12)
AS nWHRatio2, Min(tblResults.ResInfo12) AS WHRatio2Min,
Max(tblResults.ResInfo12) AS WHRatio2max
FROM tblResults INNER JOIN qryRatioResults ON tblResults.ResID =
qryRatioResults.ResID
GROUP BY tblResults.SesID, tblResults.ResInfo10;
 
R

raskew via AccessMonster.com

Yow -

Here's a formatted version:
SELECT
tblResults.SesID
, Count(tblResults.ResID) AS n
, Avg(tblResults.Tchol2) AS TChol1Av
, Count(tblResults.Tchol2) AS nChol1
, Min(tblResults.Tchol2) AS TChol1min
, Max(tblResults.Tchol2) AS Tchol1max
, Avg(tblResults.HDL2) AS HDL1Av
, Count(tblResults.HDL2) AS nHDL1
, Min(tblResults.HDL2) AS HDL1min
, Max(tblResults.HDL2) AS HDL1max
, Avg(qryRatioResults.Ratio2) AS Ratio1Av
, Count(qryRatioResults.Ratio2) AS nRatio1
, Min(qryRatioResults.Ratio2) AS Ratio1min
, Max(qryRatioResults.Ratio2) AS Ratio1max
, Avg(tblResults.LDL2) AS LDL1Av
, Count(tblResults.LDL2) AS nLDL1
, Min(tblResults.LDL2) AS LDL1min
, Max(tblResults.LDL2) AS LDL1max
, Avg(tblResults.TG2) AS TG1Av
, Count(tblResults.TG2) AS nTG1
, Min(tblResults.TG2) AS TG1min
, Max(tblResults.TG2) AS TG1max
, Avg(tblResults.Glu2) AS GLu1Av
, Count(tblResults.Glu2) AS nGlu1
, Min(tblResults.Glu2) AS Glu1min
, Max(tblResults.Glu2) AS glu1max
, Avg(tblResults.Wt2) AS Wt1Av
, Count(tblResults.Wt2) AS nWT1
, Min(tblResults.Wt2) AS Wt1min
, Max(tblResults.Wt2) AS Wt1max
, Avg(tblResults.BMI2) AS BMI1Av
, Count(tblResults.BMI2) AS nBMI1
, Min(tblResults.BMI2) AS BMI1min
, Max(tblResults.BMI2) AS BMI1max
, Avg(tblResults.BPsys2) AS BPsys1Av
, Count(tblResults.BPsys2) AS nBPsys1
, Min(tblResults.BPsys2) AS BPsys1min
, Max(tblResults.BPsys2) AS BPsys1max
, Avg(tblResults.BPdia2) AS BPdia1Av
, Count(tblResults.BPdia2) AS nBPdia1
, Min(tblResults.BPdia2) AS BPdia1min
, Max(tblResults.BPdia2) AS BPdia1max
, Avg(tblResults.Pulse2) AS Puls1Av
, Count(tblResults.Pulse2) AS nPuls1
, Min(tblResults.Pulse2) AS Pulse1min
, Max(tblResults.Pulse2) AS Pulse1max
, Avg(tblResults.ResInfo02) AS BFP2Av
, Count(tblResults.ResInfo02) AS nBFP2
, Min(tblResults.ResInfo02) AS BFP2Min
, Max(tblResults.ResInfo02) AS BFP2max
, Avg(tblResults.ResInfo04) AS Ins2Av
, Count(tblResults.ResInfo04) AS nIns2
, Min(tblResults.ResInfo04) AS Ins2Min
, Max(tblResults.ResInfo04) AS Ins2max
, Avg(tblResults.ResInfo06) AS HgA1c2Av
, Count(tblResults.ResInfo06) AS nHgA1c2
, Min(tblResults.ResInfo06) AS HgA1c2Min
, Max(tblResults.ResInfo06) AS HgA1c2max
, Avg(tblResults.ResInfo08) AS Waist2Av
, Count(tblResults.ResInfo08) AS nWaist2
, Min(tblResults.ResInfo08) AS Waist2Min
, Max(tblResults.ResInfo08) AS Waist2max
, Avg(tblResults.ResInfo10) AS Hip2Av
, tblResults.ResInfo10 AS nHip2
, Min(tblResults.ResInfo10) AS Hip2Min
, Max(tblResults.ResInfo10) AS Hip2max
, Avg(tblResults.ResInfo12) AS WHRatio2Av
, Count(tblResults.ResInfo12) AS nWHRatio2
, Min(tblResults.ResInfo12) AS WHRatio2Min
, Max(tblResults.ResInfo12) AS WHRatio2max
FROM
tblResults
INNER JOIN
qryRatioResults
ON
tblResults.ResID = qryRatioResults.ResID
GROUP BY
tblResults.SesID
, tblResults.ResInfo10;
I think you asked for the same thing as Jerry - correct? It's posted.
Denise-
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
D

Denise

Thanks for taking the "edge" off my post.

Is it not only the first two that need to be addressed? The Session ID and
the Count.

If you look at the query itself, session 10 worked correctly - all students
counted as one record and all data compiled. All other sessions are spread
out into more than one row with the same Session ID allotted to only a few of
the total number of students.

Thanks for taking the time!

raskew via AccessMonster.com said:
Yow -

Here's a formatted version:
SELECT
tblResults.SesID
, Count(tblResults.ResID) AS n
, Avg(tblResults.Tchol2) AS TChol1Av
, Count(tblResults.Tchol2) AS nChol1
, Min(tblResults.Tchol2) AS TChol1min
, Max(tblResults.Tchol2) AS Tchol1max
, Avg(tblResults.HDL2) AS HDL1Av
, Count(tblResults.HDL2) AS nHDL1
, Min(tblResults.HDL2) AS HDL1min
, Max(tblResults.HDL2) AS HDL1max
, Avg(qryRatioResults.Ratio2) AS Ratio1Av
, Count(qryRatioResults.Ratio2) AS nRatio1
, Min(qryRatioResults.Ratio2) AS Ratio1min
, Max(qryRatioResults.Ratio2) AS Ratio1max
, Avg(tblResults.LDL2) AS LDL1Av
, Count(tblResults.LDL2) AS nLDL1
, Min(tblResults.LDL2) AS LDL1min
, Max(tblResults.LDL2) AS LDL1max
, Avg(tblResults.TG2) AS TG1Av
, Count(tblResults.TG2) AS nTG1
, Min(tblResults.TG2) AS TG1min
, Max(tblResults.TG2) AS TG1max
, Avg(tblResults.Glu2) AS GLu1Av
, Count(tblResults.Glu2) AS nGlu1
, Min(tblResults.Glu2) AS Glu1min
, Max(tblResults.Glu2) AS glu1max
, Avg(tblResults.Wt2) AS Wt1Av
, Count(tblResults.Wt2) AS nWT1
, Min(tblResults.Wt2) AS Wt1min
, Max(tblResults.Wt2) AS Wt1max
, Avg(tblResults.BMI2) AS BMI1Av
, Count(tblResults.BMI2) AS nBMI1
, Min(tblResults.BMI2) AS BMI1min
, Max(tblResults.BMI2) AS BMI1max
, Avg(tblResults.BPsys2) AS BPsys1Av
, Count(tblResults.BPsys2) AS nBPsys1
, Min(tblResults.BPsys2) AS BPsys1min
, Max(tblResults.BPsys2) AS BPsys1max
, Avg(tblResults.BPdia2) AS BPdia1Av
, Count(tblResults.BPdia2) AS nBPdia1
, Min(tblResults.BPdia2) AS BPdia1min
, Max(tblResults.BPdia2) AS BPdia1max
, Avg(tblResults.Pulse2) AS Puls1Av
, Count(tblResults.Pulse2) AS nPuls1
, Min(tblResults.Pulse2) AS Pulse1min
, Max(tblResults.Pulse2) AS Pulse1max
, Avg(tblResults.ResInfo02) AS BFP2Av
, Count(tblResults.ResInfo02) AS nBFP2
, Min(tblResults.ResInfo02) AS BFP2Min
, Max(tblResults.ResInfo02) AS BFP2max
, Avg(tblResults.ResInfo04) AS Ins2Av
, Count(tblResults.ResInfo04) AS nIns2
, Min(tblResults.ResInfo04) AS Ins2Min
, Max(tblResults.ResInfo04) AS Ins2max
, Avg(tblResults.ResInfo06) AS HgA1c2Av
, Count(tblResults.ResInfo06) AS nHgA1c2
, Min(tblResults.ResInfo06) AS HgA1c2Min
, Max(tblResults.ResInfo06) AS HgA1c2max
, Avg(tblResults.ResInfo08) AS Waist2Av
, Count(tblResults.ResInfo08) AS nWaist2
, Min(tblResults.ResInfo08) AS Waist2Min
, Max(tblResults.ResInfo08) AS Waist2max
, Avg(tblResults.ResInfo10) AS Hip2Av
, tblResults.ResInfo10 AS nHip2
, Min(tblResults.ResInfo10) AS Hip2Min
, Max(tblResults.ResInfo10) AS Hip2max
, Avg(tblResults.ResInfo12) AS WHRatio2Av
, Count(tblResults.ResInfo12) AS nWHRatio2
, Min(tblResults.ResInfo12) AS WHRatio2Min
, Max(tblResults.ResInfo12) AS WHRatio2max
FROM
tblResults
INNER JOIN
qryRatioResults
ON
tblResults.ResID = qryRatioResults.ResID
GROUP BY
tblResults.SesID
, tblResults.ResInfo10;
I think you asked for the same thing as Jerry - correct? It's posted.
Denise-
Click to expand...
[quoted text clipped - 22 lines]
Gratefully,
Click to expand...
Click to expand...
Click to expand...
 
J

Jerry Whittle

GROUP BY
That's why session 10 probably works right as you are grouping by it and not
the other sessions.

In a nutshell, your table structure is horribly denormalized. About the only
way you might get it to work is with a large set of Union queries. Below is
just two of the sessions joined by a Union. See if it returns records as you
expect. If so, you need to add everything else to it.

SELECT tblResults.SesID
, "TChol1Av" AS TheSession
, Count(tblResults.ResID) AS n
, Avg(tblResults.Tchol2) AS TheAv
, Count(tblResults.Tchol2) AS TheCount
, Min(tblResults.Tchol2) AS TheMin
, Max(tblResults.Tchol2) AS TheMax
FROM tblResults
INNER JOIN qryRatioResults
ON tblResults.ResID = qryRatioResults.ResID
GROUP BY
tblResults.SesID
, tblResults.Tchol2
UNION ALL
SELECT tblResults.SesID
, "HDL2" AS TheSession
, Count(tblResults.ResID)
, Avg(tblResults.HDL2)
, Count(tblResults.HDL2)
, Min(tblResults.HDL2)
, Max(tblResults.HDL2)
FROM tblResults
INNER JOIN qryRatioResults
ON tblResults.ResID = qryRatioResults.ResID
GROUP BY
tblResults.SesID
, tblResults.HDL2 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Denise said:
Thanks for taking the "edge" off my post.

Is it not only the first two that need to be addressed? The Session ID and
the Count.

If you look at the query itself, session 10 worked correctly - all students
counted as one record and all data compiled. All other sessions are spread
out into more than one row with the same Session ID allotted to only a few of
the total number of students.

Thanks for taking the time!

raskew via AccessMonster.com said:
Yow -

Here's a formatted version:
SELECT
tblResults.SesID
, Count(tblResults.ResID) AS n
, Avg(tblResults.Tchol2) AS TChol1Av
, Count(tblResults.Tchol2) AS nChol1
, Min(tblResults.Tchol2) AS TChol1min
, Max(tblResults.Tchol2) AS Tchol1max
, Avg(tblResults.HDL2) AS HDL1Av
, Count(tblResults.HDL2) AS nHDL1
, Min(tblResults.HDL2) AS HDL1min
, Max(tblResults.HDL2) AS HDL1max
, Avg(qryRatioResults.Ratio2) AS Ratio1Av
, Count(qryRatioResults.Ratio2) AS nRatio1
, Min(qryRatioResults.Ratio2) AS Ratio1min
, Max(qryRatioResults.Ratio2) AS Ratio1max
, Avg(tblResults.LDL2) AS LDL1Av
, Count(tblResults.LDL2) AS nLDL1
, Min(tblResults.LDL2) AS LDL1min
, Max(tblResults.LDL2) AS LDL1max
, Avg(tblResults.TG2) AS TG1Av
, Count(tblResults.TG2) AS nTG1
, Min(tblResults.TG2) AS TG1min
, Max(tblResults.TG2) AS TG1max
, Avg(tblResults.Glu2) AS GLu1Av
, Count(tblResults.Glu2) AS nGlu1
, Min(tblResults.Glu2) AS Glu1min
, Max(tblResults.Glu2) AS glu1max
, Avg(tblResults.Wt2) AS Wt1Av
, Count(tblResults.Wt2) AS nWT1
, Min(tblResults.Wt2) AS Wt1min
, Max(tblResults.Wt2) AS Wt1max
, Avg(tblResults.BMI2) AS BMI1Av
, Count(tblResults.BMI2) AS nBMI1
, Min(tblResults.BMI2) AS BMI1min
, Max(tblResults.BMI2) AS BMI1max
, Avg(tblResults.BPsys2) AS BPsys1Av
, Count(tblResults.BPsys2) AS nBPsys1
, Min(tblResults.BPsys2) AS BPsys1min
, Max(tblResults.BPsys2) AS BPsys1max
, Avg(tblResults.BPdia2) AS BPdia1Av
, Count(tblResults.BPdia2) AS nBPdia1
, Min(tblResults.BPdia2) AS BPdia1min
, Max(tblResults.BPdia2) AS BPdia1max
, Avg(tblResults.Pulse2) AS Puls1Av
, Count(tblResults.Pulse2) AS nPuls1
, Min(tblResults.Pulse2) AS Pulse1min
, Max(tblResults.Pulse2) AS Pulse1max
, Avg(tblResults.ResInfo02) AS BFP2Av
, Count(tblResults.ResInfo02) AS nBFP2
, Min(tblResults.ResInfo02) AS BFP2Min
, Max(tblResults.ResInfo02) AS BFP2max
, Avg(tblResults.ResInfo04) AS Ins2Av
, Count(tblResults.ResInfo04) AS nIns2
, Min(tblResults.ResInfo04) AS Ins2Min
, Max(tblResults.ResInfo04) AS Ins2max
, Avg(tblResults.ResInfo06) AS HgA1c2Av
, Count(tblResults.ResInfo06) AS nHgA1c2
, Min(tblResults.ResInfo06) AS HgA1c2Min
, Max(tblResults.ResInfo06) AS HgA1c2max
, Avg(tblResults.ResInfo08) AS Waist2Av
, Count(tblResults.ResInfo08) AS nWaist2
, Min(tblResults.ResInfo08) AS Waist2Min
, Max(tblResults.ResInfo08) AS Waist2max
, Avg(tblResults.ResInfo10) AS Hip2Av
, tblResults.ResInfo10 AS nHip2
, Min(tblResults.ResInfo10) AS Hip2Min
, Max(tblResults.ResInfo10) AS Hip2max
, Avg(tblResults.ResInfo12) AS WHRatio2Av
, Count(tblResults.ResInfo12) AS nWHRatio2
, Min(tblResults.ResInfo12) AS WHRatio2Min
, Max(tblResults.ResInfo12) AS WHRatio2max
FROM
tblResults
INNER JOIN
qryRatioResults
ON
tblResults.ResID = qryRatioResults.ResID
GROUP BY
tblResults.SesID
, tblResults.ResInfo10;
I think you asked for the same thing as Jerry - correct? It's posted.

Denise-

[quoted text clipped - 22 lines]

Gratefully,
Click to expand...
Click to expand...
Click to expand...
 
D

Denise

It was an easy mistake that only a rookie could make. The report required
that each test be entered in the query for Avg, Count, Min & Max.

When I entered the hip measurement, I chose Group instead of Count. When I
changed it to Count the Query righted itself.

Thank you both for taking the time!!

Jerry Whittle said:
That's why session 10 probably works right as you are grouping by it and not
the other sessions.

In a nutshell, your table structure is horribly denormalized. About the only
way you might get it to work is with a large set of Union queries. Below is
just two of the sessions joined by a Union. See if it returns records as you
expect. If so, you need to add everything else to it.

SELECT tblResults.SesID
, "TChol1Av" AS TheSession
, Count(tblResults.ResID) AS n
, Avg(tblResults.Tchol2) AS TheAv
, Count(tblResults.Tchol2) AS TheCount
, Min(tblResults.Tchol2) AS TheMin
, Max(tblResults.Tchol2) AS TheMax
FROM tblResults
INNER JOIN qryRatioResults
ON tblResults.ResID = qryRatioResults.ResID
GROUP BY
tblResults.SesID
, tblResults.Tchol2
UNION ALL
SELECT tblResults.SesID
, "HDL2" AS TheSession
, Count(tblResults.ResID)
, Avg(tblResults.HDL2)
, Count(tblResults.HDL2)
, Min(tblResults.HDL2)
, Max(tblResults.HDL2)
FROM tblResults
INNER JOIN qryRatioResults
ON tblResults.ResID = qryRatioResults.ResID
GROUP BY
tblResults.SesID
, tblResults.HDL2 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Denise said:
Thanks for taking the "edge" off my post.

Is it not only the first two that need to be addressed? The Session ID and
the Count.

If you look at the query itself, session 10 worked correctly - all students
counted as one record and all data compiled. All other sessions are spread
out into more than one row with the same Session ID allotted to only a few of
the total number of students.

Thanks for taking the time!

raskew via AccessMonster.com said:
Yow -

Here's a formatted version:
SELECT
tblResults.SesID
, Count(tblResults.ResID) AS n
, Avg(tblResults.Tchol2) AS TChol1Av
, Count(tblResults.Tchol2) AS nChol1
, Min(tblResults.Tchol2) AS TChol1min
, Max(tblResults.Tchol2) AS Tchol1max
, Avg(tblResults.HDL2) AS HDL1Av
, Count(tblResults.HDL2) AS nHDL1
, Min(tblResults.HDL2) AS HDL1min
, Max(tblResults.HDL2) AS HDL1max
, Avg(qryRatioResults.Ratio2) AS Ratio1Av
, Count(qryRatioResults.Ratio2) AS nRatio1
, Min(qryRatioResults.Ratio2) AS Ratio1min
, Max(qryRatioResults.Ratio2) AS Ratio1max
, Avg(tblResults.LDL2) AS LDL1Av
, Count(tblResults.LDL2) AS nLDL1
, Min(tblResults.LDL2) AS LDL1min
, Max(tblResults.LDL2) AS LDL1max
, Avg(tblResults.TG2) AS TG1Av
, Count(tblResults.TG2) AS nTG1
, Min(tblResults.TG2) AS TG1min
, Max(tblResults.TG2) AS TG1max
, Avg(tblResults.Glu2) AS GLu1Av
, Count(tblResults.Glu2) AS nGlu1
, Min(tblResults.Glu2) AS Glu1min
, Max(tblResults.Glu2) AS glu1max
, Avg(tblResults.Wt2) AS Wt1Av
, Count(tblResults.Wt2) AS nWT1
, Min(tblResults.Wt2) AS Wt1min
, Max(tblResults.Wt2) AS Wt1max
, Avg(tblResults.BMI2) AS BMI1Av
, Count(tblResults.BMI2) AS nBMI1
, Min(tblResults.BMI2) AS BMI1min
, Max(tblResults.BMI2) AS BMI1max
, Avg(tblResults.BPsys2) AS BPsys1Av
, Count(tblResults.BPsys2) AS nBPsys1
, Min(tblResults.BPsys2) AS BPsys1min
, Max(tblResults.BPsys2) AS BPsys1max
, Avg(tblResults.BPdia2) AS BPdia1Av
, Count(tblResults.BPdia2) AS nBPdia1
, Min(tblResults.BPdia2) AS BPdia1min
, Max(tblResults.BPdia2) AS BPdia1max
, Avg(tblResults.Pulse2) AS Puls1Av
, Count(tblResults.Pulse2) AS nPuls1
, Min(tblResults.Pulse2) AS Pulse1min
, Max(tblResults.Pulse2) AS Pulse1max
, Avg(tblResults.ResInfo02) AS BFP2Av
, Count(tblResults.ResInfo02) AS nBFP2
, Min(tblResults.ResInfo02) AS BFP2Min
, Max(tblResults.ResInfo02) AS BFP2max
, Avg(tblResults.ResInfo04) AS Ins2Av
, Count(tblResults.ResInfo04) AS nIns2
, Min(tblResults.ResInfo04) AS Ins2Min
, Max(tblResults.ResInfo04) AS Ins2max
, Avg(tblResults.ResInfo06) AS HgA1c2Av
, Count(tblResults.ResInfo06) AS nHgA1c2
, Min(tblResults.ResInfo06) AS HgA1c2Min
, Max(tblResults.ResInfo06) AS HgA1c2max
, Avg(tblResults.ResInfo08) AS Waist2Av
, Count(tblResults.ResInfo08) AS nWaist2
, Min(tblResults.ResInfo08) AS Waist2Min
, Max(tblResults.ResInfo08) AS Waist2max
, Avg(tblResults.ResInfo10) AS Hip2Av
, tblResults.ResInfo10 AS nHip2
, Min(tblResults.ResInfo10) AS Hip2Min
, Max(tblResults.ResInfo10) AS Hip2max
, Avg(tblResults.ResInfo12) AS WHRatio2Av
, Count(tblResults.ResInfo12) AS nWHRatio2
, Min(tblResults.ResInfo12) AS WHRatio2Min
, Max(tblResults.ResInfo12) AS WHRatio2max
FROM
tblResults
INNER JOIN
qryRatioResults
ON
tblResults.ResID = qryRatioResults.ResID
GROUP BY
tblResults.SesID
, tblResults.ResInfo10;

Denise wrote:
I think you asked for the same thing as Jerry - correct? It's posted.

Denise-

[quoted text clipped - 22 lines]

Gratefully,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Assign Random number to a Record 10
Normalization woes - how do I reproduce a query? 5
Number of records in a group - Access 2003 2
Merge multiple detail records in a single control 3
Need help with setting up a report 4
Newbie needs a direction 9
excluding values in a query 6
Report that takes latest/last entry in a subform ... 0

Top