Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    thekiwi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11

    Help with table design / data entry

    This is my first post, and I've only been using Access for a short while (self-taught and badly at that). I use it for personal interest and I am quite sure I break many Access cardinal sins in my existing DB and table designs. That said, I would like to ask how I should best setup a table to enter the sort of information contained in the attached image. This is an RAF aircraft movement card.

    There are hundreds of these cards (often 2-sided) and each card may have up to 20 rows of information. I would normally place this information in a single table, but in this case I have up to five columns (Unit, Station, Date, Authority, etc) that need data entry.

    What is the best practice for setting up a table to enter this information? In my simple view I would have the following fields:
    AircraftType
    Mark
    Serial
    Contractor
    ContractNo
    Engine
    AirframeNo
    CatCause_1
    Unit_1
    InDate_1
    OutDate_1
    Authority_1
    GpAllot_1
    CatCause_2
    Unit_2
    InDate_2
    OutDate_2
    Authority_2
    GpAllot_2
    ...
    CatCause_20
    Unit_20
    InDate_20
    OutDate_20
    Authority_20
    GpAllot_20


    This doesn't seem the most efficient way of setting up the table as I'd have >100 fields.

    I apologise if I should have found this information somewhere in the forums, but being self-taught it is hard to know where to start looking or what to look for.

    Thanks for any thoughts - a simple nudge in toward a thread or post would help immensely.


    JB

    Click image for larger version. 

Name:	Valetta.jpg 
Views:	44 
Size:	72.8 KB 
ID:	49672

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Can you upload a copy of your Db?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    thekiwi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    Hi Mike

    *sheepishly* Ideally not, because it will almost certainly result in us spiralling into a world of "what was this guy thinking?!"

    All I'd like to understand is how would I best enter this data into a form/table that could then be displayed again when I pull it up via the serial number (RAF Number). The data in the table would be static and not rely on anything else in the database.

    Does that help elucidate what I'd like in any way?

    Thanks!


    JB

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You don't break sins, you commit them, and that you have done. Your tables are designed like spreadsheets - not good.

    I'm going to suggest you read all of the below given that you're a self declared novice. Start with normalization and understand it. When you think you understand it, try creating tables and relationships on paper. Go through the mental exercise if you can, and see if it looks like you'll be able to relate fields from one table to another. Then create those tables and relationships in Access and post a pic of that and solicit feedback. Those who try to help will need to know details on your process.

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

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

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

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html

    About Auto Numbers
    - 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
    About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by thekiwi View Post
    Hi Mike

    *sheepishly* Ideally not, because it will almost certainly result in us spiralling into a world of "what was this guy thinking?!"

    All I'd like to understand is how would I best enter this data into a form/table that could then be displayed again when I pull it up via the serial number (RAF Number). The data in the table would be static and not rely on anything else in the database.

    Does that help elucidate what I'd like in any way?

    Thanks!


    JB
    We already know what you are thinking......spreadsheet

    Look at the links Micron posted and come back with any questions. Also look on YouTube for normalization examples.
    You need a good foundation to start with, just like for a house.

    https://www.youtube.com/results?sear...+normalization

    https://www.youtube.com/results?sear...atabase+design
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    thekiwi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    Thanks guys! Truly appreciate this. Glad it was obvious where I'm coming from as well.

    You are, of course, quite right; try as I do, I'm still firmly set in spreadsheets. Not by choice, simply that it was a natural progression to Access after I was finding Excel inadequate.

    I will start looking through the documentation and seek feedback as I go.

    Thanks again for the prompt responses.

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    From your table fields listed the following are known as Repeating Groups:-

    CatCause_1
    Unit_1
    InDate_1
    OutDate_1
    Authority_1
    GpAllot_1
    CatCause_2
    Unit_2
    InDate_2
    OutDate_2
    Authority_2
    GpAllot_2

    ... Through to 20

    These fields should be Records in a separate table.

    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi JB

    This will give you a starting point.
    Attached Thumbnails Attached Thumbnails Relationships.png  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    thekiwi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    This is a great help thanks Mike

    I was somewhat familiar with data normalization, though I have not been following it anywhere near strictly enough. The example really helps show where I need to break-out all the additional tables and how the relationships work.

    The repeating group in tblAircraftMovements is something I need to get my head around next. I can see how it will work, but I need to put it into practice.

    Thank you so much for the help.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi JB

    This is an example Form for entering data which I am sure can be improved.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If one plane can be worked on by many contractors I don't think that's right as it would mean all plane fields need to be repeating records for each contractor. If many contractors can work on a plane, there needs to be a junction table for planes/contractors.

    @thekiwi - it's vital for the db designer to understand the process and how things interact as well as understand normalization principles. If you're seeking design assistance, a complete understanding should bring more accurate results.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Micron has hit the head on the nail.

    We need a full explanation of the process of recording an Aircrafts movements.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    thekiwi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11

    Question

    Oh boy. This might be a can of worms that you'll regret asking about...

    As mentioned, this is purely personal interest and there is no end goal other than me eventually planning on sticking it online to be publicly accessible. Nothing more than that.

    The movement card in the original post is just one part of a much larger db I've been collating information for. Until now, I've been populating tables using forms with a minimum of relationships built in because it was something I figured I'd address when I was forced to. There are a number of my tables that could benefit greatly from a lot more normalization, but I just cracked on with data entry thinking I could come back to the design at a later stage. Not good, I recognize, but like I say, there was no end goal here so a clear vision of what's needed doesn't really exist.

    At the moment, I'm struggling to know which direction I should go; your time is precious and I'd prefer not to waste it. Explaining more will move away from the original post. I'd be delighted to, but only to the extent that you retain the will to live...

    So, erm, would you like to know more or should we retain focus on the moment cards?

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    We would prefer the whole story as it would help you in the long run.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    thekiwi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    OK, thanks Mike. I'm inclined to agree but nervous about the resulting feedback.

    I started off planning to create a db with intentions to operate similar to the Studiegroep Luchtoorlog. But my interests are a little broader in that I have been compiling more than the aircraft loss. At the moment I have been adding the following data:

    1. Pilot details
    2. AC loss
    3. AC movement (Form 78 - see original post)
    4. AC accidents (Form 1180)
    5. Combat reports (Form 1151)
    6. Operational Record Book (Squadron diary) entries
    7. Images


    As you have already surmised from previous posts, a lot of this data will have one to many and many to many relationships. At the moment all the data has essentially been entered into tables via an associated Form. I haven't strung any of the db together via relationships as I still hadn't worked out what was going to be included and how I'd actually go about it.

    For an individual aircraft, there are many relationships which are tied via the serial number (primary key as it is unique). For any individual aircraft the following would be true:

    • multiple pilots would have flown it
    • multiple accidents (Form 1180) may exist for it
    • only one loss record
    • only one AC movement card (Form 78)
    • multiple combat reports
    • multiple squadron diary entries
    • none, one or multiple images of the AC may exist


    Similarly, for a pilot, the following relationships may exist:

    • flew multiple AC
    • none, one or multiple Form 1180 AC accident forms
    • multiple combat reports
    • multiple squadron diary entries
    • none, one or multiple images of the pilot may exist


    At the moment, my pilot data entry form looks like the following:
    Click image for larger version. 

Name:	frmPilot.jpg 
Views:	35 
Size:	221.9 KB 
ID:	49676

    You'll note the PilotID is the primary key. All the combo boxes are separate tables. The LinkText is ideal as a Repeating Group (now I am aware of it!). The F1180-ID is manually entered as I haven't tied any of the db together with the appropriate relationships and subsequent queries. This is partly because I haven't spent time trying to work out how.

    For the AC accident form (F1180):
    Click image for larger version. 

Name:	frmF1180.jpg 
Views:	33 
Size:	129.5 KB 
ID:	49677

    The PilotID and AC serial tie this form and others together. At least, it should if I had built the relationships...

    Also attached is an example of the Combat Report (Form 1151):
    Click image for larger version. 

Name:	frmF1151.jpg 
Views:	33 
Size:	131.0 KB 
ID:	49678
    Same again - tied by serial number and PilotID.

    The vast majority of my tables are far from complete (this is a long term project), and hence the reason why I hadn't invested much time on the db design vs slogging away at simple data entry. Hopefully this might start to give you an idea of what I am trying to do at a project scale, if not for the detail required in stringing it together to behave as a relational db.

    I expect lots of slings and arrows but at the same time would welcome feedback that might help me moving forward.

    Thanks for your input!


    JB

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database design: data entry
    By Amen in forum Access
    Replies: 7
    Last Post: 09-30-2013, 03:03 PM
  2. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  3. Replies: 1
    Last Post: 05-13-2013, 10:55 PM
  4. Help with database design Many-to-Many and data entry
    By fftempest in forum Database Design
    Replies: 2
    Last Post: 03-19-2011, 08:44 AM
  5. Database Design / Data Entry
    By benjammin' in forum Access
    Replies: 1
    Last Post: 12-30-2010, 09:35 PM

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