Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    1

    Basic Quoting System

    Hi guys. VERY new to access, so please excuse my ignorance

    OK: here is my (proposed) database:

    I want to create a quoting system (as the subject suggests) and im not really sure about the relationships that need to be in place. Here is my current table structure:

    Table: Product
    Product Code <-- (pr k)
    Description
    Cost Price (ex tax)
    Quantity
    Sell Price (inc tax)

    Table: Status
    Status ID <--(pr k)
    Status < -- this is the "status" of the quote ie: 'in progress', 'accepted', 'not accepted' etc..

    Table: Quote
    Quote ID <-- (pr k)
    Created < --date created, auto generated ("=now()"??) will this work?
    Expires < -- quote expiry date ("=now()+14"??) will this work?
    Total (ex)
    Total (inc)
    TAX
    Profit (ex)

    Table: Salesman
    SalesmanID <-- (pr k)
    Salesman

    Table: Company
    Company ID <-- (pr k)
    Company Name
    Contact
    phone number
    fax number
    street address
    suburb


    post code

    OK, first some basic questions

    1. Is this table structure ok? SHould I add/remove any data from it?

    2. Where exactly should my relationships be? im totally lost with this part

    3. ANY other suggestions with this would be appreciated

    Thanks in advance!

    Trav

  2. #2
    Matrix's Avatar
    Matrix is offline Admin
    Windows 10 Access 2007
    Join Date
    Jan 2005
    Posts
    421
    The tables look fine to me. But I might be wrong without the knowledge of your business.

    The relationships completely depend on your business. Maybe think about it in another way - Can all your data be represented with all these tables?

    For example, does a quote have any connections with companies? Say you could give a company a quote, then the table "quote" is related with "company". Then you may need to know if a quote is dependant on a company, if yes, you may need to insert a company id in the "quote" table to represent the relationship. But if quotes are independent of companies, in other words, a quote can be used on many companies, then you need a relationship table which consists of quote id and company id. Probably it involves a salesman and need SalesmanID too in this table.

  3. #3
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    For a beginner, you came up with a pretty good schema!

    I would do this somewhat differently (assumes taxes are always applied at a constant rate, and that there are no quantity discounts):

    Table: Product - your basic product catalog
    ProductID <-- (pr k)
    Description
    CostPrice (ex tax)
    SellPrice (inc tax)
    <Quantity removed>

    Table: Status
    StatusID <--(pr k)
    Status < -- this is the "status" of the quote ie: 'in progress', 'accepted', 'not accepted' etc..

    Table: Quote
    QuoteID <-- (pr k)
    Created < --date created, auto generated ("=now()"??) will this work?
    Expires < -- quote expiry date ("=now()+14"??) will this work?
    Status
    SalesmanID
    CompanyID
    <removed totals and profit fields--these should be calced on demand in queries>

    Table: QuoteDetails
    QuoteDetailID (PK)
    QuoteID
    ProductID
    Quantity

    Table: Salesman
    SalesmanID <-- (pr k)
    Salesman

    Table: Company <question: will you ever need >1 contact at a company?>
    CompanyID <-- (pr k)
    Company Name
    Contact
    phonenumber
    faxnumber
    streetaddress
    suburb
    postcode

    Your relationships will fall anyplace where a field on TableX appears as the primary key on another table.

    Also, you will be happier in the long run if you make sure not to use spaces in table and field names.

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

Similar Threads

  1. Using Access Instead of Visual Basic
    By dinz in forum Programming
    Replies: 2
    Last Post: 12-19-2019, 10:33 AM
  2. System.mdb or System.mdw?
    By cgriella in forum Access
    Replies: 1
    Last Post: 09-30-2008, 08:16 AM
  3. Basic Question
    By chris11590 in forum Forms
    Replies: 0
    Last Post: 08-04-2008, 05:57 AM
  4. Rookie needs help with basic reporting
    By James Rousselle in forum Reports
    Replies: 0
    Last Post: 03-01-2007, 02:36 PM
  5. Visual Basic ToolBar and ImageList
    By Athanasopolous in forum Programming
    Replies: 0
    Last Post: 01-17-2006, 03:45 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 - Senior Forums