Reaching the quantity limit

[ChrisSedgwick]

Hi all,

I'm hoping you can help me with a small question I have...

I have a database that we use for our Fitters that install our Products. It works simply by attaching an Order No and Fitter next to a Product. At a later stage once the fitter has installed the Product, we input the quantity the fitter has installed.

So, if the Product is a Toilet Cubicle and there are 10 to install. The fitter may come back and tell us they've installed 10. Perfectly fine. If however they only fit 5 for whatever reason. I want a calculating to show that there are 5 remaining.

Then, if the same fitter goes back or maybe even a different fitter. They fit the remaining 5. The job is complete.

Here we're relying on the fitter telling us the quantity they have fitted. So if there are 5 remaining and they come back and tell us they have fit 6. I need some way for the system to tell us it over the original quantity of 10.

I only have the concept at this stage, so haven't tried anything with regards to creating queries. I'm hoping that someone could suggest a working structure?

I'd be grateful of any help.

Thanks,

 

[James Deckert]

Do you already have the table structure? That is the first step. If so can you provide table and field names?

 

[ChrisSedgwick]

Yes, apologies. I have the Tables and Forms. They are.

tblFitterOrder
FitterOrderID*
OrderNo
Fitter
InputDate
ProductID (FK)

tblInstallStages
InstallStageID*
StageInputDate
StageQuantity
Paid
DatePaid
FitterOrderID (FK)

The forms I have are one for entering the Fitter Order and select the Products that are being attached to that Order No. The products and job itself is taken from a different part of the database which was already previously set up to this. I'm now trying to implement an installations system so we can track what's being fitted.

Each Product will have an Original Qty on the Job. We're then inputting into the Fitter Order what the Qty to be fitted is. After the fitter has been to the job, he reports back to tell us how many he was able to fit. This being the Stage Qty.

I obviously need something that tells me what's remaining of the Original Qty as each Stage Qty is entered, so that it doesn't allow me to go over the Original Qty, meaning duplicate payments to fitters.

Hope I haven't gone too much into this that it sounds sketchy?

 

[James Deckert]

Where is the Original Qty which contains the total number to be done?
I'm confused about what the two tables are for. Correct me if I'm wrong.
tblFitterOrder contains the name of a fitter and the products he is to install. What is OrderNo?
tblInstallStages contains the quantity that has been completed (stageQuantity) and the payment info
In your original post you stated that the job may be done my more than one fitter, so is there a table above the tblFitterOrder with the OrderNo (as a FK) to link all the fitters together for the job?