Results 1 to 3 of 3
  1. #1
    Mbroeth is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    4

    Stuck on complex Quote/ Order Database

    I am just starting with access and have unfortunately chosen a large project as my first. I work for a small manufacturer of industrial doors and am trying to make a database for creating and keeping track of quotes and orders. I have gone through a Udemy course and looked at examples such as the Tradewinds database but they all cover orders with products of a fixed size/ price. We build Custom size doors in 12 different models ranging anywhere from an 8' X 8' to 30' X 30', every 1' change in height or width is a different price. That I pretty much have figured out with a 2400 record table price list with every combination of model and size. But on my line item subform, doors will need an input of [quantity, model, high, width], While add ons may need an input of width or height or square ft as a quantity or a percentage of the price of the door. There are just so many variables I can't figure out a form that will get the information in the right place for each product, then have it print in a report in some understandable way. I know this is probably very confusing but I don't know a better way to explain it. Anything will help. Thanks,

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some insight from this post.
    It seems that your product table with all of the dimensions/styles etc will be critical.

    Google may be your best approach to find some relevant data structures.
    To add to the complexity. I'm sure Prices can and will change with time, and that you may also have loyalty programs or offer discounts based on quantity of items or cost of Order.

    Certainly not trivial, and not a typical "first time database".

    You may get some more info o design and concepts from info here.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you have 2400 possible door combinations and have all the variables in those records, then why would your subform need inputs for those variables as opposed to a search form that locates the door ID based on the required inputs? I presume the combinations table includes a pricing field, so a quote table has the quoteID, comboId and the price, reflecting any discounts. Your invoice should be able to grab all the options from the combos table fields. Not sure where the individual options prices might be coming from, or even if you need them. If I'm way off base, it should illustrate that to fully understand the design goal involves far more details than what you've revealed thus far.

    You are in for a rough ride by taking on something like this for your first project. The do's and dont's about naming things, reserved words, autonumbers, normalization, lookup fields, multi value fields etc. etc. are daunting enough for a newcomer and a small project. If I had one statement about design, it would be to learn the basics, then get some flip chart sized paper and design the relationships on that - with pencil.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2016, 05:02 PM
  2. Quote Database
    By jimbob in forum Database Design
    Replies: 1
    Last Post: 03-17-2014, 05:14 PM
  3. Replies: 7
    Last Post: 06-04-2013, 11:14 AM
  4. Very complex database
    By adryan_g78 in forum Database Design
    Replies: 20
    Last Post: 12-16-2011, 10:30 AM
  5. Replies: 1
    Last Post: 03-06-2011, 06:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums