Selecting records using a "cab rank" system

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
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
'====================================================
 
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
 
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
'====================================================
 
Thanks John. Yes I'm sorry should have given all that detail but you are
quite right in all your assumptions.
Thanks again
Tony
 
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
 
Back
Top