Dear All,

I need to manage partial/scheduled shipments of line items.
Customer orders 100 pcs to be delivered in January then we only can deliver 60 pcs in February then 40 pcs in May.

I have a tabular form with PlannedPaymentDate=January, InvoiceNumber, Qty=100, UnitPrice,..... fields.
Each line has a button.
The button should open the similar tabular form to be able to enter:
First line: PlannedPaymentDate=February, InvoiceNumber, Qty=60, UnitPrice,.....
Second line:PlannedPaymentDate=May, InvoiceNumber, Qty=40, UnitPrice,.....

We could think that then the 40pcs could be delivered with 20pcs in June and 20pcs in September... so we have a button on each line of this new tabular form....

On each form, we could have an header with the datas of the full planned deliivered quantities: PlannedPaymentDate=January, InvoiceNumber, Qty=100, UnitPrice,

I wonder how to do it.

I attached the screen capture of the main form.

Thanks a lot,

Philippe

Dear All,

May be I did not explain well and that is the reason why I have no answer (or maybe it is the week-end).

My idea is the same as Russian dolls: Opening the same empty tabular form from a button in the tabular form. A button of FrmX opens new empty FrmX with a button which can open another new empty FrmX....

Till now, I have several tables/queries for each level but may be they is a nicest way to put everything is the same table (which will make easier calculations)

Another possible solution is similar as the + button (For exemple to see files belonging to the main file in Microsoft Explorer), which allows to displays sublines... but I really do not know.

Again thanks a lot for your help and any idea is really welcome (Queries Actions?, ....).

Philippe

I understand your example of the Russian dolls. For this example lets assume a Customer and PhoneNumbers. Let's assume that each customer can have at least 3 phone numbers. What we do is create a table of Customers and a related table of PhoneNumbers and join them as below:

tblCustomers
CustomerID (PK and auto)
LastName
FirstName

tblPhoneNumbers
PhoneNumbersID (PK and auto)
CustomerID (FK)
PhoneNumber

As you can see you can add many phone numbers for one customer if your forms are set up as form/subform. The relationship above is a One to Many (One Customer, Many PhoneNumbers)

Now if in your database there will NEVER be more than ONE phone per customer then you do NOT need the second table, but can put the phone number in the table with the Customer data . The exception to this is that if the main table will have a lot of FIELDS then it may be better to make a table for phone numbers and relate it to the Customer table in a One to One relatinship (One Customer to One PhoneNumber).

I do not know which senario you have so from the above you can determine whether you need a One to Many or One to One (these are rare) relationship and you can set up your tables accordingly.

hth,
Jack

First thanks a lot Jack to answer on Sundays.

I have already 1:N relationship with a table "sales order" (equivalent to tblCustomers) and another one "products" (equivalent to tblPhoneNumbers). If you open my attachment, you will see the form with the subform.

Lets have a table with:

tblProducts
LineItemID (PK and auto)
PartNumber
PaymentDate
Quantity

and I take my example of:

Customer orders 100 pcs to be delivered in January then we only can deliver 60 pcs in February + 40 pcs in May.

Originally we have PaymentDate:January and Quantity:100.
I can now delete this and put instead two lines with PaymentDate:February/Quantity:60 and PaymentDate:May /Quantity:40 but I lost my first information.
Another solution is to use the PK with 1:N relationship to another table similar to the first tblProducts which will have the 2 lines for 60pcs and 40cs. Then come the Russian dolls, if the 40pcs can be delivered in 20+20...

At the end you divide similar information in several tables which does not help calculations.

Another elegant solution could be this + button.
We keep the line with PaymentDate:January/Quantity:100 and pushing the +buttons, lines are inserted to be able to add the 60 pcs and 40 pcs.

Again not sure if I explain well.

Philippe

I think I would use a single related table. If you enter 100 items to be shipped but you change that and now you are going to make two shipments if 50 and 50. In the next record enter a minus 100 and then enter two records of 50 units each. I would assume you are interested in the total and when they were shipped to this setup should give you the information you desire. You could have a Ordered field and a Shipped field in which you put in the amount ordered (by order number) and then when you make a shipment you enter that number. By adding the quantity shipped and subtracting it from the amount ordered you know how many are on backorder. I think that either approach will work for you. Take a look at the Northwind database that comes with Access as I think you will be able to glean some information there.

hth,
Jack

Thanks Jack.
I have a boolean field called "scheduled" that helps me to select the lines to be calculated.
For intance the line with 100 will be scheduled:True and will not be taken account then I can add two other lines with 60 and 40 (in the same related table as recommanded).

I am going to change my LineItemID - which was PK - to Numeric. Like these 100pcs will be bound to the 60pcs and 40pcs as they will have the same LineItemID in the same table .
I will still have the issue of the 40 pcs to be delivered in 20+20 but I should manage to find a way to solve it with specifyng a format for the LineItemID.

My database is mostly done for forecast (Qty related to Dates) and reminders to be sent (lines well defined in an order).

If you have any comment, do not hesitate.
Thanks,
Philippe

I would suggest that you keep your autonumber primary key for each record. You can still have a LineItemNumber, but it is best to have a unique primary key for each record.

Other than that I think you are good to go if the system is working for you.

Good luck.

Jack

Thanks a lot Jack. Surely come later on other questions....

You are welcome and continued success with your project...

Jack