Results 1 to 9 of 9
  1. #1
    DarthBrute is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    Phoenix, AZ
    Posts
    8

    First Database I'm trying not to give up on. Advice please.

    Ok I first posted here: https://www.accessforums.net/showthr...693#post456693

    So I rebuilt it into two different versions.



    Version 1: https://1drv.ms/u/s!AuWwCQc0BXWsgqhd...7SXpw?e=oSgdgK



    Version 2: https://1drv.ms/u/s!AuWwCQc0BXWsgqhc...wXg5A?e=KNG5Fl


    I read up and have been doing some tests and practice. Try it..why didn't that work..read up, try something else, wash rinse repeat.

    And quite frankly I'm more confused than ever.

    Any Data that may be in the sheets are all false and not actual PII. And the sheets should have no relationships. I tried to keep them no frill and only work with copies for testing so I don't implode the whole thing.

    Version 1: ERIS Points should be tied to the Applicants table. "ERIS Points" also contains 3 other fields (Sourcer, Assessor, Onboarder). Those would be filled in on a form as a lookup combo box (filtered by the Criteria Sourcer/Assessor/Onboarder from the Pillar column under the Recruiters Table. ERIS Points keeps a record of ERIS points for each applicant which also in turn keeps a record of points earned by each of the Sourcers, Assessors, and Onboarders. That in turn also keeps a record of points earned by each office (RSID) and each TAOC.

    Version 2: Same as above except the ERIS Points table has been split into 3 tables: Sourcing/Assessing/Onboarding ERIS

    Which version would you use?
    If neither how would you do it?

    I'm trying to get a better understanding of this and it all sounds so simple in my head. But I can't get SSNs to append from Applicants to the ERIS table I just get empty records with no SSN, my forms disappear, my queries come up empty, the cat is sleeping with the dog, and the kids are fighting.

    I appreciate whatever help/advice you can throw my way.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Color scheme in DB is bizarre and irritating to look at. Count me out.

  3. #3
    DarthBrute is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    Phoenix, AZ
    Posts
    8
    Quote Originally Posted by davegri View Post
    Color scheme in DB is bizarre and irritating to look at. Count me out.
    Color scheme fixed it was just done for the forms. Also, you could have posted nothing.

  4. #4
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    I had a look at your db and I'm afraid I can't tell what you are trying to do.

    you have four tables, only two of which have data in them. then you have two forms but no queries.

    my advice, if you want someone with zero familiarity with your environment to figure out what you are trying to do, so that they can help you...

    1. put a little data in each table. then complete the Relationships view so that all your tables are included.
    2. write some queries showing the kinds of questions you want to ask and what an answer might look like.
    3. only then would I consider writing forms. for beginners, forms are hard. concentrate on doing the heavy lifting in queries and then let the forms display stuff you have already figured out.


    good luck with you project,


    Cottonshirt

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First an FYI: there are a LOT of people that will not download files from an unknown site. You will get more views if you post (attach) your files in this forum.

    Next, a word of warning. Every one here is an unpaid volunteer. davegri was nice enough to look at your databases and gave his opinion "Color scheme in DB is bizarre and irritating to look at. Count me out."
    You responded with "Color scheme fixed it was just done for the forms. Also, you could have posted nothing." If you have a thin skin, you will have a tough time here.
    You could have responded with "Color scheme fixed it was just done for the forms. Thanks you for taking the time to have a look". Or "Oops, Color scheme fixed it was just done for the forms."
    If you are perceived to be rude/ungrateful, you could be ignored or blocked..... then you get no help. True, you could go to a different forum, but know that many people here are also on many others forums. Word get around........

    Enough of that.

    I hope you read the links I posted in your previous thread: https://www.accessforums.net/showthr...693#post456693
    Here is more info:

    Some suggestions for naming objects:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


    See Table and PK design tips


    Have you drawn out your db structure out using pencil and paper, cardboard, whiteboard, a window with dry marker, ...???

    "Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


    This is important:
    What is Normalization? (I to V)
    =======================
    What Is Normalization, Part I: Why Normalization?



    1) Maybe you could explain WHAT you are trying to do.
    So, after some searching, I found out that MEPS means Military Entrance Processing Station and I think ERIS means Emergency Response Information System (Nuclear Power)
    I did a lot of reading about An Overview of the Military Entrance Processing Station


    I took your 2nd dB and modified it. I removed the look up FIELDS, removed spaces in names or replaced spaces with the underscore and renamed the PK fields. (ID is a poor name)
    I removed the SSN field from tables Assessing_ERIS and OnboardingERIS and added a linking (FK) field to each table. I set a one to many relationship between tables tblApplicants and the two tables Assessing_ERIS and OnboardingERIS.

    The form had the"Data Entry" property set to YES - this makes the form show a new record. After the data (record) is saved it seems to disappear. But remember, with the data entry property set to yes, the form will only show a new (blank) record.


    2) What is the purpose of table Assessing_ERIS?

    3) What is the purpose of table OnboardingERIS?


    I only use saved queries or SQL strings for forms/reports. So I created a saved query based on the table tblApplicants and set that to be the record source for the form frmApplicants.
    Using a query makes it real easy to limit the number of fields used and/or the number of records displayed (filtering).


    It looks like frmApplicants should have two sub forms - a sub form for Assessing_ERIS and a sub form for OnboardingERIS.


    Awaiting your comments.....
    Attached Files Attached Files

  6. #6
    DarthBrute is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    Phoenix, AZ
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    First an FYI: there are a LOT of people that will not download files from an unknown site. You will get more views if you post (attach) your files in this forum.

    Next, a word of warning. Every one here is an unpaid volunteer. davegri was nice enough to look at your databases and gave his opinion "Color scheme in DB is bizarre and irritating to look at. Count me out."
    You responded with "Color scheme fixed it was just done for the forms. Also, you could have posted nothing." If you have a thin skin, you will have a tough time here.
    You could have responded with "Color scheme fixed it was just done for the forms. Thanks you for taking the time to have a look". Or "Oops, Color scheme fixed it was just done for the forms."
    If you are perceived to be rude/ungrateful, you could be ignored or blocked..... then you get no help. True, you could go to a different forum, but know that many people here are also on many others forums. Word get around........
    I'm a 40 yr old man and have been active in the Navy for the last 17 yrs. I have thick skin. I've worked the customer service end of IT for most of my naval career. That user could have responded with "Color scheme in DB is bizarre and irritating to look at. Would you mind changing it up?". Courtesy goes both ways and both parties are the better for it.


    Quote Originally Posted by ssanfu View Post
    See Table and PK design tips


    Have you drawn out your db structure out using pencil and paper, cardboard, whiteboard, a window with dry marker, ...???

    "Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.
    Well, I've never done a database before. I've tried and always end up programming what I need in excel. I have basic concepts drawn out on how data is connected. I am reading on how to do this and doing trial and error experiments as I learn. I'm also looking at the "Northwind" template for practical visualization on how data is linked. My issue is that I don't know how to use Access. I learned Excel by trial and error and experimenting with code and touching on macros. Access is completely different and I am trying to get my mind away from that line of thinking Excel. I am familiar with how data should connect but how thats done here is a little different. I am NOT asking someone to build it for me. Just simply some guidance.

    Quote Originally Posted by ssanfu View Post
    1) Maybe you could explain WHAT you are trying to do.
    So, after some searching, I found out that MEPS means Military Entrance Processing Station and I think ERIS means Emergency Response Information System (Nuclear Power)
    I did a lot of reading about An Overview of the Military Entrance Processing Station
    ERIS actually stands for Enlisted Recruiter Information Statistics. It is a way for recruiters in the field and the command to rank recruiters based on performance and types of contracts that they enlist throughout the year. Recruiters are working out of the job fields and require a way to properly rank them for evaluation purposes. Example a candidate that qualifies and accepts the Machinist Mate job (works on the engineering level of the ship) would be given less ERIS points than a candidate that qualifies and accepts the job Machinist Mate Nuclear.

    Quote Originally Posted by ssanfu View Post
    I took your 2nd dB and modified it. I removed the look up FIELDS, removed spaces in names or replaced spaces with the underscore and renamed the PK fields. (ID is a poor name)
    I removed the SSN field from tables Assessing_ERIS and OnboardingERIS and added a linking (FK) field to each table. I set a one to many relationship between tables tblApplicants and the two tables Assessing_ERIS and OnboardingERIS.

    The form had the"Data Entry" property set to YES - this makes the form show a new record. After the data (record) is saved it seems to disappear. But remember, with the data entry property set to yes, the form will only show a new (blank) record.
    That was my fault. I had created a query that would look up a recruiter from the "Recruiters" table and filter out any that did not have "Assessing" in the Pillar column. My form had disappeared and was trying out troubleshooting steps on why. It was the filter query and I still don't understand why. Those kind of things are part of my problem. I had also ran a query to select all the SSNs from the "Applicants" table. It worked. Then I changed it to an append query to append that data to the SSN column under the "Assessing" column. It ran, created 33 records, but the SSN column was completely blank. Even when I run a query described from the MS website or somewhere else it always seem to work in a way I do not expect and that is my main confusion and frustration.

    Quote Originally Posted by ssanfu View Post

    1) Maybe you could explain WHAT you are trying to do.

    2) What is the purpose of table Assessing_ERIS?

    3) What is the purpose of table OnboardingERIS?
    The purpose of the Sourcing/Assessing/Onboarding tables is that each step involves a different personnel along those 3 steps in the recruitment process. Examples:
    Sourcing: THink of it as that email or phone call you get from a car dealership. That person is ensuring leads are followed up on, people on the website asking questions are answered, etc.
    Assessing: This is the sales guy. He sells you the car.
    Onboarding: Essentially the manager, he ensures you like the car and keep it.

    As the recruit is found qualification processes begin to ensure they CAN join the Navy. As they move through that process points are given on certain things: Females are need of the Military as they are least likely (comparatively) to seek military service so points are awarded for that. Each of those 3 "Pillars" have a hand in that process and are awarded points. In turn each of the 3 personnel that the recruit is assigned get those points which are totaled up and accumulate with each recruit they attain. Which also in turn gives those points to the Station (identified by the "RSID" column) and then to the region (identified by the "TAOC" column) they work in.

    An excel one I wrote up: https://1drv.ms/x/s!AuWwCQc0BXWsgqhp...mnSqw?e=Hi4aX1

    However, we are restructuring and this excel model will no longer work. Same idea, different format. Again all fake names in the spreadsheet. I mixed up first and last names.

    Quote Originally Posted by ssanfu View Post

    I only use saved queries or SQL strings for forms/reports. So I created a saved query based on the table tblApplicants and set that to be the record source for the form frmApplicants.
    Using a query makes it real easy to limit the number of fields used and/or the number of records displayed (filtering).


    It looks like frmApplicants should have two sub forms - a sub form for Assessing_ERIS and a sub form for OnboardingERIS.


    Awaiting your comments.....
    I will look over this tomorrow and get back to you. Thank you again for you help.

  7. #7
    DarthBrute is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    Phoenix, AZ
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    I only use saved queries or SQL strings for forms/reports. So I created a saved query based on the table tblApplicants and set that to be the record source for the form frmApplicants.
    Using a query makes it real easy to limit the number of fields used and/or the number of records displayed (filtering).

    It looks like frmApplicants should have two sub forms - a sub form for Assessing_ERIS and a sub form for OnboardingERIS.


    Awaiting your comments.....
    OPk so basically the query becomes the entry for the form? I'm not sure I understand. I had to edit the SQL just a tad as it was missing "tbl" from in front of Applicants on the FROM line. Once I did that I was getting pop-ups for parameters for each field in the query. So I guess it is working as designed.

    Code:
    SELECT Applicants.ApplicantsID_PK AS Expr1, Applicants.SSN AS Expr2, Applicants.LastName AS Expr3, Applicants.FirstName AS Expr4, Applicants.Gender AS Expr5, Applicants.Ed_Code AS Expr6, Applicants.AFQT AS Expr7, Applicants.Status AS Expr8, Applicants.[Q_I Date] AS Expr9, Applicants.MEPS_Date AS Expr10, Applicants.[MEPS _Count] AS Expr11, Applicants.DEP_Date AS Expr12, Applicants.Ship_Date AS Expr13, Applicants.Program AS Expr14, Applicants.Reclass_Date AS Expr15, Applicants.Attrite_Date AS Expr16
    FROM tblApplicants;

    In respect to continuing the relationship chain would I connect that data like this:
    Click image for larger version. 

Name:	relationships.PNG 
Views:	42 
Size:	25.6 KB 
ID:	42179


    I am relooking at it from another angle for normalization. Your articles helped and I have read a bit more. So I am taking another look to see if there is a better way. Thanks.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi I would do it slightly different and base the Form on the Table. This way you can use a Search Combobox to select an Applicant.
    Attached Files Attached Files

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by DarthBrute View Post
    I'm a 40 yr old man and have been active in the Navy for the last 17 yrs. I have thick skin. I've worked the customer service end of IT for most of my naval career. That user could have responded with "Color scheme in DB is bizarre and irritating to look at. Would you mind changing it up?". Courtesy goes both ways and both parties are the better for it.
    First off - Thank you for your service. Very much appreciated.

    As to davegri's response. I've been reading his posts for a while and it seems to me, from his posts, that he is a good guy. The comment could have been more aimed at me and other responders, letting us know that the dB is "bizarre and irritating to look at" (for him) and he wasn't going to spend time on it. But you fixed/changed the dB so all is good now.


    Hmmmmmm, 40? I wish I was back at 40! Sucks to get old. Knees are giving out. (I'll be 67 this year)


    About your image with the red lines. That is not entirely correct. (I'll try and keep this short.) Without trying to start a war, there are two schools of thought for PK fields - Natural keys or Surrogate keys.
    The DODID (a natural key) *might* be a candidate for a PK field, IF a persons DODID will/would never change.
    I subscribe to the school of thought for all tables to use Surrogate keys, ie an autonumber type field. For more see Choosing a Primary Key: Natural or Surrogate?

    Actually, the Recruiter table doesn't really matter in the relationship window because the only thing the table will be used for is for 3 fields - as the ROW source of combo boxes on a form. (unlike the PK field for the Applicants table).
    Click image for larger version. 

Name:	Presentation1.png 
Views:	30 
Size:	130.3 KB 
ID:	42190

    I made more changes (and hopefully less errors - it is a work in progress), adding a few more queries and forms.


    Just remember, tables store data. You can have 1 or more tables in a query and you have a virtual table.
    Forms (and reports) are ways to VIEW the data in tables/virtual tables. Forms do not store data.


    Take a look at this version of the dB........
    Attached Files Attached Files

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

Similar Threads

  1. Need Advice re: Building Database
    By musicmanbob in forum Database Design
    Replies: 30
    Last Post: 02-20-2017, 01:35 PM
  2. Advice on designing my 1st database
    By cmb in forum Database Design
    Replies: 2
    Last Post: 09-09-2014, 03:42 PM
  3. Replies: 4
    Last Post: 04-02-2014, 08:51 AM
  4. Replies: 3
    Last Post: 06-22-2011, 01:59 PM
  5. Database advice
    By PRINCE SWAGG in forum Access
    Replies: 29
    Last Post: 06-21-2011, 03:56 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