Random Assignment In Access

Question

I have two tables and records in access

Table1

Acno  Branch 

123    001

124    002

125    001

Table2

OIC    Branch

A        001

B        001

C        002

I want to assign the Acno in table1 on random basis to the respective OIC per table2 where the branch code is the same. The Acno to be assigned say on equal basis. eg for Acno=123 and 125 will be assigned to OIC=A & B. 

Is it possible to do it in Access.

Thank you

Answer 1

> Let say if we ignore about the database structure

Those are fighting words. Most regulars here, and most database designers in general would focus FIRST on the database design. You may be tired to explain it to us, but please take a deep breath and humor us. Db design rules' violations can (will) come back to haunt you (or your successor). A correct database design makes life much easier.

Answer 2

Thanks Scott for your reply..

Let say if we ignore about the database structure, is there a function in access that i can trigger to assign/ tag randomly Acno with OIC.

Answer 3

OK, so the Acno is the tied to the Officer you are assigning them to? You reassign them at the beginning of each month? You need to keep a record of who was assigned for any month?

Why not just assign the ID of the Officer with an effective date? How will you manage so one Officer is assigned too many accounts?

Answer 4

The purpose is to assign each account to the Officer in charge on random basis.

Every month i will have new acno and i want to assign it automatically rather than on manual basis.

Answer 5

Can you explain your purpose here? I'm not clear why you can't just use an Autonumber datatype for Acno.