Results 1 to 12 of 12
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Single & Group Events table(s)

    It feels like my Events & PeopleEvents tables are in conflict. Currently:

    tbl_PersonEvents tbl_Events
    PersonEventID EventID
    PersonFK EventDate
    EventFK EDateAcc
    EventDate EventLabel
    EDateAcc EventDescription
    Using a Excel and trying to stump the model I have noticed that Events in tbl_PersonEvents are one of's (ie Brith, Death) and tbl_Events seems to group events (School, Grad) and that many have From-To dates.


    I would like to have Single list of dates and events, but if I split these tables , will that make a messy query?
    E.G.

    tbl_PersonEvents tbl_Events tbl_PersonGEvents tbl_GEvents
    PersonEventID EventID PersonGEventID GEventID
    PersonFK EventLabel PersonFK FrGEventDate
    EventFK EventDescription GEventFK FrGEDateAcc
    EventDate ToGEventDate
    EDateAcc ToGEDateAcc
    GEventLabel
    GEventDescription
    This doesn’t feel right, maybe the labels are off, or the structure is wrong, I just don't know.
    Any suggestions are welcomed.

    Thanks for Looking.
    PS It seems that the ToDate could be replaced with Length of the event in days but....

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would you have EventDate and EDateAcc in multiple tables?

    You don't show tbl_Persons. I would record a person's birth and death dates in tbl_Persons. Then if there is birthday party or funeral, that would be an event.
    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
    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
    Glad to hear about _stump the model_. I tend to agree with June, but perhaps a few examples that caused you to pause and reconsider/question an approach would help with context.

  4. #4
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    EDateAcc is a +- accuracy on EventDate in days. I’m using this style to avoid nulls, given that not all dates are known at this time (and maybe never known).

    The From and To dates read (to me) as From Group Event Date (Accuracy range). I do acknowledge this may be confusing, but I am open to other labels.

    Thanks for Replying

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Been doing that for a long time (stump), in fact have written only a little VBA macros in Access, all Design for this.

    I was trying to keep it generic, but think soldier for person, awards, promotion, wounds, battles for the details.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then I am not really understanding the need for 'group events'.

    Just create a record for each person's 'event' - award, promotion, wound, etc.

    Could an award date be different from presentation date? Does that really matter?
    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.

  7. #7
    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
    Is your issue with events or more associated with Individual vs Group (company, battalion, squad, team, platoon, brigade....)?

  8. #8
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Quote Originally Posted by June7 View Post
    Then I am not really understanding the need for 'group events'.
    Part of Normalization, preventing having repeating data for an event that many did at the same time.
    Quote Originally Posted by June7 View Post
    Just create a record for each person's 'event' - award, promotion, wound, etc.
    So for Iwo Jima, I would have X number of records, with Y different dates. Doesn’t sound normalized to me. A person could be there and not be entitled to the award for a number of reasons.
    Quote Originally Posted by June7 View Post
    Could an award date be different from presentation date? Does that really matter?
    Oh for sure. It takes months for an award to be authorized, but in this case no it doesn’t matter.

    At this time I have NO intentions of working this with units/formation which are the true groups.
    I hope this helps and thanks for responding.

  9. #9
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Quote Originally Posted by orange View Post
    Is your issue with events or more associated with Individual vs Group (company, battalion, squad, team, platoon, brigade....)?
    I feel its events/ group events which are similar but different.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works."

    Either way, there will be an individual PersonEvent record for each person, regardless of whether or not individual was just 1 of many for that same event. Whether or not a separate table for describing these mass events is justified depends on how much info would be replicated.

    Yes, X records for Iwo Jima and Y dates. If someone was there but not receiving award then there would be no award record for that individual.
    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.

  11. #11
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    So could I (by flag or such) link the Event to either table, OR combined the two table together with out suffering a lot of nulls?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As I said, balancing act. You have to decide if you want to suffer a lot of nulls (I have). An event could be for 1 person or 100. Don't see why the original 3 table structure would not work.

    tblPersons

    tblEvents

    tblPersonEvent
    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. Replies: 7
    Last Post: 08-31-2018, 01:40 PM
  2. Replies: 4
    Last Post: 03-30-2018, 02:31 PM
  3. Replies: 1
    Last Post: 10-08-2017, 09:59 AM
  4. Group on a single field twice
    By rhewitt in forum Reports
    Replies: 3
    Last Post: 08-24-2016, 11:24 AM
  5. updating single row per group in access query
    By sugat11 in forum Queries
    Replies: 6
    Last Post: 07-05-2016, 08:03 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