Results 1 to 11 of 11
  1. #1
    samot79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    21

    Creating Database

    Hi,

    I am new to Access but have some experience with Excel and I understand basic concept of databases.


    I will have to setup a small database and need some help with its structure, I am not sure if I should have two or more tables.
    I want to create different product templates for different customers. Properties of a product within a template will vary, e.g quantity, size etc.

    User actions will be,
    · Assign Product templates to different customers
    · Change properties for a product within a Product template.
    · Add/Remove products in a product template.
    · Create new product templates. User should be able to copy an old template and start from there.

    In order to

    · Create internal reports to know what each product template consists of what respective customer can expect
    · Create nice looking summary reports of customers and their respective template, for new and old customers

    My question now is how many tables should I use for this? I will illustrate my fields as example.

    I will obviously need one table with customer.

    CustomerT
    FirstName LastName Adress ProductTemplate
    John Snow Winterfell 1

    Will it be enough if I then just create table with templates?
    product template quantity size category
    apple 1 5 2 fruit
    apple 2 2 1 fruit
    apple 3 1 - fruit

    Size and quantity are properties that will vary between different templates.

    I have some difficulties to see if it would be better with additional tables.

    I really appreciate some guidance in this and happy to clarify if something is unclear.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Can each customer have more than one template?

    Can each template be associated with multiple customers?

    If both are yes then this is a many-to-many relationship and at least 3 tables are needed.

    A table for customers.

    A table for templates.

    A table that associates templates with customers.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    samot79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    21
    Hi!

    Only one is true here, a customer can only have one template. Which template can change over time though, but it will only be one template one specific time.

    Will it be sufficient with two tables?

  4. #4
    samot79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    21
    One product template will have multiple products in it and one product will be in multiple product templates. I guess my question really is, should this be created as one table or two tables with additional table that associates products with products templates?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Many-to-Many relationship requires 3 tables.

    Table of Products (ID, ProductName, ProductDescription, etc) - unique record for each product

    Table of Templates (ID, TemplateName, TemplateDescription, etc) - unique record for each template

    Table of TemplateProducts (ProductID, TemplateID, ProdQty) - products and templates in multiple records but each pair is unique

    This seems to be an assembly or manufacturing type db. IMO, often not an easy one to design. Topic of numerous threads. Might do a search here.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    samot79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    21
    couldnt find any thread that helped me. It felt so simple at first, I mean it wont be a large database.

    I will make one last attempt to describe what I am trying to achieve.

    1. I want to print reports of customers and see which template they have been assigned.
    2. I want to print reports of product templates to see which products they contain and the properties of products, such as quantity.
    3. It must be easy to update a template. It can be add or remove products or change it properties.
    4. Something more nice to have, but I would like to create new templates by starting form an old one.

    Data I have.

    Template Table.
    ID name
    1 Grande
    2 Big
    3 Medium
    4 Small
    5 delux

    customer Table
    ID FirstName LastName Country template group
    1 A B Canada Grande Alpha
    2 C D Sweden Big Alpha
    3 E F Norway delux Beta

    I get stuck on product table since properties of product will depend on which table it have been assigned.

    Product Table.
    product Table
    ID name category quantity price
    1 apple fruit depending on template depending on template
    2 cola drink depending on template depending on template
    3 meatballs food depending on template depending on template

    Template table will be a couple of 100 rows. Will not be many fields, 3-5.
    Customer table will probably be 500-600 rows.
    product table will be couple of K rows.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So what is a template? Would it be like a recipe - a list of ingredients combined to create something else?

    Templates
    ID TempName
    1 Grande
    2 Big
    3 Medium
    4 Small
    5 Deluxe

    Products
    ID Product Category Price
    1 apple fruit 0.80
    2 cola beverage 0.75
    3 meatballs meat 2.50
    4 celery vegetable 1.25

    TemplateProducts
    ID TempID ProdID
    1 1 3
    2 1 4
    3 1 1
    4 2 1
    5 2 2

    CustomerTemplates
    ID CustID TempID
    1 1854 3
    2 2963 1
    3 7439 4
    4 3085 3
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    samot79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    21
    First of all, thanks for your efforts of helping me.

    One template is actually more a product that contains items, where the propertie of same item will different for some templates, e.g price.

    One customer may only have one template. My changes below should do it?

    Templates
    ID TempName
    1 Grande
    2 Big
    3 Medium
    4 Small
    5 Deluxe

    Products(I cant have price as a field here since price is one property that will depend on template)
    ID Product Category
    1 apple fruit
    2 cola beverage
    3 meatballs meat
    4 celery vegetable

    TemplateProducts (Will I complicate things if I have properties for an item/product in this table)
    ID TempID ProdID Quantity Price
    1 1 3 10 5
    2 1 4 5 2
    3 1 1 5 5
    4 2 1 2 8
    5 2 2 5

    CustomerTemplates (I assume this will be the customer table, with firstname etc. One customer may only have one template)
    ID CustID TempID
    1 1854 3
    2 2963 1
    3 7439 4
    4 3085 3

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    As you did say before, a customer can have one active template, but it can be changed sometimes. So probably your CustomerTemplates must be
    ID, CustID, TempID, ValidFrom

    where ValidFrom is a date, the given template is attached to customer. Whenewer there is a later ValidFrom for same customer, started from this date another template is valid for this customer.

    Then you can write a function, which returns a valid template for customer at any date.

  10. #10
    samot79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    21
    This is good.

    I first thought of changing one record when a customer change their template, but this way I will be able to track previous templates used.

    Thanks

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Then you will need a Customers table as well as a junction table CustomerTemplates.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Creating database
    By Wilkema in forum Access
    Replies: 6
    Last Post: 12-12-2016, 06:59 PM
  2. Replies: 2
    Last Post: 06-20-2016, 07:06 PM
  3. Creating Database
    By ThorstenG in forum Access
    Replies: 5
    Last Post: 08-07-2015, 09:18 AM
  4. need help creating database
    By johnnyb in forum Access
    Replies: 1
    Last Post: 02-18-2013, 10:10 AM
  5. Creating a database
    By WayneSteenkamp in forum Access
    Replies: 7
    Last Post: 02-28-2012, 07:13 AM

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