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

    Perpetuation

    Okay, I’m on a thinking weekend and working with ideas which I haven’t solved or thought hard about.



    tblUnits has UnitID. At some point (maybe after unit deceased) it is given BattleHonours. Another UnitID will be assigned to perpetuated those honours when the assigned unit is deceased.

    So this gives a tblHonours, and tblUnitHonours. Should there be a UnitPerpetuates table? Or is there a another way assigning the honours.

    What I’m thinking is should the perpetuating unit be assigned directly or look it up though the true units?

    Hope this makes sense

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What is a unit? What are BattleHonours? What does 'perpetuate' mean in this context? Why would you assign another UnitID as opposed to just setting a value in a field?
    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
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    hmmm See https://www.accessforums.net/showthread.php?t=73590 for an old Schema to help with Unit. Battle Honours are given to units that where at a battle. WW 1 Canadian Units where are disbanded in 1920, and the battle honours granted in 1929. The WW 1 units are perpetuated by existing units in service at that time. Some of those units have been amalgamated with others units since. A problem not disclosed before is that some honours can't be used if you already have another, I'm still working out what that looks like, so that's really is a side issue.
    does this help?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    This schema starting to sound familiar. Seems I may have tried to help before and was not successful. Maybe someone else will have better insight.
    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.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    A hypothetical DB structure:
    tblUnits: UnitID, CreatedAt, DisbandedAt, ...;
    tblUnitNames: UnitNameID, UnitID, AssignedAt, UnitName;
    tblUnitsAmalged: UnitsAmalgedID, UnitID, AmalgedAt, AmalgedUnitID;
    tblHonours: HonourID, HonourName;
    tblUnitHonours: UnitHonourID, UnitID, HonourID, AssignedAt.

    You can crate a query to return the list of units, which have certain Honour awarded, or have amalged an unit with the same Honour awarded.

  6. #6
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Travel day, sorry for the slow reply.
    This looks close to what I have. I will try to be clearer when I get to my desk.

  7. #7
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    I'm not a great communicator, what I feel is tight and concise, has been called confusing and filled with jargon. Oh well. A friend once said that asking for advice in forms is like asking a lecture hall for advice. You don't know WHO is answering, but you know they are giving the best answer that they can. I really do believe that.

    I have worked on two industrial 200+ table databases that seemed to be well designed and wrote COBOL code for them 30+ years ago. I have no schema from those times, so sometimes I think I know where I'm going but don't trust myself fully. I'm always looking for the "better" way because I DON'T know Access, and I'm no expert in Schema design. Any hints or methods are looked at.

    If you see me trying to do something in Access, in Schema design, or in style which cause problems, please speak up. I know about (I think) Recursive calls must be done in VBA, but I don't know if that is a problem or a hiccup, or what that really means.

    This a hobby of mine (Canadian Military Lineage), which I have been playing with for over 40+ years. I feel I know the material, but even this week I found things that don't fit my mental image of it. When I ask questions, I'm trying to get a general answer. To explain some of outlying points gets difficult, so I leave them out. I’m not trying to trash your answer but wanting to discuss it further.

    The latest Schema is about 40 tables and a lot more complex then I every imagined. I can always restructure the data for a couple of problem pieces but doing so can wreck the integrity of the database. I’m trying to make a research quality Database the first time out. (Dreamer).

    Rant Over


    ArviLaanemets
    A hypothetical DB structure:
    tblUnits: UnitID, CreatedAt, DisbandedAt, ...;
    tblUnitNames: UnitNameID, UnitID, AssignedAt, UnitName;
    tblUnitsAmalged: UnitsAmalgedID, UnitID, AmalgedAt, AmalgedUnitID;
    tblHonours: HonourID, HonourName;
    tblUnitHonours: UnitHonourID, UnitID, HonourID, AssignedAt.
    So looking atLineage_2018-09-03 sm2.pdf from message https://www.accessforums.net/showthread.php?t=73590 gives tblUnits, tblUnitTitles, tblUnitLineage, and tblUnitHeritage. Reading Arvi comments suggest that a tblUnitHonours along with tblHonours is all that is needed. I agree with Arvi and was leaning that way to.

    For information, tblUnitLineage handles the Amalgamations, Splits etc. with tblUnitHeritage handling the associations, perpetuations, etc. (both Recursive to tblUnits) I have not actual worked though this section of the design yet, but I'm starting and is the reason for the question.

    So the 9th Bn CEF, was disbanded by GO 82/18 on 15 Sep 1917. Its honours where formally given in GO 123/29 dated 15 Oct 1929. I was wondering how the logic would handle have new data after the unit in question ended. Guessing from Avri reply this is workable.

    Standing on thin Ice

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Maybe you must have 2 dates in tbUnitHonours or whatever you name it - the date the honour was awarded, and the date of event the honour was bound to.

  9. #9
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    My initial thought was No, Honours have date and location defined with them (British & Canadian), and then Presidential Unit Citation popped into my mind.

    Thanks for the catch

    Click image for larger version. 

Name:	Honours.png 
Views:	11 
Size:	54.3 KB 
ID:	35792

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

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