Results 1 to 8 of 8
  1. #1
    AccessGG is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3

    Database design

    hi everyone



    New to Access and this has had me puzzled for a while.

    Could someone advise of the best db design to capture the following data

    Action Sites 01-Jan-18 08-Jan-18 15-Jan-18 22-Jan-18 29-Jan-18 05-Feb-18 12-Feb-18
    Action1 Site1 83 90 105 100 81 79 88
    Action2 Site1 37 42 50 47 33 34 33
    Action3 Site1 46 48 55 53 48 45 55
    Action4 Site1 4 14 24 20 12 7 23
    Action5 Site1 3 6 24 25 31 9 14
    Action1 Site2 94 85 85 111 65 66 80
    Action2 Site2 20 21 22 39 19 16 14
    Action3 Site2 74 64 63 72 46 50 66
    Action4 Site2 24 19 27 30 27 31 45
    Action5 Site2 17 28 27 4 73 32 31
    Action1 Site3 177 175 190 211 146 145 168
    Action2 Site3 57 63 72 86 52 50 47
    Action3 Site3 120 112 118 125 94 95 121
    Action4 Site3 28 33 51 50 39 38 68
    Action5 Site3 20 34 51 29 104 41 45

    As you see, I have 5 actions repeated across 3 sites (site 3 is the addition of site 1&2 in the relevant column) with each capturing data according to the date

    thanks, Gary

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Without understanding how the data is captured. I would say just 1 table.
    Have a table with Action, Site, Date and the Value for the Date, then make a crosstab query or similar to produce what you have. grouping on the weeks of date values.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is a spreadsheet setup and is ill suited for a database. What you want to do is research normalization, take a crack at it and maybe post back with a picture of your db relationships window or other pictorial representation of your schema. There are LOTS of sites on this subject. Here are simple ones. Find something that speaks better to you if these don't help. On second thought, here's a list so that you don't fall into some other common traps.
    Normalization is paramount. Diagramming maybe not so much for some people.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    Important for success:
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    AccessGG is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3
    here you go

    my attempt at structure and the data table

    Click image for larger version. 

Name:	datastructure.PNG 
Views:	34 
Size:	20.1 KB 
ID:	33623

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What is "Actions" in yellow, a table name or a field name? I think yellow represents a table name and Action1, Action2 etc are field names. Not to be rude, but if so I have to wonder if you read any of those links. Not only would that still exhibit a spreadsheet approach, you're using reserved words for field names which the links advise against.

  6. #6
    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
    I agree with Micron. The graphic doesn't communicate what you are dealing with.

    Here is a possible starting model based on
    -there is a finite number of Actions
    -there are a finite number of Sites
    -some activity/action occurs at a Site on a specific Date and produces/uses some Quantity of something.
    Click image for larger version. 

Name:	PossibleMOdel.png 
Views:	29 
Size:	12.1 KB 
ID:	33624

    Good luck.

  7. #7
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    How about something like this?
    Click image for larger version. 

Name:	Junk.png 
Views:	29 
Size:	7.3 KB 
ID:	33625
    Every table has a primary key and the SomeDataHere table has a foreign key to the other two tables

  8. #8
    AccessGG is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3
    yes the yellow boxes are table names but I can see where you are going

    I'll give it another go - thank you

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

Similar Threads

  1. Replies: 18
    Last Post: 12-04-2017, 05:25 PM
  2. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  3. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  4. Database design/help
    By WayneSteenkamp in forum Access
    Replies: 7
    Last Post: 03-06-2012, 06:58 PM
  5. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 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