Selecting records using a "cab rank" system

[Tony Williams]

I have a table that holds peoples names (amongst other things) I want to
allocate jobs to them with a system similar to a cab rank system ie the first
job is allocated to the first name (I'll sort the names with a query into
surname order), the second job to the next name and so on. When the list has
been exhausted I'll start again at the first name.

I suspect that this needs an extensive bit of VBA coding and if it does I
may struggle as I'm not a VBA expert.

Can anyone suggest anyway I can do this?
Many thanks
Tony

 

[John Spencer]

Need more information.

One possibility is to create a rank order of the persons and another
rank order of the jobs.

First Query as qPeople
SELECT Person,
(Select Count(*)
FROM Personnel as Temp
WHERE Temp.Person < Personnel.Person) as TheRank
FROM Person

If that works you should get a list of persons with a one-up number from
1 to N with no duplicates.

Second query as qJobs
SELECT JobID
, (SELECT Count(JobID)
FROM Jobs as Temp
WHERE Temp.JobID < Jobs.JobID) as JobRank
FROM Jobs

Now you can combine those two queries into a third query

SELECT qPeople.Person, qJobs.JobID
FROM qPeople, qJobs
WHERE qPeople.TheRank = qJobs Mod DCount("*","Personnel)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Tony Williams]

Thanks John I'll have to play around with that for a day or two and see how
that fits in with my database. You said you need more information, what other
information would have helped?
Thanks again
Tony

 

[John Spencer]

Well, are the person names unique?
What order are you using to assign jobs to persons?
Are the jobs uniquely identifiable?

I assumed that the names were unique and you were assigning to the
persons in alpha order.

I assumed that the jobs were uniquely identifiable and that you were
assigning the jobs by the unique identifier.

I also assumed that only one field was used in uniquely identifying the
person and the job.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Tony Williams]

Thanks John. Yes I'm sorry should have given all that detail but you are
quite right in all your assumptions.
Thanks again
Tony

 

[a a r o n _ k e m p f]

of course.. the problem with correlated subqueries... is that
eventually Jet throws a tissy and says 'the parameter is incorrect' or
'the expression is too complex'.

gag-- what a joke!

move to SQL Server if you need to use correlated subqueries

-Aaron