Automated Order Status Field... (1 Viewer)

[Carpetwarehouse]

Sorry if this is in the wrong section...

Basically working on a Customer Order Database for my carpet shop.

I have a Customer, Job and Order table. The order table contains the line items for each job and each customer can have many jobs.

I have a field in the Order table showing the item order status being either "Instock", "To be Ordered" or "Ordered"

I also have a Job Status field in the Job Table

I would like that Field to be automated depending on the item order status for each item on the job.

Im not sure the best way about it...

At the moment the Job status is a manually selected drop down box of either; 'Ready To Fit', 'Awaiting Stock', 'Items To Order' or 'Job Complete'

For example if all of the items had a item order status of 'Instock' then the Job Status would display as 'Ready To Fit'

If any of the items were 'To Be Ordered' it would display as 'Awaiting Stock'

I dont know how i would solve the situation of some marked as say 'In Stock' some 'Ordered' and some 'To Be Ordered' ????

Also the 'Job Complete' would come from another field in the Job Table marked with a check box...

Hope that makes sense

 

[jdraw]

Please post a jpg of your tables and relationships. It is important to get your database designed to match your needs before you get too involved with Access Forms/Reports etc.

 

[Carpetwarehouse]

Please see attached image of tables and relationships

 

[jdraw]

Are you currently using this database, or is this part of the design/development?
Here is a link to a few videos that may be useful.

 

[Carpetwarehouse]

I was using it but went back to paper while i try and make improvements, thanks for the link i will take a look...

 

[Carpetwarehouse]

Thanks for the links but they dont really answer my question they just explain the structure which i feel i know enough about...