Splitting query into equal recordsets

Hi Guys,

Hopefully you will understand my problem.

I have a spreadsheet that i import into a table which contains job
data. Using a number of queries i can filter the data as requested.
What i now want to do is have the database split the data into
seperate recordsets ready to export back to a spreadsheet.

Example:
Spreadsheet contains 26 jobs (6 require a two person team - 15 only
require a one person team)
We have 2 (two person teams)
We have 4 (one person teams)

I need the database to be able to split the recordset up equally so
that each team (1 or 2) receive 6 jobs each

Eg
each two man team receives 3 two man jobs and 3 one man jobs = 6 jobs
total each
each one man team only receives 6 one man jobs (never any two man
jobs) = 6 jobs total each

aplogies if you dont fully follow my requirements, i am an
intermediate access user and with direction should be able to follow
any suggestions.

Thanks in advance.

Re: Splitting query into equal recordsets

On 25 Jan, 12:21, LL-JK wrote:
> Hi Guys,
>
> Hopefully you will understand my problem.
>
> I have a spreadsheet that i import into a table which contains job
> data. Using a number of queries i can filter the data as requested.
> What i now want to do is have the database split the data into
> seperate recordsets ready to export back to a spreadsheet.
>
> Example:
> Spreadsheet contains 36 jobs (6 require a two person team - 30 only
> require a one person team)
> We have 2 (two person teams)
> We have 4 (one person teams)
>
> I need the database to be able to split the recordset up equally so
> that each team (1 or 2) receive 6 jobs each
>
> Eg
> each two man team receives 3 two man jobs and 3 one man jobs = 6 jobs
> total each
> each one man team only receives 6 one man jobs (never any two man
> jobs) = 6 jobs total each
>
> aplogies if you dont fully follow my requirements, i am an
> intermediate access user and with direction should be able to follow
> any suggestions.
>
> Thanks in advance.

changed the number of jobs above as they were incorrect sorry.

Re: Splitting query into equal recordsets

On Fri, 25 Jan 2008 04:29:26 -0800 (PST), LL-JK
wrote:

Perhaps you can assign jobs round-robin. In your import table you have
an extra column TeamID. Then you loop over the records in that table
using a recordset, and assign a TeamID for each row. With a bit of
code you can round-robin over the 2PersonTeamIDs and the
1PersonTeamIDs as well. In the end you have evenly distributed the
TeamIDs to the jobs. Then you run some queries to export this data
back out.

-Tom.


>On 25 Jan, 12:21, LL-JK wrote:
>> Hi Guys,
>>
>> Hopefully you will understand my problem.
>>
>> I have a spreadsheet that i import into a table which contains job
>> data. Using a number of queries i can filter the data as requested.
>> What i now want to do is have the database split the data into
>> seperate recordsets ready to export back to a spreadsheet.
>>
>> Example:
>> Spreadsheet contains 36 jobs (6 require a two person team - 30 only
>> require a one person team)
>> We have 2 (two person teams)
>> We have 4 (one person teams)
>>
>> I need the database to be able to split the recordset up equally so
>> that each team (1 or 2) receive 6 jobs each
>>
>> Eg
>> each two man team receives 3 two man jobs and 3 one man jobs = 6 jobs
>> total each
>> each one man team only receives 6 one man jobs (never any two man
>> jobs) = 6 jobs total each
>>
>> aplogies if you dont fully follow my requirements, i am an
>> intermediate access user and with direction should be able to follow
>> any suggestions.
>>
>> Thanks in advance.
>
>changed the number of jobs above as they were incorrect sorry.

Re: Splitting query into equal recordsets

On Jan 25, 2:23=A0pm, Tom van Stiphout wrote:
> On Fri, 25 Jan 2008 04:29:26 -0800 (PST), LL-JK
>
> wrote:
>
> Perhaps you can assign jobs round-robin. In your import table you have
> an extra column TeamID. Then you loop over the records in that table
> using a recordset, and assign a TeamID for each row. With a bit of
> code you can round-robin over the 2PersonTeamIDs and the
> 1PersonTeamIDs as well. In the end you have evenly distributed the
> TeamIDs to the jobs. Then you run some queries to export this data
> back out.
>
> -Tom.
>
>
>
> >On 25 Jan, 12:21, LL-JK wrote:
> >> Hi Guys,
>
> >> Hopefully you will understand my problem.
>
> >> I have a spreadsheet that i import into a table which contains job
> >> data. Using a number of queries i can filter the data as requested.
> >> What i now want to do is have the database split the data into
> >> seperate recordsets ready to export back to a spreadsheet.
>
> >> Example:
> >> Spreadsheet contains 36 jobs (6 require a two person team - 30 only
> >> require a one person team)
> >> We have 2 (two person teams)
> >> We have 4 (one person teams)
>
> >> I need the database to be able to split the recordset up equally so
> >> that each team (1 or 2) receive 6 jobs each
>
> >> Eg
> >> each two man team receives 3 two man jobs and 3 one man jobs =3D 6 jobs=

> >> total each
> >> each one man team only receives 6 one man jobs (never any two man
> >> jobs) =3D 6 jobs total each
>
> >> aplogies if you dont fully follow my requirements, i am an
> >> intermediate access user and with direction should be able to follow
> >> any suggestions.
>
> >> Thanks in advance.
>
> >changed the number of jobs above as they were incorrect sorry.- Hide quot=
ed text -
>
> - Show quoted text -

Thanks for the response ill have a play with your suggestion.