random function

[anil]

i need the help in one query.
Problem is - Let there are 70 'Towns' and each town has fixed number of
'Sites'.e.g 'A' has 10 'Sites','B' has 12 Sites ,'C' has 15 Sites and
so on.In every Town water sampling is done.Say from Town 'A' out of
'10' Sites 3 are sampled and in 'B' out of '12' Sites ,2 are
sampled.Hence there are Fixed NUmber of Towns,FiXed Number of Sites and
Fixed Number of Sample_Sites.So I need to generate random Sample_Sites
every week so that Sample_sites selected this week are not in previous
week.
I tried few Functions LIke 'How to fill a Table with Random Record from
other table' but not successful.Please can u help.
thanks
anil

 

[Duane Hookom]

You can't "generate random Sample_Sites every week so that Sample_sites
selected this week are not in previous week". That wouldn't be random.

Duane Hookom
MS Access MVP

 

[anil]

But if we want to generate random sites for one week and then again run
the query next week ,won't it be random as compared to last week.
say if it won't be random next week , can you please help for
generating randomly for once .
thanks
anil

 

[Duane Hookom]

There should be lots of examples on generating random records in queries if
you search the web. If you exclude records this week that were selected last
week then your selection is no longer random.

Duane

 

[John Spencer]

Dear anil,

I believe you want a random selection of the sites that have not been
sampled during a specified time frame (the previous week). Further, you
know you need a specific number of sites for each "town" per period.

Do you have a table something like the following
Table: SamplingFreq
Field: TownID (identifies the town)
Field: NumberPerWeek (identifies the number of sites to be sampled in a
period)

One problem I see is that the NumberPerWeek must be less than half the total
number available in any town. If not, then you have added some complexity
to the solution.

Without more details on your table structure it is hard to offer you a
complete solution.

 

[anil]

the tables look like this : table 1= tblTown
townCode name sample sites
ara ararat 3
bri Brim 2 and so on
second table tblSite look like this
SiteID SiteCode SiteAddress
1 ara201 23 High Street ,Ararat
2 ara202 24 high st,ararat
and so on .there are 74 towns and and 600 site with respect to Town.
Problem is in Town-Ararat there are 20 fixed sites out of which 3
sites are to be sampled each week.now we must genearte randomly 3 sites
out of 20 sites for 1Town - Ararat.Similarly we need to generate sites
for 74 Town.

 

[John Spencer]

You still haven't answered the question of time frame.

For example, Ararat has 20 sites and you want to sample 3 sites each week
Say you sample 1, 5, and 7 in week 1 and 8, 13, and 20 in week 2.
Now in week 3, are 1, 5, and 7 back in the potential sites to be sampled?
Or do you want to sample all the sites in random order and then start over
with an new random order.

Also, does tblSite have a last reviewed date? Does tblSite have a planned
review date?

Here is a quote and some code from MVP John Vinson that may help you assign
a random number to all your rows.
Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);


Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces
Access
to give you a new random number for every row. If you don't have a numeric
field available then you can use RndNum(Len([SomeField])) to force a number
to be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

 

[anil]

thanks for your help.Regarding ur question.Suppose if we select 1,5,7
in week 1 ,then in week 2 we might select next 3 from remaining 17
sites.thus in week 3 we can allow week 1 sites to come back and again
select 3 sites from 17 in week 3 .only important is that not to have
last 3 sites in next week.
No tblSite or tblTown do not have date field but we might create new
temperory table and compare with it.

 

[John Spencer]

IF you want to eliminate the sites visited in the previous week, you must have
some way of recording that the sites were visited in the previous week. A date
field that contains the last visit date would probably be the easiest and best
way to do this. If you don't record that information somewhere then you cannot
exclude the sites that were visited from the selection process.

Also, it appears that you are storing two valuesw in Site Code - The townCode
and an incremental number. This is going to make life tougher since you have to
figure out how much of the site code to use to match the town code.

This query returns the potential sites with a random number

QueryOne:
SELECT tblSite.SiteID
, CLng(rndNum(SiteID) * 1000)+1 as Random
, TownCode
, [Sample Sites]
FROM TblSite INNER JOIN tblTown
ON tblSite.SiteCode LIKE tblTown.TownCode & "*"
WHERE tblSite.LastVisit is Not Null or
tblSite.LastVisit < DateAdd("d",1-WeekDay(Date()),Date())


QueryTwo:
SELECT Q.SiteID, TownCode
FROM QueryOne as Q
WHERE [Sample Sites] <=
DCOUNT("*","QueryOne","Random<=" & Q.Random &
" and TownCode=""" & Q.TownCode & """")

You could do the DCount as a coordinated subquery.
SELECT COUNT(*) FROM QueryOne as Q2 WHERE Q2.Random <=Q1.Random AND Q2.TownCode
= Q1.TownCode

Of course all of the above is UNTESTED AIR CODE and may not work at all.

 

[John Spencer]

I realized after I posted this that you would need to save the results of query
one into a table. The rndNum function would get called everytime that queryOne
was called in the subquery or in the DCount and would generate new random
numbers each time it was called.

Perhaps you need an additional table or an additional field in tblSite.
Assuming an additional field in tblSite, you could use a query to update the
random number value before you run the select query. Then you can use the
stored value in the table.

IF you want to eliminate the sites visited in the previous week, you must have
some way of recording that the sites were visited in the previous week. A date
field that contains the last visit date would probably be the easiest and best
way to do this. If you don't record that information somewhere then you cannot
exclude the sites that were visited from the selection process.

Also, it appears that you are storing two valuesw in Site Code - The townCode
and an incremental number. This is going to make life tougher since you have to
figure out how much of the site code to use to match the town code.

This query returns the potential sites with a random number

QueryOne:
SELECT tblSite.SiteID
, CLng(rndNum(SiteID) * 1000)+1 as Random
, TownCode
, [Sample Sites]
FROM TblSite INNER JOIN tblTown
ON tblSite.SiteCode LIKE tblTown.TownCode & "*"
WHERE tblSite.LastVisit is Not Null or
tblSite.LastVisit < DateAdd("d",1-WeekDay(Date()),Date())

QueryTwo:
SELECT Q.SiteID, TownCode
FROM QueryOne as Q
WHERE [Sample Sites] <=
DCOUNT("*","QueryOne","Random<=" & Q.Random &
" and TownCode=""" & Q.TownCode & """")

You could do the DCount as a coordinated subquery.
SELECT COUNT(*) FROM QueryOne as Q2 WHERE Q2.Random <=Q1.Random AND Q2.TownCode
= Q1.TownCode

Of course all of the above is UNTESTED AIR CODE and may not work at all.

thanks for your help.Regarding ur question.Suppose if we select 1,5,7
in week 1 ,then in week 2 we might select next 3 from remaining 17
sites.thus in week 3 we can allow week 1 sites to come back and again
select 3 sites from 17 in week 3 .only important is that not to have
last 3 sites in next week.
No tblSite or tblTown do not have date field but we might create new
temperory table and compare with it.

 

[anil]

thanks for ur help.i will talk to manger and get more help .
thanks
anil

 

[anil]

thanks for ur help.i will talk to manger and get more help .
thanks
anil

 

[anil]

thanks for ur help.i write the module function to get the output which
is as :
Sub MySecondConnection()
Dim con1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim recset2 As ADODB.Recordset
Dim recset3 As ADODB.Recordset
Dim strSQL As String
Dim strSQLforSampleSite As String
Dim strTownCode As String
Dim strsearch As String
Dim noOfSampleSites As Integer

Set con1 = CurrentProject.Connection

'strsearch = InputBox("Enter the Town to find", "Search Creteria")
strTownCode = "SELECT tblTown.TownCode FROM tblTown;"
Set recset3 = New ADODB.Recordset
recset3.Open strTownCode, con1

If recset3.EOF Then
MsgBox "No Sites"
Exit Sub
Else
Do Until recset3.EOF
strSQLforSampleSite = "SELECT tblTown.SampleSites FROM
tblTown where tblTown.towncode = '" & recset3(0) & "'"
Set recset2 = New ADODB.Recordset
recset2.Open strSQLforSampleSite, con1
If recset2.EOF = False Then
noOfSampleSites = recset2(0)
Else
MsgBox "Sorry !! The Sample sites are not specified"
Exit Sub
End If

strSQL = " SELECT TOP " & noOfSampleSites & "
tblSite.SiteCode, tblSite.SiteAddress, tblTown.Town,
tblTown.SampleSites " & _
" FROM tblTown INNER JOIN tblSite ON
tblTown.TownCode = tblSite.TownCode " & _
" WHERE ((Randomizer() = 0) And (tblTown.Towncode
= '" & recset3(0) & "')) " & _
" ORDER BY Rnd(IsNull(tblTown.Towncode)*0+1);"

Set recset1 = New ADODB.Recordset
recset1.Open strSQL, con1
Do Until recset1.EOF
Debug.Print recset1.Fields("SiteCode") & " - " &
recset1.Fields("SiteAddress")
'MsgBox ("SiteCode") & " - " &
recset1.Fields("SiteAddress")
recset1.MoveNext
Loop
Debug.Print "---------------------------------------------"
recset1.Close

recset3.MoveNext
Loop
End If
con1.Close
Set con1 = Nothing
Set recset1 = Nothing
End Sub
Now i get the result in immediate window.i need to get result in report
or form.can u please help to change the code to get the output in
report.if possible ,can i get output in excel or word.
thanks
anil