Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    reubendayal is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18

    Exclamation Help with Design of Tables for services to customers with different prices per customer

    Hi All,

    I have begun to explore Access so I am still understanding basics of a Database's design. And I am trying to create a database to store our Service and fees per customer. The problem I am facing is with its design. I am unable to figure out how to have a list of services available to different customers, but at different prices. The difference in prices is due to the amount of work done on each service and based on what prices are negotiated with each customer. I also want to be able to have a table/form/datasheet view of all customers, and the services each of them are provided. So to be able to check on/off a service so it shows up or does not show up for those specific customer.

    Right now I have a table each for Company, Services and Prices.

    Under Company I have:
    CVR_Number (PK)
    Company_Name
    HR_Contact1
    HR_Contact2
    HR_Contact3
    HR_Contact4
    AssigneeID (FK)


    Price ID (FK)
    Service ID (FK)
    Services


    Under Services:

    ID (PK)
    Service (List of all services)
    Comments
    PriceID (FK)
    Company ID (FK)


    Under Prices:

    ID_Prices (PK)
    CVRNumber
    Price
    Service ID (FK)


    There are also tables for Employees (for each Company), Dependents to the employees and Case related information per employee.

    My final aim is to use the database to create a list of cases we have on each company. As each company will have several employees that we assist with but those employees are authorized with different services.

    And then to finally have an Monthly orders view with list of cases and the respective services and fees and whether those cases were invoiced.

    Please feel free to suggest a link to a standard template if that would be a better place to start with.

    Thank you so much.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is one of these a CustomerPriceList?
    it would be filled from the master price list, then adjusted for the 1 customer.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For assistance with database and table design work through 1 or 2 of the tutorials from RogersAccessLibrary.
    You will learn a process that can be used with any database if you spend the time working through the tutorial(s).
    Links to the tutorials are in this material. There are several articles in various formats related to Database Planning and Design and related info.

    Are you sure you want specific prices for specific customers?
    See the Similar Threads at the bottom of the page for ideas also.

  4. #4
    reubendayal is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Quote Originally Posted by ranman256 View Post
    Is one of these a CustomerPriceList?
    it would be filled from the master price list, then adjusted for the 1 customer.
    Hi ranman256,

    Right now there's only one price sheet. Perhaps I should create a Master Price List.

    Thanks for the suggestion.

  5. #5
    reubendayal is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Quote Originally Posted by orange View Post
    For assistance with database and table design work through 1 or 2 of the tutorials from RogersAccessLibrary.
    You will learn a process that can be used with any database if you spend the time working through the tutorial(s).
    Links to the tutorials are in this material. There are several articles in various formats related to Database Planning and Design and related info.

    Are you sure you want specific prices for specific customers?
    See the Similar Threads at the bottom of the page for ideas also.
    Thank you, orange.

    These are helpful ideas.

    I will try to change my database design.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    As you are setting prieces for your services for every customer individually, you can't have a general price list, unless you have some formalized price calculation rules present (in which case you have a base price per service and a list of modifiers based on some customer characteristics, like the number of licenses, or the number of service hours in period, etc.).

    Probably you need:
    tblEmployees: EmployeeID, ForeName, LastName, ..., EmployedAt, LeavedAt;
    tblServices: ServiceID, ServiceDescription, ..., IsActive (In case some services are not available at some time moment, you can't delete them from table without corrupting your data, but you must avoid applying them currently);
    tblCustomers: CustomerID, CustomerName, ... (There may be fields for contact info, company web address, Bank info, etc. In case a customer may have several contact persons for you, you must have an additional table per row for contact person);
    tblCustomerContracts: CustomerContractID, CustomerID, ServiceID, ContractDate, ContractPrice, ..., IsActive (You can't apply the service directly, as you may contract same service several times - with different price every time. and you can have several services contracted with customer.);
    tblEmployeeContract: EmployeeContractID, ContractID, EmployeeID, IsActive.

  7. #7
    reubendayal is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Hi ArviLaanemets,

    Thank you for your reply.

    You have understood my plan perfectly. And your suggestions on setting up the tables and their relationships was spot on. I have though run in to another problem with the fees. I do have standard fees in certain cases. One common fee is the Government Fee. and this remains standard across the board. How do I add this in to the database? I am enclosing my Database's relationship model for your review.

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	39 
Size:	142.1 KB 
ID:	41954

    Also, if I wanted to get/create a combined view (query - I suppose) for all services that are authorized to each customer, then how can I achieve that?

    Thanks again.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    One Comment regarding Dependants.

    You should move the Children into a related table to Dependants.

    Edit****
    Steve's suggestion is a better option

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    I noticed that you have a table for Dependents with 2 children. What will you do when someone has 4, 6 or 8 children? Or maybe dependent parents/in-laws?

    You have
    Click image for larger version. 

Name:	Immigration.jpg 
Views:	36 
Size:	37.0 KB 
ID:	41959
    The Dependent field in table Assignees is not needed because you have a table for dependents.




    Consider
    Click image for larger version. 

Name:	Assignees1.png 
Views:	37 
Size:	28.0 KB 
ID:	41960

    For the Relationship field, you could have "Spouse", "Child", "Parent", "Spouse Parent".... All you have to do is add a new Relationship type......




    Good luck with your project..............

  10. #10
    reubendayal is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Thank you for your reply, mike60smart!

    Hi Steve,

    Thank you for highlighting the issue with the dependents. Honestly I had fields for up to 5 children. But my way was not thought out well and would have failed if I continued .

    I have amended the tables and cleared out some clutter and mismatch in the fields being switched around.

    I still do not know how to tackle the issue of linking the services authorized for each case (a case per employee). For example, if an employee off ABC Inc. is authorized 5 services out of all the services, then how does one enter that in to the database and/or how do I pull that data from the database (what sort of queries, etc.)?

    Secondly, I am still waiting on a response regarding standard fees in certain cases and how to incorporate that in the design.

    And Lastly, if I wanted to get/create a combined view (query - I suppose) for all services that are authorized to each customer, then how can I achieve that?

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	41 
Size:	142.1 KB 
ID:	41961

    thanks again!

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    reuben,
    Just a few comments for consideration. I realize your project may be a little different than the traditional Customer places Order and buys a Product/Service, but see if there are some concepts that may apply. If nothing applies to your set up, then please disregard the comments.

    Many years ago the approach you are following -different Prices for each item/service for each Customer - was a case study for the move to database. The consensus was to identify Customers,Orders, Order Details, Products/Services, Suppliers etc if necessary, And to have a standard Price list for Products and Services. Then, instead of different Prices for each Product/Service for each Customer, a loyalty type approach where Customers were assigned a Discount (%) based on number of purchases, dollar value of purchases,... anything you want -clearance sales, weekend special.... Much easier and less awkward than Price per Item per Customer. (Customer gets/has earned Discount which could be separate table with a ChangeDate if the Discount changes. You may want to identify which Product/Service Categories can be discounted, if the Discount isn't across the board).


    Be aware that Prices change with time, so don't depend on the Product Price to retrieve/reprint historic orders. Store the Price of any Sale with the Order Details record. I called this the AgreedUponPrice which is the Price this Customer paid for this Product/Service on this Order this Date. That Price stays with the historic record. If you change the Price for any reason, it does not change any historical records. The underlying concept here is that there are 2 Prices involved 1: The current Price of the Product/Service at the moment, and 2: The Price that the Customer paid for that Product/Service on a specific Order on a specific Date.
    You may also find the "stump the model" approach helps with refining your proposed database design.

    Good luck with your project.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by reubendayal View Post
    And Lastly, if I wanted to get/create a combined view (query - I suppose) for all services that are authorized to each customer, then how can I achieve that?
    This is easy. Yes, you would use a query. Add all of the required tables and put a customer PK number in the CVRNumber column to limit the records by customer.
    If you use a form, then you can use a combo box/list box to select the customer.




    Click image for larger version. 

Name:	Services.jpg 
Views:	37 
Size:	43.5 KB 
ID:	41962
    Quote Originally Posted by reubendayal View Post
    I still do not know how to tackle the issue of linking the services authorized for each case (a case per employee). For example, if an employee off ABC Inc. is authorized 5 services out of all the services, then how does one enter that in to the database and/or how do I pull that data from the database (what sort of queries, etc.)?

    Secondly, I am still waiting on a response regarding standard fees in certain cases and how to incorporate that in the design.
    What I do is similar to what you have.
    In the table "tblServicesMasterList" would have every service you provide. No prices, No company ID.

    "tbl_ServicesPrices&Company" (the letters after "Co" are cut off) is a junction table between the Company and the services. BTY, don't use special characters/punctuation in object names - you have an "&" in the table name.
    This table shows the services that are available by company. When you select a service that is available in/to a company, you can add the agreed service prices to that company.
    Company A could have 5 services available with certain prices and Company B could have 5 different services, while Company C could have all 10 services with still different prices.

    You would use "tbl_ServicesPricesCompany" to allow which services are available to each Assignee.

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by reubendayal View Post
    I have though run in to another problem with the fees. I do have standard fees in certain cases. One common fee is the Government Fee. and this remains standard across the board. How do I add this in to the database?
    You can't have fees hardcoded into your service contracts. Consider what happens, when Government Fee is changed halfway through contract. Or your government introduces some new fee at some time moment.

    Fees are calculated for invoices - for certain payment. You need a tables, e.g.
    tblInvoices:InvoiceID, CustomerID, InvoiceDate, ...;
    tblInvoiceRows: InvoiceRowID, InvoiceID, RowType, RowQty, OneAmount;

    , where RowType differs between service price, discount, Government Fee, etc. RowQty is for case, when you deliver multiple of same service to customer. OneAmount is amount for single quantity, and is entered manually (e.g. for discount when this is irregular), or is calculated in subform of invoices form according RowType. And you need a table/tables, where are stored all parameters needed to calculate amounts for every RowType.

  14. #14
    reubendayal is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Thanks orange.

    Yes, as you say the "the traditional Customer places Order and buys a Product/Service", is not the exact picture here. Our business is a service business. And as part of our service, we receive requests from the Company HR to assist their employees with respective services.

    Although my current aim is to assist with designing a database for my colleagues that will automate their very manual invoice tracking and business process at present. My final plan is to scale this up to have a simple case management system for our team to handle the local companies we service.

    Now regarding the prices, as I have explained earlier, the fees are customer based. We do have our standard fees too. But I do not know how often those are used. And for the rest of the customers, the fees are quoted based on different parameters such as Volume of cases received each year, number of services opted by each Customer/company, etc.

    I fully agree with you that "Stump the model", is the way to test out the database design.

    The underlying concept here is that there are 2 Prices involved 1: The current Price of the Product/Service at the moment, and 2: The Price that the Customer paid for that Product/Service on a specific Order on a specific Date.
    Could you help me understand how can I have a 2 approaches you suggest about the 2 prices involved and where to store this data?

    One thing that has been consistent is the constant encouragement from all of you and the good wishes towards my project.

    Thank you so much for that!

  15. #15
    reubendayal is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    thanks for all the suggestions and tips, Steve.

    I have implemented the changes. and here's the updated relationships model.

    Click image for larger version. 

Name:	Relationships-20May.jpg 
Views:	30 
Size:	120.0 KB 
ID:	41980

    You would use "tbl_ServicesPricesCompany" to allow which services are available to each Assignee.
    How do I do this? Should I make another relation between the tbl_CaseInformation using CaseID (PK) with ServiceID(FK) in tbl_ServicePricesCompany?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-17-2014, 11:08 AM
  2. Replies: 5
    Last Post: 04-22-2013, 07:50 AM
  3. Replies: 2
    Last Post: 09-23-2012, 10:20 AM
  4. Creating Packages of Services - diff discounts by Customer
    By Marcotte in forum Database Design
    Replies: 8
    Last Post: 04-13-2012, 04:06 PM
  5. Different Prices for diff customers
    By kerrin in forum Access
    Replies: 4
    Last Post: 12-31-2011, 07:50 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