Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Quotation database - how to get started

    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

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    "Start it" with the purchase of a good introductory Access book.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,557
    You read my mind, Teddy!
    Hope this helps!

    The Devil's in the Details!!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    It seems very complicated.
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    chopped colinsv

    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

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    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.
    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.

    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.
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    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.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    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

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    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!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Quote Originally Posted by marcusmacman View Post
    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... what I don't want is previous quotes changing.

    ... copy them into one record in the main Quotes table to preserve the data
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •