Results 1 to 6 of 6
  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
    16,716
    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
    9,664
    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

  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
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

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
  •  
Other Forums: Microsoft Office Forums