Quote => Order => Invoice

[Frederique]

Goodafternoon,

I have attached a part of my data model.

Lets consider for a moment that one designs a project and sends out a quote.
More often than not there will be changes to be done together with new quotes. However there will be some point where you'll get the order based on a certain quote.

So is there a way where you can fetch a copy of the records in the table quote+quotelines to import into an order/orderlinesform?

The customer, employee, projectreference, products ordered, prices, and so on will all be the same.

There might be occasions where you just receive an order without having done any work so i guess the import should be optional. Then you could do the same to produce and track the invoices.

Is this possible? It almost feels like a copy/paste routine based on a commandbutton to get the quotelines you want.

So before i continue this road i'd request your advise/input if this is feasable or if i should forget it all together.

thanks in advance

 

[plog]

I think its best to think of quotes and orders as different statuses, not entirely different objects. A record should simply have its status changed to 'order' from 'quote', you shouldn't move all the data to new tables.

A structure that conforms to that thinking would help eliminate the glaring error I see in your structure---circular paths. You should only be able to draw one path between objects. I see 2 ways to get from tbl_Products to tbl_Employees:

tbl_Products: tbl_SalesOrderLines: tbl_SalesOrders: tbl_Employees
tbl_Products: tbl_PurcharseOrderLines: tbl_PurchaseOrders: tbl_Employees

That's wrong, there should only be 1 path. I think keeping Orders/quotes in the same table would clear that up.

I do see other errors, but think you should get the Orders/Quotes issued squared away before focusing on them:

1. Circular path between tbl_Projects and tbl_PurchaseOrders.
2. Tables with only 1 real field. tbl_LU_CoatingTypes, tbl_LU_WireDiameters, etc.
3. Redundant tables. Data from tbl_Companies & tbl_Supliers should be in same table, as should tbl_Customers and tbl_Suppliercontacts.
4. Redundant data. tbl_PurchaseOrders many of the same fields as tbl_PurchaseOrderLines (ConversionRate, fkCurrencyId).

 

[Frederique]

Thanks for your input plog.

2.
The tables with only one field i've created to grind through a backlog of orders from 2013 till today. At first i used a dropdown box inside the table but after studying up and learning how access works this was a very bad idea. So i deleted all the relationships and got rid of those comboboxes. I don't like storing them embedded in each table as options since theres no easy way of adding/changing them (ok apart from the tiny button on the bottom on the list). I add to these tables as i go along and some contain between 5-20 options. I know they are redundant in some way but i've setup input forms to add to them. Thats what the end user should do i think.

3.
As for the relationship between companies and customers that has been done deliberate. I have a multiple companies where there are upto 8 contacts who each place their own orders. Therefore i've made a seperate table to track all of them. The user must add a non existing company to the table through a form. The dropdown on the customertable to identify the company is based on a query which keeps everything up to date. (Otherwise you risk getting multiple companynames for 1 company= Cleaning Ltd., Cleaning, Ltd. cleaning, ... .)
So yes, i went rogue with normalizing the forms .

For clarity i've setup seperate tables for clients and suppliers.
It was easier building the forms that way. I tried it at first but hit a wall with cascading comboboxes in a subform.

4.
The currency + conversion rate needs to be stored for historical tracking.
Its added once on each order and each orderline references to those fields.
So one only needs to punch in the number sonce.

You have sparked my interest with your remark that each object should only have 1 path. This does make sense as well as Combining purchase orders and salesorders together. Everything else is the same.
I only need to add a status (yes/no or optiongroup) to identify what type of order we are talking about. That would result in easier queries. I have been setting those up and it was rather difficult to get all the correct end results. I ended up combining the purchaseorderqueries and salesorderqueries together anyway. I'm going to give that a try and see where it brings me.

Thank god for backups

So yes it does make sense. I'll remember it for the future. If i have to drag a primary key more than once to make a 1-many relationship, somethings wrong.

thanks for your input

 

[jdraw]

Here is an older jpg re a similar issue. You may find it helpful.

 

[Frederique]

Hi Jdraw
thanks for this.
If i understand correctly the basic method is using the statement table as my masterform and statement lines as a subform. I presume the statement type will tell if its an order, quote or invoice. Items will be products and all their details.

Why are there 2 primary keys each time?
Is that one of those composite keys?
ID = 120
StatementType = Order
Is orderNr then Order120

I know i can build a query to get the order,quote and invoiced lists but how would the ordernrs and so on be generated?

thanks for your advise
much appreciated!

 

[jdraw]

The jpg is taken from a different forum, and I have seen it referenced from time to time. I think the reality is that if you can generalize an invoice/quote/order you can call them all Statement. This is called Generalization/Specialization or Supertype/subtype in data modelling.

See these for more info.

Generalization
SubClassing Rebecca
Daniel Soper sub/super in models

Also re composite unique index

Good luck.

 

[Frederique]

Thanks JDraw. Awesome to have the correct lingo for this particular type. I'll read up about the subject and have a go at consolidating the database and generalize it.

 

[Frederique]

Solved thanks to your input. I now have only one inputform which stores different steps of the quote/order/invoice process.
Fields like shipping address and shipmethod are greyed out during the quote phase, become available during orderphase and DateDelivered becomes available during invoicephase.

 

[jdraw]

It would be helpful to others to see your tables and relationships. Perhaps you could show us some jpgs of your physical implementation. There is not much info re: subtypes/supertypes from concepts to physical design.
Good luck with your project.