Maarkr said:
Two tables... one is a lookup table with countries, like USA, UK, Canada,
Australia. The other is a table of names, country, etc, with only names
filled in. I want to update the null country values with random country
values from the lookup table. (generating a table of dummy values from
lookup tables) I tried this but it said the set is not updateable:
UPDATE Countries INNER JOIN Members ON Countries.Country= Members.Country
SET Members.Country = (SELECT top 1 Countries.Country FROM Countries ORDER BY
Rnd([CountryNo])
WHERE (((Members.Country) Is Null));
Or if u know of another way to enter the country values from the lookup into
the empty field. Thx
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Yeah, Access (JET) doesn't like standard SQL syntax for most UPDATEs.
You have to use a domain aggregate function in place of the (SELECT...).
But, since there isn't a DTop() d-aggregate function you won't be able
to do it; but, you might not need it.
A shotgun approach would be to use a Cartesian product, but then you'd
have Nbr_of_members X Nbr_of_Countries rows (records) in the result set:
UPDATE Countries, Members
SET Members.Country = Countries.Country
WHERE Members.Country Is Null
Another way to assign random countries would be thru a VBA routine. I'd
create two Recordsets: one of the Countries.Country; the other of the
Members.Country. Then I'd create a random number array of the
Countries' recordset (each record number would be randomly assigned to
each element of the array). The record number would represent the
position in the recordset. Then I'd iterate thru the Members recordset
and use the random numbers from the array to read the country and assign
it to the Members.Country. You could load the Countries recordset into
an array using the GetRows() function. Then just use the random array
value to assign the Countries.Country to the Members.Country (the VBA
below is just a sketch of what to try - it is not complete):
dim CountriesArray() As Variant, i as integer, varCount as Variant
' Open the Countries recordset
..............
' get the number of rows
rsCountries.MoveLast
varCount = rsCountries.RecordCount
rsCountries.MoveFirst
CountriesArray = rsCountries.GetRows(varCount)
' Open the Members recordset
...........
i = LBound(random_nbr) ' initialize the index for the random # array
do while not rsMembers.EOF
rsMembers.Edit
' the zero in the CountriesArray indicates the first Field
rsMembers!Country = CountriesArray(0, random_nbr(i))
rsMembers.Update
rsMembers.MoveNext
i = i + 1
loop
Read the Access VBA Help article "GetRows Method" for info on how to use
that method.
The above assumes a Countries query like this:
SELECT Country FROM Countries;
And a Members query like this:
SELECT Country FROM Members;
Here's the random number array generator:
Sub getRandomNumbers(ByVal lo As Integer, ByVal hi As Integer, _
ByVal toSelect As Integer, result() As Integer)
' Purpose:
' Return an indicated number of unique random numbers
' from a defined population.
' In:
' lo The bottom number in the population
' hi The top number in the population
' e.g: lo = 73, hi = 250
' toSelect The number of items between lo & hi to return.
' Out:
' result() The resulting array of integers
' Created:
' mgf 25may99
' Modified:
'
ReDim items(lo To hi) As Boolean
Dim selected As Integer
Dim num As Integer
' Seed the randomizer
Randomize
' Generate the array of unique, random items
Do While selected < toSelect
' Get a number between lo and the hi boundaries
' * From the VBA Help file on Rnd()*
num = Int((hi - lo + 1) * Rnd + lo)
If items(num) = False Then
' Mark the item as selected
items(num) = True
' Keep track of the number of items selected.
selected = selected + 1
' Load the results array
result(selected) = num
End If
Loop
End Sub
Good luck,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSam/+4echKqOuFEgEQK97gCg9bfA7pyvtHfDhAGeaVhXZgwUTIoAmwSI
Oqa+lM03dzt4ODToOTw32NZg
=bknD
-----END PGP SIGNATURE-----
