Obtain Top Value from Table

Y

yator

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?
 
J

John Spencer

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
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?
Click to expand...
 
Y

yator

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
 
J

John Spencer

Well is the calculation returning 87? Probably not.


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

yator

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.
 
J

John Spencer

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
 
Y

yator

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#));
 
Y

yator

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" ???
 
Y

yator

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;
 
J

John Spencer

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
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 said:
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#));
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

DateSerial-Calculating a date 35 days from today 5
I need to find top 3 records from my table 7
Grouped top values help 1
Print macro Excel 2003 used to work, now crashes 1
Hide Columns Based On Date Value 1
Nested Do...Loops and Date Cell Defined as Date in VBA code 4
Unexplainable results from my vlookup 10
Input boxes 5

Top