Results 1 to 4 of 4
  1. #1
    SamAtticus is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    2

    Post Storing occurrences against dates

    Hi Guys,

    I'm creating a database that logs problems, and want a nice way to log the number of occurrences of any specific problem against a date, say a problem happened 5 times on the 10/10/2019 for example

    I figure the best way to store this would be to link an excel table with the dates in the rows, then create a new column for each unique problem, but I don't know how to do this.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Do you want to do this in Access (why to use Excel then), or in Excel (wrong forum when this is the case)?

    For either of cases, instead of adding new columns again and again, register the log entry, and the type of problem in single table row. Additionally you need a table where problem types are registered before you can register the entry of this type problem into log table.

  3. #3
    SamAtticus is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    2
    I wanted to use access to do this - the problem though is that the main table in this databases stores a new unique problem with each record

    I've ended up finding a solution to this by changing the problem, I can store occurrences by calendar week instead of by day, so have a table with the unique problem ID, the year, and each calendar week (1-52)

  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
    Quote Originally Posted by SamAtticus View Post
    I've ended up finding a solution to this by changing the problem, I can store occurrences by calendar week instead of by day, so have a table with the unique problem ID, the year, and each calendar week (1-52)
    The problem now is that you do not have a normalized structure. (you have repeating fields - the weeks).



    Without knowing more about your requirements/data, consider this design:
    Click image for larger version. 

Name:	Issues1.png 
Views:	12 
Size:	18.7 KB 
ID:	39943

    IssueID_PK and OccurrenceID_PK are Autonumber type fields.
    IssueID_FK is a Long type field and is the foreign key link to table "tblIssues".



    Maybe you would post a few examples of the data and/or post your current dB???

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

Similar Threads

  1. Counting all occurrences of a unique value
    By CJFugate in forum Queries
    Replies: 3
    Last Post: 04-06-2019, 03:40 AM
  2. Counting occurrences using a query
    By RickK in forum Queries
    Replies: 4
    Last Post: 11-16-2016, 09:31 AM
  3. Counting occurrences of specific data
    By DaveC in forum Reports
    Replies: 1
    Last Post: 09-12-2016, 01:29 PM
  4. Replies: 3
    Last Post: 02-14-2012, 09:43 AM
  5. Report the number of occurrences in a field
    By ecpike in forum Reports
    Replies: 1
    Last Post: 05-05-2009, 01:57 PM

Tags for this Thread

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