Combining fields in report

[jwrnana]

I previously posted this in forms. It should have been reports.



I have an Order Entry database (from Microsoft Access) and I am using the
invoice provided. I have a question as follows:

1. Order contains i.e.- 10 line items
2. Order is split into 2 different dealer deliveries
3. When order is entered, I CAN enter; i.e. line items 1, 2, 4, 6, 7, 8,
and 9.
4. I can then select a new order page and enter line items 3, 5, and 10.
Each of these entries has a different Order ID. The customer remains
the same.
5. I can print an invoice for each of these orders.

How do I get the invoice to print ALL of the line items on one invoice?


Second question is on this same form, in the field Order Date, the date
automatically pulls today's date. How do I change that to allow input of
the actual order date?

Windows XP Pro, and Access 2003

Thank you.

 

[Allen Browne]

The tables you have are inadequate for what you are trying to do. Getting
the tables right will be the best way to answer your question.

You need to work through the issue of how orders relate to deliveries and
invoices. It seems that one order can have multiple deliveries. Could there
ever be an order where *part* of a line is delivered at one time, and
another part in another deliviery? For example, if a customer places an
order for 99 widgets, and you have 9 in stock: would you deliver the 9 now,
and place the 90 on back order to be delivered in future deliveries?

If so, you need to have at least these table:

Order table (one record for each order):
OrderID primary key
OrderDate when ordered
CustomerID relates to your Customer table

Order Detail table (one record for each line item):
OrderDetailID primary key
OrderID which order this line belongs to
Quantity how many of this product is ordered.
ProductID relates to your Product table.
UnitPrice charge each

Delivery table (one record for each delivery):
DeliveryID primary key
DeliveryDate when delivered

DeliveryDetail table (one record for each product delivered):
DeliveryDetailID primary key
DeliveryID which delivery this line item belongs to.
OrderDetailID which order line item entry this record relates to
Quantity quantity of product actually delivered.

Now when the sum of Quantity in DeliveryDetail for an OrderDetailID matches
the Quantity in the OrderDetail table for that entry, the delivery of that
line of the order is complete. And when that is true for all line items on
the order, the delivery of the order is complete.

If you do not have to cope with partial delivery of line items, you need
basically the same structure, without the Quantity field in the
DeliveryDetail table. Then you can assume that once an OrderDetailID turns
up in DeliveryDetail, that row is delivered.

Now you can easily do things like:
- print the whole order;
- print separate delivery slips;
- choose whether to create an invoice for the whole order, or for the items
delivered.

 

[jwrnana]

Thank you for the information supplied. I will certainly look at my tables
before proceeding.

There was one question at the end of the post as follows:

Second question is on this same form, in the field Order Date, the
date
automatically pulls today's date. How do I change that to allow
input of
the actual order date?

Can you please provide assistance on this?
Thank you.
JR

 

[Allen Browne]

If you cannot change the date, it sounds like you set the text box's Control
Source property to =Date()?

If so change the Control Source to the field name, and put the =Date() into
the Default Value property instead.