Selecting Records

L

LisaInAz

I tried to research and I am not sure if I should start a new thread in a
different forum (I found Select N when N is a variable but couldn't get it to
work)

I would like to pass the number of records that I need for each unique
entity via form/table, below is the SQL that is working however, I have 36
different entities where I need to randomly select X data (for example I
would like to pass 1883) I don't need to necessarily SELECT TOP if I could
figure out how add to/subtract from a counter for random selection . <using
SQL server
is not an option; we are using Access 2002 SP3>...

SELECT TOP 1883 Rnd([CountNum]) AS RandomValue, tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

I have found a few items

Such as

Dim intRecCount As Integer
intRecCount = DCount("*", "tblTest")

&
****************
Private Function TopParameter()

Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"

strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"

Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)


End Function

However, my knowledge is limited and I can not get them to work...
Thanks for your help in advance
 
D

David H

If you do need a varying number of records, you could dynamically create the
SQL Statement for each record as in...

NumberOfRecords = [Random Number Generator Code]
SQLStatement = "Select Top " & NumberOfRecords & " Name, Address, ..."

The statement "SELECT TOP 1000 [AddressLine1]" tells Access that you only
want the top 1000 records.
 
L

LisaInAz

That is my struggle I don't know how to convert this:

SELECT TOP 1833 Rnd([CountNum]) AS RandomValue, tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

Into something that works...
[NbrofPrimstoPull] is a numeric field on a table.

When I try this I get an error "SELECT statement includes a reserved word..."

SELECT TOP & [NbrofPrimstoPull] & Rnd([CountNum]) AS RandomValue,
tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

I know it is simpiler than I am making it out to be...just don't know how to
get there

David H said:
If you do need a varying number of records, you could dynamically create the
SQL Statement for each record as in...

NumberOfRecords = [Random Number Generator Code]
SQLStatement = "Select Top " & NumberOfRecords & " Name, Address, ..."

The statement "SELECT TOP 1000 [AddressLine1]" tells Access that you only
want the top 1000 records.

LisaInAz said:
I tried to research and I am not sure if I should start a new thread in a
different forum (I found Select N when N is a variable but couldn't get it to
work)

I would like to pass the number of records that I need for each unique
entity via form/table, below is the SQL that is working however, I have 36
different entities where I need to randomly select X data (for example I
would like to pass 1883) I don't need to necessarily SELECT TOP if I could
figure out how add to/subtract from a counter for random selection . <using
SQL server
is not an option; we are using Access 2002 SP3>...

SELECT TOP 1883 Rnd([CountNum]) AS RandomValue, tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

I have found a few items

Such as

Dim intRecCount As Integer
intRecCount = DCount("*", "tblTest")

&
****************
Private Function TopParameter()

Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"

strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"

Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)


End Function

However, my knowledge is limited and I can not get them to work...
Thanks for your help in advance
Click to expand...
Click to expand...
 
D

David H

INTO is a KEY WORD and thus reserved. You'll need to encapsulate that
specific line with brackets as shown below. Spaces in any object name are
bad, bad, bad.

I think that there might be a few other issues, but add the brackets and
post back if you're still having problems.

LisaInAz said:
That is my struggle I don't know how to convert this:

SELECT TOP 1833 Rnd([CountNum]) AS RandomValue, tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

Into something that works...
[NbrofPrimstoPull] is a numeric field on a table.

When I try this I get an error "SELECT statement includes a reserved word..."

SELECT TOP & [NbrofPrimstoPull] & Rnd([CountNum]) AS RandomValue,
tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, >tbl002_F1BWDATA.DISTRICT,
Click to expand...

[tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO]
tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

I know it is simpiler than I am making it out to be...just don't know how to
get there

David H said:
If you do need a varying number of records, you could dynamically create the
SQL Statement for each record as in...

NumberOfRecords = [Random Number Generator Code]
SQLStatement = "Select Top " & NumberOfRecords & " Name, Address, ..."

The statement "SELECT TOP 1000 [AddressLine1]" tells Access that you only
want the top 1000 records.

LisaInAz said:
I tried to research and I am not sure if I should start a new thread in a
different forum (I found Select N when N is a variable but couldn't get it to
work)

I would like to pass the number of records that I need for each unique
entity via form/table, below is the SQL that is working however, I have 36
different entities where I need to randomly select X data (for example I
would like to pass 1883) I don't need to necessarily SELECT TOP if I could
figure out how add to/subtract from a counter for random selection . <using
SQL server
is not an option; we are using Access 2002 SP3>...

SELECT TOP 1883 Rnd([CountNum]) AS RandomValue, tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

I have found a few items

Such as

Dim intRecCount As Integer
intRecCount = DCount("*", "tblTest")

&
****************
Private Function TopParameter()

Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"

strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"

Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)


End Function

However, my knowledge is limited and I can not get them to work...
Thanks for your help in advance
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

You can use a custom VBA function to build and execute the SQL on the
fly. That might be the easiest way to handle the situation and get
fairly quick performance.

The following untested function should work for you. Put this function
into a VBA module and save the module with a name other than fBuildSQL.
Public Function fBuildSQL(LNumber As Long)
Dim strSQL As String
Dim db As DAO.Database

On Error GoTo Proc_Error

strSQL = "SELECT TOP " & LNumber & " Rnd([CountNum]) AS RandomValue" & _
", CountNum, [Unique}, [UniqueTwo], DISTRICT, ELIG, PRIM_ID" & _
" INTO tbl003C_F1BWDATA" & _
" FROM tbl002_F1BWDATA" & _
" WHERE (((randomizer())=0))" & _
" ORDER BY Rnd([CountNum]) DESC"

Set db = CurrentDb()
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & " records were created in tbl003C_F1BWDATA"

Set db = Nothing
Exit Function

Proc_Error:
MsgBox Err.Number & ": " & Err.Description
End Function


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

That is my struggle I don't know how to convert this:

SELECT TOP 1833 Rnd([CountNum]) AS RandomValue, tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

Into something that works...
[NbrofPrimstoPull] is a numeric field on a table.

When I try this I get an error "SELECT statement includes a reserved word..."

SELECT TOP & [NbrofPrimstoPull] & Rnd([CountNum]) AS RandomValue,
tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

I know it is simpiler than I am making it out to be...just don't know how to
get there

David H said:
If you do need a varying number of records, you could dynamically create the
SQL Statement for each record as in...

NumberOfRecords = [Random Number Generator Code]
SQLStatement = "Select Top " & NumberOfRecords & " Name, Address, ..."

The statement "SELECT TOP 1000 [AddressLine1]" tells Access that you only
want the top 1000 records.

LisaInAz said:
I tried to research and I am not sure if I should start a new thread in a
different forum (I found Select N when N is a variable but couldn't get it to
work)

I would like to pass the number of records that I need for each unique
entity via form/table, below is the SQL that is working however, I have 36
different entities where I need to randomly select X data (for example I
would like to pass 1883) I don't need to necessarily SELECT TOP if I could
figure out how add to/subtract from a counter for random selection . <using
SQL server
is not an option; we are using Access 2002 SP3>...

SELECT TOP 1883 Rnd([CountNum]) AS RandomValue, tbl002_F1BWDATA.CountNum,
tbl002_F1BWDATA.Unique, tbl002_F1BWDATA.UniqueTwo, tbl002_F1BWDATA.DISTRICT,
tbl002_F1BWDATA.ELIG, tbl002_F1BWDATA.PRIM_ID INTO tbl003C_F1BWDATA
FROM tbl002_F1BWDATA
WHERE (((randomizer())=0))
ORDER BY Rnd([CountNum]) DESC;

I have found a few items

Such as

Dim intRecCount As Integer
intRecCount = DCount("*", "tblTest")

&
****************
Private Function TopParameter()

Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"

strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"

Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)


End Function

However, my knowledge is limited and I can not get them to work...
Thanks for your help in advance
Click to expand...
Click to expand...
Click to expand...
 
Top