Tricky one? How to print a report once a certain field reaches a number of entries?

[Garindan]

Hi all, I'm unsure how to go about this and wondered if anyone might have an idea to get me started?

I have a customer sales database, and the owner would like to send out deliveries once there are 150 items ordered. The company sells compost, mulch etc for gardening so they are heavy bags and can only fit so many on a van.

So its tblCustomerPurchasedItems which holds the quantity of items ordered (in the 'Quantity' field. They would like it to add up and print once its reached 150 different items (not orders), so this might be 50 orders with 3 items each, or one order with 150 items, or anything really as long as the items total 150.

Here are the tables which hold this info:-

tblCustomerPurchases:-
PurchaseNumber (AutoNum PK)
CustomerNumber (Long Int, links to CustomerNumber in tblCustomers)
DateOfPurchase
TotalCost

tblCustomerPurchasedItems:-
PurchasedItemID (AutoNum PK)
PurchaseNumber (Long Int, links to PurchaseNumber in tblCustomerPurchases)
ProductID
Quantity

So as you can see tblCustomerPurchases holds date, cost and customer number of the purchase, and then tblCustomerPurchasedItems holds which Products were in the purchase and at what Quantity.

I know I am obviously going to have to count on the quantity field, but I dont know how to do it??

Also, some other things I have thought of... if the last order placed which reaches 150 items actually takes it over 150, e.g. 153 etc, obviously I don't want to cut this order/delivery in half.

And... they would like the option somewhere in a form to change the quantity needed from 150 to another number.

Also, there is a table and field which holds a Salesperson name. They would like the option to alternatively count 150 orders (or whatever is chosen) per salesperson and then print!!!
I don't know if that's just getting too complicated!

So I'm stuck, any ideas how I can begin to go about this?

Many many many thanks for any help!!

 

[DCrake]

If this was my problem I would tackle it this way.

First I would open a form that contained a list of all orders awaiting delivery, I would sort them by date entered onto the system oldest first. This list would contain the item description, weight, quantity etc. I would then have a tick box in my subform to be able to multi select. As I click on the item a control on the subform would add up all the quantites chosen. Then when I get to the desired weight/quantity I would then flag all these for delivery and produce a picking list for the warehouse staff. Finally it would flag those records as being delivered and as such drop off the list.

 

[Garindan]

DCrake - I have decided to try your idea because it sounds good, but i'm having real trouble!

I have created a form (fsubUnprocessedPurchases) which shows data from qselCustomerDetails and tblCustomerPurchases (using qselUnprocessedPurchases). This shows the details of each 'unprocessed' purchase:-

CustomerNumber
Title
FirstName
Surname
Street
Area
Town
Postcode
etc

&

PurchaseNumber
PurchaseDate
TotalCost
DeliveryDate

So everythings simple so far. I wanted to show these orders as continuous forms, so I could add a check box to select which ones to 'process' and send for delivery as you suggested.

However, each order also needs to show the products and quantities in the order, from tblCustomerPurchasedItems.

tblCustomerPurchasedItems:-
PurchasedItemID (PK)
PurchaseNumber
ProductID
Quantity

I have created fsubUnprocessedPurchasedItems to show these, and I wanted to then add it to fsubUnprocessedPurchases as a subform (linked on PurchaseNumber field) to show the items in the order.

The problems are A) I can't have a subform in continuous forms, and B) will I still be able to put a count on the quantity field from several records in this way?

I'm really stuck now and i'm running out of time

Many thanks for any help!!