Results 1 to 6 of 6

Newbie got lost with the design!!

  1. #1
    Hargo67 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3

    Newbie got lost with the design!!

    Good evening experts

    I have read a couple of MS Access guides and decided to get stuck in, but I am now at the stage where I'm unsure if what I have is correct and will work.

    I am trying to create a Customer / Order database initially, then perhaps develop it further in the future to incorporate employees etc.

    I have 4 tables at the moment based upon my initial reading, but am concerned I have stuff I don't need and other stuff missing that could be crucial.



    I have related my tables as I think they should be, but have no idea how I would add records (presumably with a form, but not sure how the orders form (or subform) would work. To give some context, the business sells organic juices, beansprouts and micro-greens in various pack sizes and/or weights. Thus an order form would need to have the ability for accepting multiple product types and quantities. A big concern here is how I deal with the order value and any discounts the customer may be entititled to.

    I attache my relationship diagram showing my tables with key/foreign fields. If someone could scan it for appropariateness and suggest a way forward for order value it would be much appreciated

    Many thanks in advance

    hargo67
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,303
    Looks quite reasonable. A few thoughts:
    -you can use the Now() function in Access to record Date and Time as 1 field
    -you should avoid embedded spaces and special characters (CIF/DNI/NIE) in field and object names
    -there doesn't seem to be any Price associated with Product unless ProductRRP is it?
    -you may want to separate Delivery to a table (especially if Partial Deliveries are possible)
    -

    Be aware that there are 2 Prices involved in such a business.( I am ignoring any Prices/Costs you may incur from Supplier(s))
    First, is the current selling price of a Product. This can change with time. It is NOT constant.
    Second, is the price you charge for a line item(Product) on that Order for a Customer at the time of sale. This should be recorded with the line item on the Order detail. It allows you to keep a history of processed Orders; allows for Clearance and special sales; and provides a means to have Loyalty programs.... I call this the AgreedTo price.
    See this and/or this for more info.

    It is refreshing to see a self-identified newbie designing and working from a data model.

    There are links in my signature that may be helpful.
    Good luck with your project.

  3. #3
    Hargo67 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3
    Thanks very much for your suggestions - hopefully I can implement them! haha

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,445
    Welcome to the forum.

    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

    You have fields "CIF/DNI/NIE". This is bad because of the special characters (the "/"). Maybe use "CIF_DNI_NIE"
    You have a couple of fields with parenthesis in the field name - Also bad. (UnitWeight(Grams)) Maybe use "UnitWeightGrams" or "UnitWeight_Grams".

    What is the field type for "CIF/DNI/NIE" in the "Customers" table? Text or Autonumber?


    See Microsoft Access Tables: Primary Key Tips and Techniques

    Autonumbers--What they are NOT and What They Are
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Hargo67 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3
    Quote Originally Posted by ssanfu View Post
    Welcome to the forum.

    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

    You have fields "CIF/DNI/NIE". This is bad because of the special characters (the "/"). Maybe use "CIF_DNI_NIE"
    You have a couple of fields with parenthesis in the field name - Also bad. (UnitWeight(Grams)) Maybe use "UnitWeightGrams" or "UnitWeight_Grams".

    What is the field type for "CIF/DNI/NIE" in the "Customers" table? Text or Autonumber?


    See Microsoft Access Tables: Primary Key Tips and Techniques

    Autonumbers--What they are NOT and What They Are




    Thanks very much Steve, seems good practise - one thing, could you explain the problem with Lookup fields?


    CIF/DNI/NIW WAS text but i deleted this field (but changed an added field to a list box to accommodate)

    Cheers

  6. #6
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    156

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

Similar Threads

  1. Database Design - Newbie
    By Kookai in forum Database Design
    Replies: 5
    Last Post: 10-17-2016, 05:16 AM
  2. Newbie Pre-Design Question
    By JamieB in forum Database Design
    Replies: 13
    Last Post: 10-18-2013, 05:20 PM
  3. Lost Newbie.....Please help.
    By beachbumch in forum Access
    Replies: 3
    Last Post: 06-26-2012, 10:06 AM
  4. Newbie needs help with survey design
    By Buakaw in forum Database Design
    Replies: 5
    Last Post: 01-20-2011, 10:20 PM
  5. newbie needs design help
    By ashiers in forum Database Design
    Replies: 0
    Last Post: 09-13-2008, 07:05 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
  •  
Tech Forums: Microsoft Office Forums