Update fields with equal number 1

[jcarmody]

I have an update query that sets a Y/N field to Y, and updates a date field with today's date. I now need to update another field with a value of 1,2 or 3. I need an equal number of 1's, 2's and 3's, so the random number function doesn't seem to be the answer.

jcarmody

 

[BNPMike]

Allocate a random number to each record. Then sort the data on the random field and see where the first 3rd stop (and the second third). Let's say its 0.292 and 0.817. Then amend the number to 1, 2 or 3 using an IIF statement like IIF (RandField<0.293,1,(IIF (RandField > 0.817),3 etc etc.

 

[Crowley16]

does it matter what order the 1/2/3's come in?

you can use (count(*) MOD 3) + 1 to get a repeating pattern of 1,2,3,1,2,3...

--------------------
Procrastinate Now!

 

[jcarmody]

Crowley16 -

No it doesn't matter what order. Where do I insert the (count(*) MOD 3) + 1? In my update query? I tried that and it didn't seem to work, the fields still are zero.

Jcarmody

 

[Crowley16]

your update query should look something like:

insert into tblName(field1, field2...) Values(val1, ((count(*) MOD 3) + 1)...)

you should use Count(IDField) whatever your idfield is instead of count(*)


--------------------
Procrastinate Now!

 

[jcarmody]

Crowley16 -

Well, you've probably guessed it, I'm fairly new to all this stuff. Here is my query:

UPDATE Source INNER JOIN [Referral Table] ON Source.[Source ID] = [Referral Table].Source SET [Referral Table].[Initial Letter Date] = Date(), [Referral Table].[Mail Sent] = 1,[Referral Table].FSR= ((Count(FSR) Mod 3)+1)
WHERE (((Source.Name)=[Forms]![Event Selection For mailing labels]![Event Name]));

I'm still getting zeroes in the FSR field. Any direction you can give me is greatly appreciated.

Jcarmody

 

[PHV]

Something like this ?
UPDATE Source S INNER JOIN [Referral Table] R ON S.[Source ID] = R.Source
SET R.[Initial Letter Date] = Date(), R.[Mail Sent] = 1, R.FSR = 1+Int(Rnd(R.Source)*3)
WHERE S.Name = [Forms]![Event Selection For mailing labels]![Event Name];

If Source is not defined as numeric in [Referral Table] then replace Rnd(R.Source) by Rnd([any numeric field in S or R])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jcarmody]

PHV,

It ALMOST does the trick. I tried updating 5 records in my update query and only got values of 1 and 3 (no 2's).

Jcarmody

 

[PHV]

Choose a numeric field (for the Rnd function) that is different for almost all records (an autonumber is ideal).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jcarmody]

PHV -

Thanks, That seems to do it for me.

Jcarmody