Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lloyd_A is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    8

    Customer Product Discount Sub form

    Hi All,

    Apologies for what may seem a simple question. I've done a fair bit of searching online but can't find the right question to ask to return the answer I'm looking for so thought it best i just ask on here.

    I'm just playing around with a variant of the Northwind sample template database and am looking to insert customer discount into the customer details form (for those that know the template)

    The customer discount varies for each customer and each product, so I'm thinking I'll need to create a new table which is linked to both the customer and product table and come up with the following fields;

    Customer ID - from Customer table
    Company - from Customer table
    Product ID - from Product table


    Product Name - from Product table
    Product Code - from Product table
    Standard Cost - from Product table
    List Price - from Product table
    Discount % - new field
    Discount Price - new field

    Now that could be completely the wrong way of going about it.

    The end goal is to have a sub form underneath the Customer Details form which will display every product in a list along with all the information stated in the fields above.

    Apologies if I haven't made my question clear but hopefully it's given a brief overview and if you have any questions then you can just ask me.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    One of the rules for database normalization is do not repeat data, so the cross-reference table would have just the PK's and the discount: CustomerID, ProductID and discount percent. Another rule is not to store calculated fields so Discount Price will not be a field on the table as it is calculated each time it is needed. For your subform your would create a query which would display all the data that you need.

    Note - the table that is missing is the Orders table, that is where all the data comes together - price, discount % (can be changed per order, maybe depending on quantity ordered), etc.

  3. #3
    Lloyd_A is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    8
    Thank you for your quick reply.

    If i put this in an example

    Customer Details Form

    Customer A

    Product 1 - £10 - 10% discount - £9 discount price
    Product 2 - £50 - 5% discount - £47.50 discount price
    Product 3 - £100 - 8% discount - £92 discount price


    Customer Details Form

    Customer B

    Product 1 - £10 - 20% discount - £8 discount price
    Product 2 - £50 - 10% discount - £45 discount price
    Product 3 - £100 - 5% discount - £95 discount price


    So i get what your saying however because the discount is different for each product & for each customer would this effect how i go about doing it?

    Also this is me just starting out on Access databases again so any 'simple' instructions would be much appreciated

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This would be a form/subform situation - the user would select a customer on the main form and the subform would display the details as you have them above. The record source for the subform would a query.

  5. #5
    Lloyd_A is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    8
    Perfect thank you!

    So with regards to building the query obviously my product fields (product name, standard price, list price) will come from the product table but as the discount % and discount price are new fields and are done on a customer by customer basis will i have to add these fields to the product table or create a new table or something else completely?

    Thank you again for all your help so far.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The idea was to have a separate table to house those fields (post #2). You would link this new table to both Product and Customer tables by their primary keys. Do it in the Relationships window then it will be set up for you whenever you need it. The query would include all three tables.

  7. #7
    Lloyd_A is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    8
    Ok so just to make sure I'm getting this right from the start...

    Click image for larger version. 

Name:	Relationships.JPG 
Views:	20 
Size:	55.7 KB 
ID:	26140

    Does that look correct? with the correct relationship setup?

    Thanks

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Perfect! Always use referential integrity (do you understand its meaning?), but use the Cascade with caution and full understanding.

    I do need to make some comments on the names of your fields. Names will come back and bite you later on and is very hard to change later, so best to get them right at the start.

    When you create tables, you have your "techy" hat on, the rest of your system is for the user. Names can be changed in queries and on forms to keep the customer happy, but the names on the tables need to be of the technical kind.

    1. Never have spaces in names
    2. Always have meaningful names (ID? what is that?) - Company, Product, these names don't say what the field contains (name? desc?)
    3. When a field is defined once, always keep the same name - ProductID must be called that on all tables
    4. Never use the same name in two places - such as ID, which could describe many different contents, depending on the table
    5. Do not use special characters, such as %, in field names
    6. Techy's know that "Prod" is a Product, you don't have to have long names

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Forgot to mention:

    When you use formatting/lookup's/etc on tables it becomes very difficult later on especially with troubleshooting. Real techy's never do that, all fields on tables are pristine, clean and uncluttered. That is what queries/forms/modules are for, to manipulate data as and when needed. It also results in a decrease in performance as every time that table is updated in some way those fields need to be kept up to date. Fine when you only have 10 records, but....

    I am referring to (1) the Supplier IDs and (2) the discount price.

  10. #10
    Lloyd_A is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    8
    Thank you for your reply again aytee111, so from the top... I do have a basic understanding of it, simply put that it insures that fields across the database are valid?

    With regards to the names I've just gone through and had a tidy up (see below) - I realised after my post that I'd made a mistake with discount price as you mentioned in a previous post this will be a calculated field.

    Click image for larger version. 

Name:	Products.JPG 
Views:	13 
Size:	46.9 KB 
ID:	26146Click image for larger version. 

Name:	Customers.JPG 
Views:	13 
Size:	39.5 KB 
ID:	26147Click image for larger version. 

Name:	Discount.JPG 
Views:	14 
Size:	16.0 KB 
ID:	26148

    Is that looking better?

  11. #11
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    MUCH better, job well done!

    What is discount? DiscountPct
    SupplierIDs - plural? Lookup?
    Beware of standard names - description could depend on the table, Product description, part description. Same for Address, does it belong to the customer or the supplier? Just in case you need to use those names again in the future.
    Currency format, not really needed, you could use "Number" and "Double" instead and format when you need to (format is for looks only, not a technical thing)
    Techy's are lazy, make the computer work not me, those names are very long! That is a choice thing, however.

    In a nutshell, your number one job as the database creator is to keep the data correct (users think it must only look good!). Data integrity therefore is your top priority. Use Access for that as much as possible, one less thing for you to have to do. By putting all links into the relationship window and setting the referential integrity flag goes a long way towards ensuring this. It prevents deleting records by mistake, for instance, and then having records that are orphans or widows - very bad!

  12. #12
    Lloyd_A is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    8
    Ok so I've gone through and updated all my tables, shortening the names as well.

    Discount was discount % which I've updated to DiscountPct. SupplierIDs within the Product table detail which suppliers we can get this product from.

    Below is all of the relationships within the database (a bit more for you to get your teeth stuck in to and pick out the flaws)

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	11 
Size:	163.3 KB 
ID:	26149

  13. #13
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1. All those left and right joins - what you are telling the database is that it doesn't matter what you enter in that field. For instance your Order can have a ShipperID that is meaningless/invalid. Are you sure you want it to be like this? None of my relationships ever have them so I wonder why you need them. If it is to say that there may or may not be a ShipperID on your order then it should be an inner join. Or you can have Purchase Order details for a Purchase Order that doesn't exist. Goodbye referential integrity! For the type fields, such as status you could have a default
    2. You have broken my rule number 3! e.g Inventory Transaction table (and others)
    3. Your table names have spaces in them! What I said about spaces, special characters, etc applies to all objects in the database - forms, queries, tables, the lot.
    4. I'm not sure that you need all those "status" tables, surely one would suffice? (additional maintenance, troubleshooting, etc)

    You seem to have a good handle on database design generally, well done!

  14. #14
    Lloyd_A is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    8
    So i took a day out to go through everything you said and clean up/simplify it all, making sure all fields were named the same throughout the database and making sure any spaces/special characters had been removed.

    So now going back to my original task which was to implement the discount sub form within the customer details form - the discount table has been created but could you now assist me as to how i would exactly create the query for it?

    Thanks

  15. #15
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you mean by "the query" - now that you have created the table structure, what do you want to do next? A way for the user to input the disc %? Reporting?

    And good job fixing the names everywhere, you'll never do that again!

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

Similar Threads

  1. Replies: 2
    Last Post: 07-03-2016, 02:30 PM
  2. Access - product list by customer margin
    By Evans2 in forum Access
    Replies: 1
    Last Post: 11-29-2015, 06:21 AM
  3. Replies: 3
    Last Post: 06-26-2014, 10:08 PM
  4. Replies: 5
    Last Post: 03-23-2014, 09:01 AM
  5. Replies: 3
    Last Post: 09-03-2012, 04:54 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