report filter

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I accidently posted this request yesterday to
microsoft.public.access.gettingstarted. Please forgive my
crossposting.

Hi

I'm using Access 2000 to create a report that displays order
information for data entry. If there are 90 orders today and I have 4
associates available for data entry, I print the report and sort it
manually so each associate gets 23 or 24 orders.

I'm trying to find a way to automate this filtering. I'm thinking of
adding another field to the underlying table and update that field
with a 1, 2, 3 or 4. Then use that value to filter the report. I don't
know how to populate that field using a query.

The available associate quantity is not the same each day. Some days
there are two associates available. I could have up to 5 associates
available.

Any suggestions?

Dan
 
The next number would be given by an expression such as this:
(Nz(ELookup("AssignTo", "OrderTable", "[OrderDate] = " & Date, "[ID]
DESC"), -1) + 1) Mod 4

where the ELookup() is copied from:
http://members.iinet.net.au/~allenbrowne/ser-42.html

It means, lookup the AssignTo field, in the OrderTable, for todays date,
matching the highest autonumber. If there was none, use -1. Add 1 to the
result. Then give me the remainder when divided by 4.

This should cycle through the numbers 0 to 3.
 
Thanks for your feedback Allen. I did not use your Elookup function.
However, I used the mod function in an update query and that met my
needs. Again, Thank you. Dan

Allen Browne said:
The next number would be given by an expression such as this:
(Nz(ELookup("AssignTo", "OrderTable", "[OrderDate] = " & Date, "[ID]
DESC"), -1) + 1) Mod 4

where the ELookup() is copied from:
http://members.iinet.net.au/~allenbrowne/ser-42.html

It means, lookup the AssignTo field, in the OrderTable, for todays date,
matching the highest autonumber. If there was none, use -1. Add 1 to the
result. Then give me the remainder when divided by 4.

This should cycle through the numbers 0 to 3.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
I accidently posted this request yesterday to
microsoft.public.access.gettingstarted. Please forgive my
crossposting.

Hi

I'm using Access 2000 to create a report that displays order
information for data entry. If there are 90 orders today and I have 4
associates available for data entry, I print the report and sort it
manually so each associate gets 23 or 24 orders.

I'm trying to find a way to automate this filtering. I'm thinking of
adding another field to the underlying table and update that field
with a 1, 2, 3 or 4. Then use that value to filter the report. I don't
know how to populate that field using a query.

The available associate quantity is not the same each day. Some days
there are two associates available. I could have up to 5 associates
available.

Any suggestions?

Dan
Click to expand...
Click to expand...
 
Back
Top