the relation between the order and the production (business logic)

[mseo]

I have a database for orders and production
I will explain everything in detail:
The database contains 4 tables
1- tbl_order (OrderID PK , OrderNo, Employee_ID, Customer_ID, OrderDate)
2- tbl_Orderdetails( OrderDetailID, OrderID FK, ProductID, Quantity , Date Required, UnitPrice)
3- tbl_Production (BatchNo, Employee_ID, DateEntered)
4- tbl_ProductionDetail ( BatchNO, Date, Machine_ID, Employee_ID, Product_ID, Quantity, Defective, OvertimeQuantity, OvertimeDefective)

in the second table (tbl_Orderdetails) I made the field orderdetailID as PK in order to allow duplicating the product
because the order will be like that:
Product Product quantity daterequired
So I can have the same product more than one time but the daterequired will be different.
Assume that I have this order

Product    Quantity    Date Required
T.V    10    1/6/2010
T.V            3             1/8/2010
VCR    17    1/15/2010
Camera    4    1/14/2010

the order will be entered into the database like that above-shown
and the production department will receive the same order to carry it out
and the will enter units produced into the database like that:

Product    Quantity    Date
T.V    2    1/7/2010
VCR    6    1/2/2010
Camera    1    1/10/2010
TV    2    1/4/2010
Camera    2    1/5/2010

so I need to develop a form to calculate how many units need to be produced for which date ( the daterequired that mentioned in the order):

in other words i want to compare between the production and orders depending on the order date required of every product, it's now 8 days looking for any piece of information to make this query

[NeoPa]

So, if I understand you correctly, you have a table that was called [tbl_Order Details], but is now [tbl_OrderDetails], that has details of all ordered items ([ProductID]) and when they are required by ([Date Required]), and you also have a table that was called [tbl_Production detail], but is now [tbl_ProductionDetail], that has details of all items ([Product_ID]) produced and when they were (Was [Date Produced], but is now apparently just [Date]).

There appears to be no deleting of any of these records from the table triggered by their relevance expiring (such as when orders are completed etc), so all orders from the beginning of time are available.

You want to be able to design a query that shows totals of all the products where there are quantities that have been ordered but not yet produced, but these totals should be shown grouped by [Date Required].

Is that about right, or have I misunderstood somewhere?

[mseo]

hi,
You understood me very clearly, but you drew my attention to very important points such as (the deleting of the records from the table triggered by their relevance expireing such as when orders are completed etc)
but how can I do so? because it is the first time I know that I can do something like that
If you have any useful advices that would be nice from you, but without deleting any tables because I designed a lot of forms depending on the tables of orderdetail and productiondetail
thank you very much

[NeoPa]

As a general rule, I would not recommend deleting these records. I would simply create a flag instead, which would indicate which records are done with. That way the historical data and the current data can be maintained together (No extra complication of designing new tables into the structure) with a simple value to indicate which is which.

If you consider this necessary, then you can copy expired records to a separate table and delete the originals. Sometimes (quite rare but does happen), the number of records to process makes this approach more sensible.

That said, you didn't really answer my question from the previous post so it's hard for me to give further help at this time.

[mseo]

hi,
I told you that you understood what I need to do and this is the answer of your last question, have you asked me any other questions?
ok, I don't like to copy the records to another tables or deleting them i just need this query to use it within a form this form will be used for showing the status of the orders ( the quantities have not undelivered yet) from all products for each customer.
please help me to do this because this query that I will use within a form for following-up the orders
the names of the fields I just write it for making it simple for everyone to know what I mean and in my post I told you it isn't about the code it's all about business logic so the names of tables and fields shouldn't be as I did in my design
thank you for any help you may provide me

[mseo]

hi,
attached to this post the design of the database
please help me solving this dilemma
thank you for any solution you may provide me

[NeoPa]

MSEO,

I do apologise for the delay in replying to this thread. I know I wrote the above, but I can't see what I meant without much reconsideration. I do accept that I will need to look again carefully at this. You deserve that. However, for various reasons I'm finding time hard to devote at the moment. I was hoping to have a look over Easter, but barely got on my PC at all during that weekend.

I will try to have a look again at it tonight for you. Maybe I can at least make sense of what I wrote.

[mseo]

Thank you very much for your interest, Neopa
I hope that you may have spent a happy Easter Holiday
I really appreciate your reply
and for saving your valuable time
the attached file is topher23's answer for my thread (Query between two tables with no relationship)
but after making a little change in the report to get the remainder in stead of getting the on-hand After Fulfillment and i want to make the line number 3 of the product milk to be 300 rather than 446 because it would duplicate the values of the above quantities and filter the report to contain the remainder only I tried to filter the report but the running sum of order quantities will start from the first record according to the query criteria
thank you again

[NeoPa]

It's nearly 02:00 in the morning here & I find I haven't yet had a chance to look at this. I have not forgotten. Just very busy.

[NeoPa]

It's a bit hard to get back into this now, especially as you have proceeded with the conversation somewhat in my absence (my fault mainly of course). What I will try to do is to explain my thinking about how you would use the original table structure, all pretty well linked together, to get access to all the information relative to each product.

The Orders and the Productions then all appear together as grouped data. The sum of one can be subtracted from the sum of the other and the result is what you need. Your date filtering would be handled in the WHERE clause of course (not HAVING, as is easy to be left with if working with Access Query Design View).

Does that all make sense?

[mseo]

thank you Mr. NeoPa
I appreciate your reply
that make sense for me but the report has all the product even if the remainder equal zero and I want to view the products that have a remainder value greater than zero
the second problem for me the second line in the report of the product milk has a value 146 so the third line should be 300 not 446
please help me to do this
it's long time looking for any solution for this
I really appreciate your help

[NeoPa]

QUOTE=mseo;3561536
that make sense for me but the report has all the product even if the remainder equal zero and I want to view the products that have a remainder value greater than zero
/QUOTE
If you saw what I said in my last post about using WHERE instead of HAVING, then you need to wipe your memory. Ignore that suggestion as it's rubbish :(

Aggregate functions (Sum(), Max(), Avg(), etc) can never appear in the WHERE clause. They must always be filtered in the HAVING clause.

QUOTE=mseo;3561536
the second problem for me the second line in the report of the product milk has a value 146 so the third line should be 300 not 446
/QUOTE
Remember, I have no information with which to understand what you're saying here. I would need to see your SQL and some data to be able to follow what you're saying. What seems particularly strange at the moment is that you're talking of multiple records for the same item when I'm thinking they're all grouped together into one. Makes no sense at all for now.