Hi there,

I'm trying to create a quotation database for sales orders.

I don't know how to start it.

I have an example from the past where the form was unbound. It had a combo box which displayed a list of jobs in a subform. New records where created using a button invoking a macro to add one to the last job number and requery. It seems very complicated.

Is there an simpler was of doing it?

I would like a combo box driving the form. My boss woul like the combo box display quotes in date order.

I've spent all day on it and my mind is going to explode

cheers
Marcusmacman

"Start it" with the purchase of a good introductory Access book.

You read my mind, Teddy!

Yes. This is why companies that design bespoke databases for other companies can charge the money that they do!

To get started, you need to sketch out the tables that you think you'll need, and plan how the records in them will fit together. Get that idea down, and then you can start building the forms around them.

well i sat through 8 hours of an access database course provided by the Virtual Training Company..I stayed awake for most of it!!

My biggest concern I have is preserving data. It's all very well creating a referral database but if I say have a table relationship between the orders table and customers. (one - to many as 1 customer can have many orders). If in the future I change the customers fax number for one order this will change all the other records which refered to that customer in the past.

Ok that's not a big problem but I am looking at managing our Condition of Sales. These may change from time to time and what I don't want is previous quotes changing.

I figure I'll have to copy all the data I get from other table relationships and copy it into the main table to preserve it's integrity. least that way I can look up customers / latest Terms & Conditions but then copy them into one record in the main Quotes table to preserve the data

If anyone has any other ideas It would be very welcomed.

Has anyone got the name of 'that' Good book?
many thanks
marcusmacman

No, this is the point of a relational database. If you maintain the customer's detail in the customer table, and display them with the orders, the orders will always display the current details for the customer. No extra work required from you or your colleagues.

That's a different problem. As an initial thought, I'd look at having a table to hold the T&Cs, with fields to hold the effectivity dates and a flag to indicate if it's the current set to use.. Then you set the orders to pick up the current one, so that the order history will show whichever set of T&Cs applied to it.

thanks weejas,

I think your idea of a table for holding the T&C's is good, however is there a way of write protecting the current T&C's so no one can change a line or two once it has been selected, thus changing other records with reference to the current T&C's.

thanks again.

Of course. It gets complicated, though - you'll need to have a split between the front and back ends, and set up a form to maintain the T&Cs that only allows them to be created.

splitting the front and back sounds painful? So do you mean the user can call up the T&C's but is not allowed to make changes unless you enter another form (Password protected perhaps) that allows modifications to the T&C's together with a new revision number / date. If that's the case it sounds good. I just need to think how best to do it, unless you have any idea's?
Thanks

Splitting the front and back ends is standard practice in a lot of places, and makes some things easier.

I have several ideas, but sadly no time to do anything with them!

That's how I'd do it. You might benefit from grapping an OS eCommerce platform and having a look at their database structure. It's very common to attach a set of delivery specs to the order which were copied from the customer, no biggie.