Results 1 to 3 of 3
  1. #1
    AccessFreak is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    17

    Relational Database Design - Client Rates vs Supplier Cost

    Good Morning




    I am putting together a plan for a database which can be used to keep track of our client's rates, annual rate increases, supplier cost and profit.

    I am however struggling with which tables would be best suited ?


    So far I have the following which does not seem to be correct - and I cannot get my head around a better solution:


    tblClients - would contain basic client information
    tblInventory - would be the various services we offer
    tblPriceList - (lookup inventory code from "tblInventory" ) and also contain the customer's rate.




    tblSuppliers - basic supplier information
    tblSupplierRates - (lookup inventory code from "tblInventory") and also contain the supplier's rate for that particular service


    Reports:
    Profit Report - reflects the list of clients - along with the client rate from tblPriceList - and compares it with the supplier rate from tblSupplierRates


    - the problem is that more than 1 supplier can have a rate for each different inventory item


    Any advice or guidance will be appreciated


    Thank you

  2. #2
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by AccessFreak View Post
    - the problem is that more than 1 supplier can have a rate for each different inventory item
    So you also need a lookup to suppliers in tblSupplierRates.

    You also need to think about a solution for price changes. Consider to add a startdate to tblSupplierRates and tblPriceList.
    Groeten,

    Peter

  3. #3
    dblife's Avatar
    dblife is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2011
    Location
    South Shields, UK
    Posts
    95
    "the problem is that more than 1 supplier can have a rate for each different inventory item"

    And in the real world, those suppliers also change their prices, I expect.
    I would have a junction table of all the suppliers and their cost for the items/inventory they sell you and keep adding to the junction table as they update their prices.
    This will allow you to keep a track on what their price changes are through a filtered query.
    You will be able to see latest price for each item of inventory through the 'MAX' function in a query and it will give you the latest price form each supplier who sells you that item.



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

Similar Threads

  1. New Relational Database design
    By JonathanT in forum Database Design
    Replies: 27
    Last Post: 02-05-2020, 07:41 AM
  2. Replies: 7
    Last Post: 10-04-2017, 09:18 AM
  3. Replies: 3
    Last Post: 03-24-2017, 12:34 PM
  4. Need to hire out relational database design
    By janakybrent in forum Access
    Replies: 5
    Last Post: 02-19-2016, 05:11 AM
  5. Relational Database Design Questions
    By mribnik in forum Database Design
    Replies: 40
    Last Post: 08-09-2011, 02:57 PM

Tags for this Thread

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