UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mentor And Help, Access 2016    
 
   
dlafko
post Apr 3 2019, 07:18 AM
Post#1



Posts: 217
Joined: 7-May 14



Hello,
I am building a database (or hope to ), Database name is Posting Entering. I can not pay for this to be done it has to be done by me lol. I struggle with relationships. This database will hold thousands of records over time and most likely will need to be archived due to the amount of data at some point.
I have attached the start of my database, which has tables and forms. The forms are my guide to the data I needed and thoughts on how to put it together. I built but because of trying to make the tables relationships it keeps yelling at me LOL.

We are an audit unit and are collecting all the data off job postings to ensure compliance. We will then be using this data to determine what offices we want to audit the hiring practices for.
There are 2 types of hires and postings.
1. External jobs which are jobs anyone can apply for and do not have a lot of data attached to them.
2. Internal postings which are available to people who currently work for us. These have a lot more data and options for how someone can be hired.

I want to capture this data in 2 forms; Internal Postings and State_Local_External_Posting and have the forms share some static data that never changes to eliminate spelling errors and such. Also am looking to do checkboxes for common mistakes so when we run a report it will be easier to pull them out. My ultimate goal is to have as little actual data entry (hand typing) as possible.

The end result should be a nice user interface that allows 4-5 people to enter data at any time simultaneously and that the manager can pull reports and queries that can give us the information we need.

Right now we are doing this on two separate excel sheets and there are probably 3,000 records between them. I would ultimately like to pull these records back into the database but since I have various tables not sure how I can achieve that?

Below I will post the table names and what they are to do and then post the database. In another post I was asked to better explain what the goals of the database are and so I decided to repost. I was also told that some of the tables they did not understand but offered not suggestions so I am going to give details here.

These 3 tables that have data already in them. These are areas where people will use combo boxes to get the info when filling out the form. What I call static data as in it will not change.
1. Agency-- These are all the agencies that can post jobs
2. EPR Rating-- These are overall ratings an employee has for an Employee Performance Review
3. Job Code Title -- Each job title has a specific code with it. Example and Human Resource Analyst 1 job code is 0502A The title will be behind it I have them both stored in one table and again they don't change. Ideally in the form I had the one combo box pull the job code number and then automatically fill in a sperate field for the job Title. The code will be less likely to have mistakes so this is why I want to push the user to use the code and not the title, but we must display the title to ensure accuracy as another check of the posting.

Now I have several tables that will store data ( I hope if the method is right.)
1. Next Lower Class, When offering positions for promotion the agency can list the job titles in the lower pay level for which they will consider for promotion. Each posting could have upwards of 20 next lower class jobs so I thought it would make sense to store this in its own table
2. Position number, Each job that a human sits in has a unique position number sometimes 1 posting can be for multiple position numbers. Again thinking of the amount of data thought storing it in its own table made sense
3. Hiring Preference, for External postings they can identify specific preferences such as, county they live in, languages spoken and so on.
4. Issues, This is a check box table where we will store all the typical issues we see with job postings
5. Recruitment- Agencies are allowed to use different methods to recruit candidates and they have to place them on the posting. Again large amount of data

My goal with these separate tables is so that I don't have one huge table holding all of the data but smaller tables holding specific data that can be tied together thru a relationship (if I understand it right) to get quicker responses when running quires and reports

Finally I made a table called Main which holds THE PKID's of these other tables (not sure that's right ) as well as, holding some miscellaneous data that I could not really figure any better way to do it.

All your help will be greatly appreciated I have been banging my head against this for a week because I cant figure out how to get the data from the form into each of these tables right.

I have attached the Database and the entering excel sheet just so you can see some records. I am able to email call or text anything really to get some help thru the build. There are some things I know how to do and others that drive me nuts.
Originally I thought it would make sense to have subforms in the main for but I know there has to be a way to code that I don't have to do that.

Attached File(s)
Attached File  Log_of_Internal_Job_Postings_as_of_3_20.zip ( 201.83K )Number of downloads: 1
Attached File  Posting_Entering.zip ( 89.16K )Number of downloads: 4
 
Go to the top of the page
 
mike60smart
post Apr 3 2019, 07:58 AM
Post#2


UtterAccess VIP
Posts: 13,207
Joined: 6-June 05
From: Dunbar,Scotland


Hi
Not at my Pc at the moment but will take a look soonest.


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
BruceM
post Apr 3 2019, 08:17 AM
Post#3


UtterAccess VIP
Posts: 7,888
Joined: 24-May 10
From: Downeast Maine


There is a lot of reference material in the UtterAccess Newcomer's Reading List. Crystal's tutorial is as good a starting point as any, but there is a lot of good information.

I don't have time to look at your posting or the attached database in detail, but I can tell you that the three tables with numbered fields is not the way to go. You are "committing spreadsheet". smile.gif

Briefly, I can say that you seem to be on the right track in that you have related tables, but with tblHiringPreferenceE (for instance) you most likely need just two or three fields:

PKIDHiring (autonumber PK)
HiringPreference (describe the hiring preference)
Rank (manually rank the records, if desired)

If HiringPreference is to be selected from a list, another table is needed to hold that list, and you would most likely stored the autonumber PK from the selected record, not the text value of the hiring preference.

The tutorials explain this concept, so I won't try to provide details here.
Go to the top of the page
 
dlafko
post Apr 3 2019, 08:35 AM
Post#4



Posts: 217
Joined: 7-May 14



BruceM.. The Hiring Preferences there could be a wide variety of them for each posting that's why I have so many fields. Thanks for the info.
Go to the top of the page
 
dlafko
post Apr 3 2019, 08:35 AM
Post#5



Posts: 217
Joined: 7-May 14



mike60smart Ok thanks I did email you too so not sure if you got those?
Go to the top of the page
 
mike60smart
post Apr 3 2019, 09:30 AM
Post#6


UtterAccess VIP
Posts: 13,207
Joined: 6-June 05
From: Dunbar,Scotland


Hi
Bruce is on the right track with hiring preferences but I believe you probably need Cascading Combobox's.
This means Select a Posting with 1st Combobox and only related data would displat in 2nd Combobox

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
dlafko
post Apr 3 2019, 09:53 AM
Post#7



Posts: 217
Joined: 7-May 14



OK. I think I was doing that in a different way but the issue with the hiring preferences is that you might have 10 different preferences that need to be captured per posting and these will be manually typed in by the person entering the data. They wont come from a static table.

Not sure I am following the issue you guys are seeing smile.gif with that table there are 2 other tables that I have handling data the same way. The Next Lower class and the Position numbers.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 05:16 PM