Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    reubendayal is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Quote Originally Posted by ArviLaanemets View Post
    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.
    This is really helpful. You are correct about this as recently there have been changes to the government fees quite sporadically. Could you explain where should the government fees be stored per case?

    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
    Thanks for this explanation. Very helpful indeed.

  2. #17
    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
    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?
    In general,

    the current price of a Product/Service is stored in the Product/Service table.
    For example, consider the Price of 1 unit of Product X is 5 dollars on May 1. And on May 2 a Customer buys 10 units with total Price 50 dollars. In the OrderDetails record you record the Qty (10units), Price/unit (5 dollars).

    Also consider, the Price of 1 unit of Product x changes to 8 dollars on May 10.

    If you rely on the Price of the Product in the Product table, if you look at that Order or reprint that Order from May2, you would get Customer purchased 10 units of Product X @ 8 dollars/unit with a total of 80 dollars.

    You record the Price paid by the Customer at the time of the sale in the Order details table. It is a permanent record.

    When people use only the Price of the Product in the product table for calculations, they are using the current Price of the Product. And the current Price of the Product changes with time(usually) and can be discounted for clearance or other Sales.

    It may seem obvious, and it is once you've experienced the issue.
    Some people will include a PriceEffectiveDate in the Product table and adjust their calculation logic accordingly.
    Regardless of the technique used, the bottom line with the 2 prices concept is there is Price of the Product when it was sold; and the "current" Price of the Product today.
    Last edited by orange; 05-21-2020 at 06:14 AM.

  3. #18
    reubendayal is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Quote Originally Posted by orange View Post
    In general,

    the current price of a Product/Service is stored in the Product/Service table.
    For example, consider the Price of 1 unit of Product X is 5 dollars on May 1. And on May 2 a Customer buys 10 units with total Price 50 dollars. In the OrderDetails record you record the Qty (10units), Price/unit (5 dollars).

    Also consider, the Price of 1 unit of Product x changes to 8 dollars on May 10.

    If you rely on the Price of the Product in the Product table, if you look at that Order or reprint that Order from May2, you would get Customer purchased 10 units of Product X @ 8 dollars/unit with a total of 80 dollars.

    You record the Price paid by the Customer at the time of the sale in the Order details table. It is a permanent record.

    When people use only the Price of the Product in the product table for calculations, they are using the current Price of the Product. And the current Price of the Product changes with time(usually) and can be discounted for clearance or other Sales.

    It may seem obvious, and it is once you've experienced the issue.
    Some people will include a PriceEffectiveDate in the Product table and adjust their calculation logic accordingly.
    Regardless of the technique used, the bottom line with the 2 prices concept is there is Price of the Product when it was sold; and the "current" Price of the Product today.
    Thank you for the explanation, Orange.

    I understand your concept on the change of price with time. But with my limited knowledge on structuring this, I am sorry I am unable to figure out how to have the government fee and similar standard prices in to the database. Here's how the relationships look. Can you spot any issues? I am also enclosing a copy of the database for you to troubleshoot if you like.

    Click image for larger version. 

Name:	Relationships-26May.jpg 
Views:	22 
Size:	129.8 KB 
ID:	42009

    Link to the file:

    https://wetransfer.com/downloads/41d...6050901/11a877


    The other issue that seems to be coming up from the current structure of the tbl_ServicePricesCompany and tbl_ServicesMasterList setup is that I am unable to get a combobox of all services in like a drop down list to be able to chose from when assigning it to a particular Customer. This will be very important especially when adding/authorizing a new service to a customer account.

    Thanks again.

  4. #19
    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
    I wasn't addressing your set up specifically. My post was more of a comment --be cautious of how you handle Price. I've seen many threads where problems with Price, Sales, reprint of Orders etc have been traced back to this design concern.

    With respect to your tables and relationships, I prefer to create a paper-based model and refine/vet it by using sample test data and sample scenarios. This tutorial from RogersAccessLibrary -especially the Hernandez process - should help with designing any database. The Database Planning and Design link in my signature has many related articles in various formats.
    Good luck with your project.

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

    I have only looked tblCompanies and this table is wrong.

    You are using CVR_Number as the Primary Key but you have CompanyID as an Autonumber ?

    The Primary Key should be CompanyID - Autonumber

    Then you should be linking the related tables on CompanyID Foreign Key

  6. #21
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    I Have reworked the relationship between tblCompanies and tblServicePricesCompany

    I also sorted out the Lookup for Services

    Look at how the Relationships are set between these two tables.
    Attached Files Attached Files

  7. #22
    reubendayal is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Quote Originally Posted by orange View Post
    I wasn't addressing your set up specifically. My post was more of a comment --be cautious of how you handle Price. I've seen many threads where problems with Price, Sales, reprint of Orders etc have been traced back to this design concern.

    With respect to your tables and relationships, I prefer to create a paper-based model and refine/vet it by using sample test data and sample scenarios. This tutorial from RogersAccessLibrary -especially the Hernandez process - should help with designing any database. The Database Planning and Design link in my signature has many related articles in various formats.
    Good luck with your project.
    Thank you so much, Orange.

    I think I am making some progress.

  8. #23
    reubendayal is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Feb 2015
    Location
    Denmark
    Posts
    18
    Quote Originally Posted by mike60smart View Post
    I Have reworked the relationship between tblCompanies and tblServicePricesCompany

    I also sorted out the Lookup for Services

    Look at how the Relationships are set between these two tables.
    Hi Mike60smart,

    Thank you for sorting out the database. I can see how the multiple one to many relationships I had created in the database was resulting in queries not showing any data.

    You help is much appreciated.

    Reuben

Page 2 of 2 FirstFirst 12
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