Radom Selection Query Difficulty 1

[Swi]

I should start by saying that SQL is not my string suit. I can write basic queries but am at a loss when it comes to things like this. After reading the FAQ secion of this forum I saw how to grab random records from a table:

select top X from yourtable
order by rnd(numericalfield)

My question is how can I select a random sampling of a certain database field and make sure I get a minimum amount of lets say 10 of each code in a field and also base this selection on whether or not another field contains lets say an 'X'? I guess that this really should be an insert statement as what I really want to do is write data into another database field in the same table based on a random selection with a max of 10 records per code and an 'X' in another field in the database.

Ex.-
Table has the following information:
Name Address1 Address2 City State Zip XFieldX YOutputFieldY

In this test example I want randomly select a minimum of 10 records from each state that contain an 'X' in XFieldX and then insert a 'Y' in YOutputFieldY.

I hope I explained this good enough. I have found a way to do this in our proprietary software but it is tedious and very time consuming. Any help is greatly appreciated. Thanks.

Swi

 

[jrbarnett]

An insert statement adds a new row to the same table. If you want to update a field in an existing row, you need an update statement.

Anyway, here's how to go about it:

1. Create a new query, switch to SQL view and paste the following code in (replace X with your number of records).

select top X Name, Address1, Address2, City, State, Zip, 
XFieldX, YOutputFieldY
from yourtable 
where XFieldX = 'X' 
order by rnd(numericalfield)

Save this query as qryRandomRecords. If you open it, you will see X random records from your set of data.

2. In the event for your code to update the data (eg a button on screen), use

CurrentDb.Execute "Update qryRandomRecords Set YOutputFieldY = 'Y'"

John

 

[Swi]

Yes, I am sorry, update. I will give it a shot but now they want to do this to a DBF as well which I don't think the above can be done to a DBF as I don't think you can run a query on a query using DBF's.

Swi

 

[Swi]

Also, how would this be implemented to update a minimum of 10 per state. The above query just looks like it would update the top 10 of 1 certain state.

Swi

 

[PHV]

What is the PrimaryKey of your table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Swi]

It is a input record number when the file is imported into the table.

Swi

 

[jrbarnett]

Which field (or fields) is the primary key? Is it shown on the SQL code above?

John

 

[Swi]

It is not in the SQL above. It would be called INPUTRCRNUM.

Swi

 

[jrbarnett]

Without going to an SQL solution (but I'm sure there is a way of doing this), the easiest way is to embed the update for one state in a loop against a distinct list of the states.

Something like the following:

1. Open the VBA editor and create a new standard module

2. Copy and paste the following code in:

Public Function UpdateStateData

Dim db As DAO.Database
Dim rstStates As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb


' Open the list of states
Set rstStates = db.OpenRecordset ("Select distinct state from yourtable order by state")

On Error Resume Next ' because first time round there won't be a temp query to delete

Do while not rstStates.EOF
  strSQL = "select top 11 Name, Address1, Address2, City, State, Zip, XFieldX, YOutputFieldY from yourtable
where XFieldX = 'X' and state = '" & rstStates!state & "' order by rnd(numericalfield)
  DoCmd.DeleteObject acQuery, "~statelist"
  Set qdf = db.CreateQueryDef ("~statelist", strSQL)
  CurrentDb.Execute "Update ~statelist set YOutputFieldY = 'Y'"
  rstStates.MoveNext
Loop
rstStates.Close
Set db = Nothing

End Function

If you're stuck for the numerical field to sort by, I suggest that you use InputCRNum.
I used 11 as it is the smallest whole number above 10 that satisfies your requirements. You can change this if needed.

John

 

[Swi]

Thanks, I will try this out when I get a chance.

Swi