Results 1 to 3 of 3
  1. #1
    350zflyby is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    1

    Army School Databse Design

    Hi,



    I am trying to design a database to track Soldiers' school request packets. Attached is what I have designed so far.

    The general idea is that I have hundreds of Soldiers submitting school request packets to me, so I first created a table to track Soldier personal info (SoldierInfo Table). It has a unique key for each Soldier. Soldiers come and go so I'll always need to add new Soldiers as they arrive in the unit or submit a school request for the first time.

    The Soldier submits a school request packet (hard copy) that request a certain school and class date, usually a primary and alternate class date. Since I need to track the status of the packet, I created another table for the packet with a unique key for each packet, using SoldierInfo key as a foreign key with One to Many relationship. I also put the SchoolInfo Key as a foreign key in the PacketInfo Table with a Many to One relationship.

    I went on to build a SchoolInfo table to capture each individual school. Each school has a unique key. This key is a foreign key in the PacketInfo Table and a foreign key in the ClassInfo Table.

    Lastly, each school is run several times a year so I build a table "ClassInfo," to show each class date for each session a school has throughout the year.

    As tables and to capture the data, this design works fine. However I am having some serious issues when I try to imagine a new Soldier submits a new school request. Basic forms allow me to input the new soldier info or select a soldier I already input. The subform allows me to fill in new packet info as well. However, I am unable to view more sub-forms to select the school and class date.

    My design may be flawed, which is why I am posting here. Essentially, I am trying to create an input form that allows me to select a soldier I already have info on or create a new one. Then I need to create a new packet on that soldier or update a previous one. Next, I need to select which school the packet is for or input a new school, if it's a school we don't normally deal with and I was not already tracking for some reason. Lastly, when I select a school, I need to select the class # or class dates, and it should only have options for class dates that are tied to the specific school.

    Eventually, I want this database to feed into our SharePoint Calendar and populate it with all of the schools and dates, but I need to work through these basic problems first.

    I'm still watching videos on Lynda.com and trying to learn as much as I can. Please email me if the file didn't upload correctly and I can send it to you. 350zflyby@gmail.com I am even open to exchanging phone numbers so I can get this worked out. Willing to pay a little $$ but I can't afford to much...



    SchoolRequest.zip

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    It sounds like ONE class has MANY times. You may consider creating a table for your classes, then a different table to store the class times. Then, instead of linking a packet directly to a school you would link them to a class time. How are you recording the alternative class time?

    As far as user interface goes there are a number of ways to approach it. What's your comfort level with vba? My first instinct is to make a series of unbound cascading combo/list boxes. Meaning your user would first select a school in listbox 1, that would in turn filter listbox 2 to classes that belong to that school. When the user selects a class that would filter listbox 3 and 4 to class times (primary, alternate) that belong to that class. Once the user selects the class times or hits a submit button then your packet info record is created...

    You could also create a series of forms that act like a typical wizard interface you might see in other apps. First form prompts user for a School and passes it to the next form, the next form prompts for a class and so on.

    Another option would to be just to create a custom search form that simply has a flat listing of all the classes and class times and the user can type in filters to narrow down and select one class.

    Another and probably most straight forward to implement might be to just have a soldier main form with a packet subform that simply has a combo box with a flat listing of all the classes. Though this might be the most pain to use.

    Lots of ways to skin a UI cat.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In table "ClassInfo" there is a field "ClassFY" that is a multi-Value field.
    One of the rules I live by: Do not use Look up FIELDS, multi-Value fields (MFV) or Calculated fields in tables.
    This "feature" was not fully implemented by MS - you will end up banging your head against the wall.

    I see you have a lot of look up FIELDs in your tables. Not a good idea..... for reasons see
    The Evils of Lookup Fields in Tables
    http://www.theaccessweb.com/lookupfields.htm


    There are no experienced Access programmers that I know of that use MFVs and only a handful would even think about using look up FIELDs.

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

Similar Threads

  1. Need Grocery List Databse Design form help
    By Zipster1967 in forum Database Design
    Replies: 0
    Last Post: 05-18-2015, 04:32 PM
  2. Are you a army of one?
    By Paul H in forum General Chat
    Replies: 6
    Last Post: 12-06-2012, 02:12 PM
  3. I am creating an army HR database
    By johnwatkins35 in forum Database Design
    Replies: 1
    Last Post: 05-16-2012, 06:39 AM
  4. Databse design for a teacher application
    By James89 in forum Database Design
    Replies: 6
    Last Post: 04-18-2012, 08:54 AM
  5. Database Design for a School
    By FallingToaster in forum Database Design
    Replies: 2
    Last Post: 08-12-2009, 10:47 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