Appending one of two letter to each record

[sallyc]

I have a file of data that I am using in a DM campaign.

I need to add a R or a B to each record to indicate which pack the person recieves - I simply want 50% to be R and 50% to be B.

I thought I could just create a table with an R and a B in it, put my data and that table in a query run it and it would just go r, b, r, b down the datafile, instead it doubles the size of teh file because i is adding an r and a b to each record, rather than an r or a b - is there a way to do this

Any help appreciated

S

 

[norman]

Hi

If you have a unique id number in the table, you could try this

Add a new field to the table say packtype

then using an update query with

Field packtype
table tblcustomer
update to IIf([customerid] Mod 2=0,"r","b")


This won't guarantee 100% 50 - 50 split if records have been deleted

HTH
Norman

 

[sallyc]

Hello there

That worked a treat!

I was just wondering if you could explain what the "Mod 2=0" bit is actually doing in the IIf statement?

Just so I understand what I'm doing!

Thanks

 

[norman]

Glad to hear it worked.

I'm not a mathematician so I can't explain fully the workings of the Mod function.

In simple terms what it is doing here is,

[customerid] mod 2 takes the customer id number for a record and divides it by 2

the mod function takes the remainder from the number returned.

Eg customer id 33 divide by 2 = 16.5 therefore the mod is 5 and the number must be odd
24 divide by 2 = 12.0 therefore mod is 0 and the number must be even

I hope this makes sense.

Regards

Norman

 

[mresann]

The Mod operator basically compares one number to another number, based on the amount of iterations in the first number. The easiest example would be an analog clock. If you were to count 12 hours from 12 o'clock, it would bring you right back to 12 o'clock (discounting am/pm or 24 hour time formats). If you added 24 hours, it would make two circuits and still give you a return of 12:00.

Now, if you add 18 hours, it would return 6:00. Follow so far?

So with the clock example, substitute 0 for 12.

12 Mod 12 = 0

18 Mod 12 = 6

Access Help give the answer as refering to a Remainder of a number divided, for instance, 18/12 = 1.5, so half of 12 = 6, so 18 Mod 12 = 6

Hope this helps a little, I'm sure a better way of explaining it can be done.

Now, you can change the Mod operator to whatever number you want.

 

[norman]

Mresann

Thanks for that, as I said I'm no mathematician.

So, what I should have said was

If the Mod operator is 2, then if the number(integer?) being tested is even, then 0 would be returned. If the number is odd then 1 would be returned .

My example worked only because I chose to use 0 in the IIf.

Right?

Regards

Norman

 

[mresann]

Yes, you are correct. Mod 2 is actually an excellent method of determining odd and even characters.

0 Mod 2 returns 0, or even
1 Mod 2 returns 1, or odd
2 Mod 2 returns 0
3 Mod 2 returns 1

...ad infinitum.