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

    Repetitive Subcomponent

    Here is the 2nd last problem for a bit. (the last one is how to tie the sources in, but I still thinking on it)

    Military units have “Idea” organization call a TOE (Table of Organization and Equipment) (US term), which can change over time. Most (active) units are close to it, but peace, losses, shortages, manpower limits, and “found” items all change or modify this Idea. In the future I will be including what I have of this data (TOE) but not now (so allow for it if possible).

    At this time, I’m working with the militia units that are of basic two types, City and Rural, and vary in subcomponents’ (companies) from 4 (1910 standard?) to 10 (1800 standard?). This in effect means 4 to 5 structures are in play over time, and they were NOT adopted at the same time. Most of these companies are either labeled alphabetical (1890’s plus) or numbered. Some units (>10%) use a modified scheme to label these units. The city ones are mostly in the one city (some outlaying) and the rural ones at 1 per town. This makes the city one repetitive and the rural ones distinct enough to warrant a separate record.

    I’m not seeing how to normalize this except to treat each company as a separate record.



    Any thoughts?
    Neil

    Click image for larger version. 

Name:	Repetitive Subcomponent.PNG 
Views:	20 
Size:	34.3 KB 
ID:	25159
    Attached Thumbnails Attached Thumbnails Reptetitive Subcombonties.PNG  

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    there are many analogies for this in other industries. it comes down to balancing the practical ease of data creation & use. I like to use the example of Country, State, City, Street. This is a classic hierarchy. Should they be separate tables? Focus on just the City & Street.... yes some street names are repeated in many cities - but does that really mean that Streets must be its own table? ... to what benefit?

    if a city has 100 streets then there is going to be 100 records in a table - is there any benefit if it a junction table of 2 separate tables or just in 1 CityStreet table?

    one must know your practical requirements and go from there.....

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Repetitive Subcomponent

    Hi
    In the matter of streets, NO they are distinct items Bayshore Drive Chicago has no relationship to Bayshore Drive Cottagetown. Now all the address in Chicago use City=Chicago and are related, but another table is only needed if you going link to it and expanded on the entity Chicago (eg Pop, Area). Am I understanding this right or missing something?

    Splitting tblPlace into State & Country tables is the harder way of doing this and will cause me to have grey hairs, correct? I should just put up with the typing and errors that this old man creates and leave this problem alone (for now).

    To the question I was asking, below is a partial table of data from two major sources:
    # Title Parent Location +- Effect Date Note Order #
    1 "A" Squadron Ind CMR Kamloops BC on 1908 04 08
    2 "B" Squadron Ind CMR Vernon BC on 1908 04 08
    3 "A" Squadron Ind BCH Kamloops BC by 1910 04 01 see rec 1
    4 "B" Squadron Ind BCH Vernon BC by 1910 04 01 see rec 2
    5 "C" Squadron Ind BCH Coldstream BC by 1910 04 01
    6 "D" Squadron Ind BCH Lower Nicola BC by 1910 04 01
    7 "E" Squadron Ind BCH Lumby BC on 1911 04 01 unorganized
    8 "F" Squadron Ind BCH Kelowna BC on 1910 04 01 unorganized
    9 "G" Squadron Ind BCH BC by 1911 05 18 unorganized
    10 "H" Squadron Ind BCH BC by 1911 05 18 unorganized
    11 "A" Squadron 30th BCH Lumby BC by 1912 04 01 see rec 7? GO 52/11
    12 "B" Squadron 30th BCH Veron BC by 1912 04 01 see rec 4 GO 52/11
    13 "C" Squadron 30th BCH Armstrong BC by 1912 04 01 see rec 5 GO 52/11
    14 "D" Squadron 30th BCH Kelowna BC by 1912 04 01 see rec 8? GO 52/11
    15 "A" Squadron 31st BCH Kamloops BC by 1912 04 01 see rec 3 GO 52/11
    16 "B" Squadron 31st BCH Salmon Arm BC by 1912 04 01 see rec 9? GO 52/11
    17 "C" Squadron 31st BCH Wallachin BC by 1912 04 01 see rec 10? GO 52/11
    18 "D" Squadron 31st BCH Merritt BC by 1912 04 01 see rec 6 GO 52/11
    19 30th BCH 1st BCH 1911 12 01 Rec 11 - 14 GO 199/11
    20 1st BCH 30th BCH 1912 04 15 Rec 11 - 14 GO 74/12
    21 30th BCH BCMR 1920 03 15 Rec 11 - 14 GO 26/20
    22 BCMR BCD 1929 03 15 Rec 11 - 14 GO 34/29

    Note
    1) Rec 5 moved from Coldstream to Armstrong at some point.
    2) I assume the date of Rec 8 is wrong by a year.
    3) Parent is showing a shorten title
    4) Rec 19 to 22 are the evolving Parent title for Rec 11 to 14.

    Using the attached ER model and using Tables Unit, Unit_x_Title, Title, Action, Unit_x_Family, Unit_x_Place and Place, assuming a full table and not what is shown I think this data will fit in, but is it the best fit?

    Neil
    Click image for larger version. 

Name:	Repetitive Subcomponent.jpg 
Views:	10 
Size:	110.0 KB 
ID:	25185

  4. #4
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    After playing with data, getting my mind around how the data has to imported, I agree that A sqd BCD and A sqd BCH are two separate things. Still feels funny to see both in the same this though
    Neil

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

Similar Threads

  1. Replies: 5
    Last Post: 06-23-2014, 07:32 PM
  2. Mass Import Repetitive Txt Files
    By Todd84 in forum Import/Export Data
    Replies: 4
    Last Post: 01-03-2014, 12:04 AM
  3. Quick repetitive data entry in form
    By scottfrock in forum Forms
    Replies: 1
    Last Post: 11-15-2013, 11:08 AM
  4. Form Issue (repetitive fields)
    By netchie in forum Access
    Replies: 2
    Last Post: 08-15-2011, 02:39 PM
  5. Repetitive Import Problem.
    By jasonbarnes in forum Import/Export Data
    Replies: 5
    Last Post: 02-18-2011, 11:09 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