One order, multiple invoices

[jerem]

Hi,

Attached is a modified version of the Goods database from Ms.

For the moment, the tool is invoicing per order, but I would like to build the structure so that per item invoicing becomes possible. For example one order would have 3 invoices. The user should be able to select items to invoice and change quantities (from an invoicing form that would link to the order). The way I was contemplating that is with a new query or a buffer table but I am not yet sure on the way to build the whole thing.

June7 was suggesting:

Possibly control this in report set up as invoice document. Design report to force new page after each item. Good topic for a new thread when you have specific issues with report design.
Discretionary selection of items to invoice requires applying filter criteria. Maybe a Yes/No field in table. Invoice items checked yes?

Any advice? Other ideas?

Thanks!

[rpeare]

I can't read 2010 files but I'd think you'd want to mark the line items with the date they were invoiced and that would become your identifier if you, say, wanted to reprint an invoice for a customer or print an invoice for any 'new' line items (items that didn't have an invoice date or pick from a list of items that didn't have an invoice date). I don't think there would be a foolproof way to handle creating invoices that didn't bill the same customer multiple times for the same charge if someone accidentally reprinted an invoice.

[jerem]

Ok I will try. Thank you. Btw, you should be able to download the 30 day free trial version of Office 2010.

[jerem]

I am stuck here! When I create a new order, I have a Form linked to Order tables and a Subform linked to OrderDetails table that get created and data is sent to both tables accordingly. For invoicing, I would like exactly the same to happen: An Invoice table and an InvoiceDetail table with corresponding Form and Subform. The only difference is that I would like the form to be loaded with the corresponding data from Oder and Order details form but then update data into InvoiceDetail Form. And that is what I am not able to do. Use existing data in orders and then switch to invoice.
I'd even know how to do it in Excel VBA but in Access I am stuck. Thanks a lot for your help!

[rpeare]

You have to have some way to identify the order details you want printed on the invoice, like I said, a invoice date would probably be ideal because then when you printed an invoice you could mark the unmarked items with the current date (or a user input date) then whenever you wanted to reprint a specific set of data you could refer to that invoice date.

The other option is to create a list box where you pick out the items you want to appear on the invoice but that could get tedious if you're reprinting invoices that have 10 line items you'd have to re-pick all the items correctly to reprint the invoice.

[orange]

You may get some ideas from the data model here
http://www.databaseanswers.org/data_...ices/index.htm

A similar concept is shown here
http://www.databaseanswers.org/data_...ries/index.htm
where Orders-OrderItems are sent/could be sent in different Shipments.

[jerem]

Thanks guys!

Orange, thanks for the database map, I compared to my one and it is very similar, I just had one extra relationship between Orders and InvoiceDetails (and I don't remember why). The problem is I want to create an invoice from the ordering form. Therefore, when the InvoiceDetail form pops up it needs to take the OrderDetails data corresponding to the active order. Then, changes the user makes (changing quantities...) should affect the InvoiceDetails table so that one order can be invoiced many times. It is that switch between The form sourced in OrderDetails but controlling InvoiceDetails that I don't manage to create.

[orange]

Glad you have it under control. If I understand correctly the terminology would go something like this:
You have Orders. Each Order can consist of 1 or many OrderItems. When you are invoicing the Customer, you may have multiple Invoices against an OrderId, and Each Invoice is for 1 or more OrderItems related to that Order.

[jerem]

That is correct! How to make that work? I think where I don't get it is how to make the forms work with that.

[orange]

Once you have your tables designed and your relationships set up, you now go to the user interface. You need to determine what process(es)/interaction(s) the user must make with the database -- typically Create, Read, Update or Delete something. Don't make your forms do too much - focus on a specific interaction; determine what tables( or query) is involved and what must occur....

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

[jerem]

Thanks Orange. I'll go through them and get back to you.

[jerem]

Ok, those videos are all about normalization, not about forms. Maybe I have to put my question another way. How to link two forms in the structure we have described? I am working on Order Form and OrderDetail Subform... Then I click on Invoice and a new form appears with the order I was just working on. I choose the quantities I want to invoice and then it creates the data in Invoice and InvoiceDetails table. I have got all the forms, the only issue is the step between Order form and Invoice form... Do I need to use a OnLoad event?

[orange]

Yes I am aware the videos are all about database design and getting tables and relationships set up.

Here are some videos on Access that may be helpful to you.
https://www.youtube.com/playlist?lis...E1E1F86126CBA0

Also, there was a sample in the Northwind database that comes with earlier versions of Access --I can't see to find one in the install of A2010.

Think of the process involved:
-you have an Order for a Customer
-from that you can get the OrderItems
-you select/pick the OrderItems for which this Invoice will apply

I suggest you watch some form related videos (there are others on youtube) and do some experimenting.
I'm not sure why you mentioned the OnLoad event, but depending on what exactly you have in mind that might be appropriate.

I suggest you lay out the steps you logically want to do to create the Invoice for these OrderItems for this Order for this Customer, then look at/set up and test some options.


Followup:
There are some youtube videos by UncleGizmo that deal with some forms and techniques.

if you do a search in youtube for "uncleGizmo northwind orders" you will see several that deal with combo boxes. These should give you some ideas for your forms and how to set some things up.
Here is one of the videos.
https://www.youtube.com/watch?v=MgIq...=results_video

[qa12dx]

its pretty easy. forget relatioinships, they dont always work the way u want. what i'd is have order tbl, with a id(autonumber), other flds. invoice tbl with its own ID (invoice id)and a number fld, to store orderID and other flds etc.


now is order form have dt and id, etc. subfrm invoice, with all ur fields. textbox for tbl_invoice.orderID, set default value as mainfrm.id

when you open the form, new, have a macro setvalue,dt, date(). now the record is started in order tbl, and the subfrm same id in the said fld. and when u view, it is already linked by master and child so when you scroll rcords in main form, it will show only related invoice details

[jerem]

So you suggest that I have 2 tables instead of 4? I thought separating Order and OrderDetails, as well as Invoice and InvoiceDetails was the proper way to build this. Also, what does dt stand for?