Obtain Top Value from Table

I am attempting to pull a random set of records using a Random Number and the
Top Value function. The Top Value changes depending on the specific group I
am looking at so I would like to obtain the Top Value from a table rather
than entering manually.

First, the appropriate statistically significant sample size is determined
for each group in my table by the query below. The sample size and group
information are added to [tbl_tt_sample_size].

INSERT INTO tbl_tt_sample_size ( MonthSample, Code, Fac, Sample )
SELECT
DateSerial(Year(Date()),Month(Date()),0) AS MonthSer,
tbl_tt_transfusions.Code,
tbl_tt_transfusions.Fac,

((0.5*(1-0.5))*Count([Unit#]))/((0.5*(1-0.5))+((0.1/1.96)*(0.1/1.96))*(Count([Unit#])-1)) AS Sample
FROM tbl_tt_transfusions
WHERE (((tbl_tt_transfusions.ISSUE_FLAG)="T") AND
((tbl_tt_transfusions.PtType) Not Like "O") AND ((tbl_tt_transfusions.UseDT)
Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),1)))
GROUP BY DateSerial(Year(Date()),Month(Date()),0), tbl_tt_transfusions.Code,
tbl_tt_transfusions.Fac;

Sample data in [tbl_tt_sample_size]:
MonthSample CODE Fac Sample
9/30/2009 FFP C 34
9/30/2009 FFP H 75
9/30/2009 RC C 67
9/30/2009 RC H 87
10/31/2009 FFP C 35
10/31/2009 FFP H 65
10/31/2009 RC C 72
10/31/2009 RC H 88
(the [MonthSample] field is the last date of each month)

Next, a Random Number is added to field [tbl_tt_transfusions.].[RandomNo] by
the query below for later reference.

UPDATE tbl_tt_transfusions SET tbl_tt_transfusions.RandomNo =
CLng(Rnd([TransNo])*1000000)
WHERE (((tbl_tt_transfusions.RandomNo) Is Null));

Now I have a Random Number and Sample Size to work with. I can do this fine
with the query below, but I have to manually look up and change the Top Value
for each group each time.

SELECT TOP 87 tbl_tt_transfusions.RandomNo, tbl_tt_transfusions.Fac,
tbl_tt_transfusions.CODE, tbl_tt_transfusions.UseDT,
tbl_tt_transfusions.TransNo
FROM tbl_tt_transfusions
WHERE (((tbl_tt_transfusions.Fac)="H") AND ((tbl_tt_transfusions.CODE)="RC")
AND ((tbl_tt_transfusions.UseDT) Between #9/1/2009# And #10/1/2009#))
ORDER BY tbl_tt_transfusions.RandomNo;

Is there a way to "grab" [tbl_tt_sample_size].[Sample] to use as my Top
Value in the query above?

Select Top N records where N is variable ( a parameter)

One method that can be used is to use a ranking query to assign a number to
the records and then use the ranking to return N records. Here are two sample
queries built on that idea.

'Probably not updatable
SELECT MyTable.*
FROM MyTable INNER JOIN
(
SELECT A.MyField, Count(B.MyField) as TheCount
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField B.MyField
GROUP BY A.MyField
) as Ranking
ON MyTable.MyField = Ranking.MyField
WHERE Ranking.TheCount [Top How Many Number]

'Probably updatable
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) [Top How Many Number])

So if you replace MyTable with the name of your query (minus the TOP n) this
might work for you. Your other option would be to build the query's SQL
string using VBA.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

yator wrote:
I am attempting to pull a random set of records using a Random Number and the
Top Value function. The Top Value changes depending on the specific group I
am looking at so I would like to obtain the Top Value from a table rather
than entering manually.

First, the appropriate statistically significant sample size is determined
for each group in my table by the query below. The sample size and group
information are added to [tbl_tt_sample_size].

INSERT INTO tbl_tt_sample_size ( MonthSample, Code, Fac, Sample )
SELECT
DateSerial(Year(Date()),Month(Date()),0) AS MonthSer,
tbl_tt_transfusions.Code,
tbl_tt_transfusions.Fac,

((0.5*(1-0.5))*Count([Unit#]))/((0.5*(1-0.5))+((0.1/1.96)*(0.1/1.96))*(Count([Unit#])-1)) AS Sample
FROM tbl_tt_transfusions
WHERE (((tbl_tt_transfusions.ISSUE_FLAG)="T") AND
((tbl_tt_transfusions.PtType) Not Like "O") AND ((tbl_tt_transfusions.UseDT)
Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),1)))
GROUP BY DateSerial(Year(Date()),Month(Date()),0), tbl_tt_transfusions.Code,
tbl_tt_transfusions.Fac;

Sample data in [tbl_tt_sample_size]:
MonthSample CODE Fac Sample
9/30/2009 FFP C 34
9/30/2009 FFP H 75
9/30/2009 RC C 67
9/30/2009 RC H 87
10/31/2009 FFP C 35
10/31/2009 FFP H 65
10/31/2009 RC C 72
10/31/2009 RC H 88
(the [MonthSample] field is the last date of each month)

Next, a Random Number is added to field [tbl_tt_transfusions.].[RandomNo] by
the query below for later reference.

UPDATE tbl_tt_transfusions SET tbl_tt_transfusions.RandomNo =
CLng(Rnd([TransNo])*1000000)
WHERE (((tbl_tt_transfusions.RandomNo) Is Null));

Now I have a Random Number and Sample Size to work with. I can do this fine
with the query below, but I have to manually look up and change the Top Value
for each group each time.

SELECT TOP 87 tbl_tt_transfusions.RandomNo, tbl_tt_transfusions.Fac,
tbl_tt_transfusions.CODE, tbl_tt_transfusions.UseDT,
tbl_tt_transfusions.TransNo
FROM tbl_tt_transfusions
WHERE (((tbl_tt_transfusions.Fac)="H") AND ((tbl_tt_transfusions.CODE)="RC")
AND ((tbl_tt_transfusions.UseDT) Between #9/1/2009# And #10/1/2009#))
ORDER BY tbl_tt_transfusions.RandomNo;

Is there a way to "grab" [tbl_tt_sample_size].[Sample] to use as my Top
Value in the query above?

John, thanks for the response. I am trying to work through this step by
step....

I tried your second example just to test as follows:

SELECT q_tt_z_random_test_a.*
FROM q_tt_z_random_test_a
WHERE RandomNo in
(SELECT A.RandomNo
FROM q_tt_z_random_test_a as A LEFT JOIN q_tt_z_random_test_a As B
ON A.RandomNo B.RandomNo
GROUP BY A.RandomNo
HAVING Count(B.RandomNo) 87);

This returns all 87 records as expected.
I then tried replacing the "Top Number" with my calculation as follows:

SELECT q_tt_z_random_test_a.*
FROM q_tt_z_random_test_a
WHERE RandomNo in
(SELECT A.RandomNo
FROM q_tt_z_random_test_a as A LEFT JOIN q_tt_z_random_test_a As B
ON A.RandomNo B.RandomNo
GROUP BY A.RandomNo
HAVING Count(B.RandomNo)
((0.5*(1-0.5))*Count([A].[RandomNo]))/((0.5*(1-0.5))+((0.1/1.96)*(0.1/1.96))*(Count([A].[RandomNo])-1)));

This returns the 87th record only. What is the difference? The calculation
should return "87" as a result.
thanks

Well is the calculation returning 87? Probably not.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

yator wrote:
John, thanks for the response. I am trying to work through this step by
step....

I tried your second example just to test as follows:

SELECT q_tt_z_random_test_a.*
FROM q_tt_z_random_test_a
WHERE RandomNo in
(SELECT A.RandomNo
FROM q_tt_z_random_test_a as A LEFT JOIN q_tt_z_random_test_a As B
ON A.RandomNo B.RandomNo
GROUP BY A.RandomNo
HAVING Count(B.RandomNo) 87);

This returns all 87 records as expected.
I then tried replacing the "Top Number" with my calculation as follows:

SELECT q_tt_z_random_test_a.*
FROM q_tt_z_random_test_a
WHERE RandomNo in
(SELECT A.RandomNo
FROM q_tt_z_random_test_a as A LEFT JOIN q_tt_z_random_test_a As B
ON A.RandomNo B.RandomNo
GROUP BY A.RandomNo
HAVING Count(B.RandomNo)
((0.5*(1-0.5))*Count([A].[RandomNo]))/((0.5*(1-0.5))+((0.1/1.96)*(0.1/1.96))*(Count([A].[RandomNo])-1)));

This returns the 87th record only. What is the difference? The calculation
should return "87" as a result.
thanks

You are correct. As written in this query it returns "1".
It must be the Count function nested in the calculation:

((0.5*(1-0.5))*Count([A].[TransNo]))/((0.5*(1-0.5))+((0.1/1.96)*(0.1/1.96))*(Count([A].[TransNo])-1))

is Counting only what is returned from the Group By clause. I tried a
simplified calculation: (Count(A.TransNo)) and get the same result, only the
top record is returned.

How would I get my calculation into the query?

I tried changing the GROUP BY to: GROUP BY A.Code, but this gives me a error
that "RandomNo" is not part of an aggregate function.

I don't understand what you are trying to calculate with Count(A.TransNo) or
why you are trying to calculate some value.

You might consider using the DLookup function to get the sample size from your
table.
DLookUp("Sample","tbl_tt_sample_size","You need to build a where clause")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

yator wrote:
You are correct. As written in this query it returns "1".
It must be the Count function nested in the calculation:

((0.5*(1-0.5))*Count([A].[TransNo]))/((0.5*(1-0.5))+((0.1/1.96)*(0.1/1.96))*(Count([A].[TransNo])-1))

is Counting only what is returned from the Group By clause. I tried a
simplified calculation: (Count(A.TransNo)) and get the same result, only the
top record is returned.

How would I get my calculation into the query?

I tried changing the GROUP BY to: GROUP BY A.Code, but this gives me a error
that "RandomNo" is not part of an aggregate function.

I tried DLookup as below but get a syntax error for the WHERE statement.

SELECT q_tt_z_random_test_a.*
FROM q_tt_z_random_test_a
WHERE RandomNo in
(SELECT A.RandomNo
FROM q_tt_z_random_test_a as A LEFT JOIN q_tt_z_random_test_a As B
ON A.RandomNo B.RandomNo
GROUP BY A.RandomNo
HAVING Count(B.RandomNo) DLookUp("Sample","tbl_tt_sample_size",
"[MonthSample]=#9/30/2009#));

oops, just realized I was missing a " after the DLookup where clause.
With that fixed, I still only get 1 record returned, the 87th.
I know that the field [MonthSample]=#9/30/2009# is equal to 87.
When I run the DLookup on its own it returns a "1" ???

"yator" wrote:

I tried DLookup as below but get a syntax error for the WHERE statement.

SELECT q_tt_z_random_test_a.*
FROM q_tt_z_random_test_a
WHERE RandomNo in
(SELECT A.RandomNo
FROM q_tt_z_random_test_a as A LEFT JOIN q_tt_z_random_test_a As B
ON A.RandomNo B.RandomNo
GROUP BY A.RandomNo
HAVING Count(B.RandomNo) DLookUp("Sample","tbl_tt_sample_size",
"[MonthSample]=#9/30/2009#));

Ok I got it, had a problem with the underlying query. Final version that
works fine and is updateable follows. This returns the correct number of
records based on the pre-determined sample size stored in tbl_tt_sample_size
for a specific date, code and fac. Thanks for all you help!!

SELECT q_tt_08hrc.*
FROM q_tt_08hrc
WHERE RandomNo in
(SELECT A.RandomNo
FROM q_tt_08hrc as A LEFT JOIN q_tt_08hrc As B
ON A.RandomNo B.RandomNo
GROUP BY A.RandomNo
HAVING Count(B.RandomNo) DLookUp("Sample","tbl_tt_sample_size",
"[MonthSample]=DateSerial(Year(Date()),Month(Date()),0) AND [code]='RC' AND
[Fac]='C'"))
ORDER BY RandomNo DESC;

Your original post had
MonthSample CODE Fac Sample
9/30/2009 FFP C 34
9/30/2009 FFP H 75
9/30/2009 RC C 67
9/30/2009 RC H 87

So At a minimum I would think you would need to include Code and Fac in the
criteria and not just date.

DLookUp("Sample","tbl_tt_sample_size","[MonthSample]=#9/30/2009# AND
Code=""RC"" AND FAC = ""H""")

If DLookup is returning one, then you probably have a monthSample for
9/30/2009 that has a sample value of 1.

The trick here is to be able to get the date, code and Fac from someplace else
in the query. Probably from q_tt_z_random_test_a if they exist.

You might be better off using VBA to construct your Top n query as needed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

yator wrote:
oops, just realized I was missing a " after the DLookup where clause.
With that fixed, I still only get 1 record returned, the 87th.
I know that the field [MonthSample]=#9/30/2009# is equal to 87.
When I run the DLookup on its own it returns a "1" ???

"yator" wrote:

I tried DLookup as below but get a syntax error for the WHERE statement.

SELECT q_tt_z_random_test_a.*
FROM q_tt_z_random_test_a
WHERE RandomNo in
(SELECT A.RandomNo
FROM q_tt_z_random_test_a as A LEFT JOIN q_tt_z_random_test_a As B
ON A.RandomNo B.RandomNo
GROUP BY A.RandomNo
HAVING Count(B.RandomNo) DLookUp("Sample","tbl_tt_sample_size",
"[MonthSample]=#9/30/2009#));