I have a table that is called tblSalesQueueEntry where there is a field called SpecialistAssignment. I would like to auto assign this field based on three possible names. Is it possible to utilize the Default Value of the field properties in this field to assign each record in my parent table, tblLeadManager, based on my parent to child linked LEADID field. tblLeadManager is the parent and tbleSalesQueueEntry is the child. For example, record 1,4,7,10,13, and etc. would be assigned to Specialist1. Record 2,5,8,11,14 and etc. would be assigned to Specialist 2. A pattern based on 3,6,9,12,15 and etc. would apply to Specialist 3.

I have not attempted to auto number in this fashion previously. This is a brand new database with no field entries as yet. Please assist.... :confused:

Since 3 is a prime number you can do it without any headaches....

However what if we go to 4 or 6 (cuase 5 is prime as well) then you will run into trouble...

I think the only way woud be to code a select SpecialistID, Count(*) from tables order by count(*) asc

This would put the specilist with the fewest (is that proper english?) assignments on top, however this may run slower and slower as the DB grows and grows. But it will work and allow you to even out the number of leads assigned to a specialist.

Greetz

Is there another possible solution? My issue with implementing this fix is the access speed. By the end of a fiscal year the number of leads could be well over 20,000. Please let me know your thoughts and thanks for the current solution. :(

That would depend, Because you dont need to reassign all 20K at one time. You only need to assign 1 or a couple of leads at a time. Also how up-to-date does it need to be. Can you say run the assignments once or twice a day for a couple of leads or does it need to be real time?

Does this help at all?

Regardz

P.S. No need to PM me for further info. I make it a point to revisit the threads i put a message in. :D

The records will only be updated twice weekly, as the Dbase needs are described by the users. Would you mind entering a sample of the select statement just to assist with me getting the syntax correct the first time? As a relative SQL novice this is what I entered:

SELECT SalesSpecialist.SalesSpecialistName, Count(*)
FROM SalesSpecialist
ORDER BY Count(*) asc;

It is not working. Thank you again. :D

I would start off getting your tables ready...

but... here goes

select * from tblSalesQueueEntry where SpecialistAssignment is null

that would select all non assigned leads.

Then in VBA use the earlier count query to count the leads per specialist and loop...

Greetz

Perfect. I was preparing the tables while awaiting your response. Works great. Thank you again.