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 Setting Up Tables For Data Storage., Access 2016    
post Apr 1 2019, 06:42 AM

Posts: 217
Joined: 7-May 14

Ok so I am not new to access but still a novice with code and complex databases. I am trying to construct a database using techniques learned over time to build a more complex database that will have 4-5 people inputting data on a daily basis. I am trying to build it with relationships (not sure its needed) I have a multitude of tables set up to keep sections of data since each piece will have a lot of data with it. I set up relationship and I made a main table so the PK ID's could be stored for report running (Not sure this is right). I have to use access because the employer will not spend the money to purchase a program and I have been tasked with its creation.

I am hoping someone out here would be willing to look at what I have constructed so far and guide me along. I have forms built because my brain works backwards, so I am having trouble with some of the forms storing the data in their respective tables. I was detailed when I built my tables and put descriptions in each item.

I can post a lot of specific detail here or if someone is willing to help can send you more specifics individually. IDK what to do at this point I am stuck because I know that this database might hold 10,000+records at some point so trying to build it with out just one giant table. I also know I need to split the database later on as well when It is all constructed.

Anyone willing to help with this piece of looking at my tables and the relationships and tell me how bad I messed up LOL

I would really appreciate it. I have a sharable one drive as well.

Go to the top of the page
post Apr 1 2019, 08:39 AM

UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA

Relationships are to an MS Access database application what arteries and veins are to your heart, lungs and brains. Can't get blood from one place to the other without that pathway and pretty soon it's all over but the last farewell. So, yes, relationships are quite important. wink.gif

With regard to cost. Take your hourly rate (and for comparison I'm going to use a sort of basic Access developer rate of $100/hour --US). How many hours will you spend--in between moments stolen from the task to do your regular job, of course--building this tool. Let's say 40 hours, or approximately one work week using the old school standard. My math makes the cost of a custom solution around $4,000 on that basis. Plus testing, bug fixes, modifications, etc. Call it $5,000 all in. Is that more or less than the cost of the off-the-shelf alternative the boss won't buy. Since I don't know your hourly rate, that may be too high or too low, and it may take you two or three times longer to get it right given your starting position here. And I'm well aware of my tendency to be overly optimistic and underestimate how long good results can take, so maybe double that again.

That's not the only consideration, of course, but one good way to ground the discussion. I recently enjoyed a presentation by Armen Stein, of J Street Tech, in which he explained how his company helps his clients make the decision to buy vs build. There are pros and cons to both and I'm hoping he makes his slide deck available, although I strongly suspect he'll keep it for other events like Access Day. or the upcoming Access Conference in Vienna.

Anyway, back on topic. Once you're totally committed to the "build" side, the approach you're taking now is a good way to go.

Let's start, as we ALWAYS should, with the data that needs to be stored. Then the tables to store it, and then you can worry about forms. Forms may be a good way to identify what data points are going to be of interest (hmm. We need a way to capture the date we started the new sales campaign. That's a field in "SOME" table yet to be designed.) But without a solid foundation, the forms are going to tip over in the first high wind.

Put your proposed tables -- with any sample data you may be able to generate -- in new accdb. Compact and Repair it. Compress it into a ZIP file. Upload it here for review.

This post has been edited by GroverParkGeorge: Apr 1 2019, 09:25 AM

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Apr 1 2019, 12:55 PM

Posts: 217
Joined: 7-May 14

Here is the zip file. Some tables store data to be used in Combo boxes others are actual storage tables. so at the end of the table the D is for stored data and the E is for entry.
Attached File(s)
Attached File  Posting_Entering.zip ( 89.16K )Number of downloads: 7
Go to the top of the page
post Apr 2 2019, 01:20 PM

Posts: 217
Joined: 7-May 14

Saddly this is what I am stuck with. They want us to build it because right know we have some down time. I am really struggling with the correct direction so any help with the tables would be great. I have tried to make relationships and these are the Baine of my existence I am never able to make them with out help and not sure why. I feel like I am doing them as we are suppose to.

I can give a lot of info if you want to here or I can send email or what ever is easiest just let me know.
Go to the top of the page
Dan Dungan
post Apr 2 2019, 01:57 PM

Posts: 316
Joined: 20-July 10
From: chatsworth, ca

I've looked at your table structure, and I'm having difficulty understanding your model.

At least two tables seem to have design issues: TBLHiringPreferenceE and TBLNextLowerClassE.

I'm wondering what is the purpose of this database?

It might help if you write down each entity and its attributes. For example what is the entity TBLMainTableE?

Also, it may be useful to write down the questions you want the database to answer.
This post has been edited by Dan Dungan: Apr 2 2019, 02:00 PM

Go to the top of the page
post Apr 2 2019, 02:04 PM

Posts: 217
Joined: 7-May 14

The purpose of the database is to collect information from job postings and determine what issues they may have for audits that will be done later.

TBLHiringPreferenceE and TBLNextLowerClassE The reason I have these two tables is that they each hold different data and could be a lot of data at that. On another database I built I was told that when you have one cateogory that will hold a lot of data you should have it in separate tables.

Each main record may hold up to 24 of these two.
TBLHiringPreferenceE -- will be used to hold locations, or other items the agency needs to higher. Example would be Berks, Schuylkill, and Dauphin counties, People who speak English, Spanish, French.
TBLNextLowerClassE-- These are all the lower classes of postions that the hiring agency will consider. Example I am an Human Resource Analyst 2 and when filling my position they will consider Human Resource Analyst 1's Some job titles offer up to 24 of these type jobs as well.

Happy to take any input this database will end up holding 10,000+ records or more with 4-5 people entering in it daily.

The overall goal is to collected each job posting and document it in the database. The end result will be lots of reports and queries generated out of it for all kinds of reasons. Errors with in a specific department, county and so on. Right know we are not even sure how we will be utilizing the data because this is a brand new department.
This post has been edited by dlafko: Apr 2 2019, 02:15 PM
Go to the top of the page
post Apr 2 2019, 02:09 PM

Posts: 3,124
Joined: 27-February 09

If I were you, I'd start at the beginning.

Describe what this database is supposed to do. What are the real world things you need to keep information about? How are those things related?

You've got repeating field city. Don't do that. You're gonna cry if you have to find a single value that could in any one of 30+ columns.

Tell us the brief "story" of what this database is going to store information about. If we understand that, then we can probably help you. Right now, I don't think anybody can make head or tail of your current design.
Go to the top of the page
post Apr 2 2019, 02:34 PM

Posts: 217
Joined: 7-May 14

Ok so starting at the beginning.
I am part of a new unit that will audit the hiring processes with in my organization. There are multiple offices all around the state and multiple agencies with in them. I want to have this as a split database to ensure the data entry people can do that quickly and when we start running searches to find out what issues are out there so it does not take for ever to generate a report. My overall goal is to eliminate as much human error as possible by having known data, such as an agency or department name, in tables that combo boxes can pull out and then store with the record and check boxes to give people the ability to identify specific issues we see all the time. I also want people to choose the Job Code (Unique to every position) and the database automatically pull the position title this is used as a check to ensure the job posting title is correct to the job code. (I know how to physically do this, it is then storing the data for later use that's the issue)

This data entry of new job postings will continue for ever at this point with the data growing and growing. I figure at some point we will do archives to ensure it does not get unwieldy. I am not able to pay anyone to do this and right know the data is being pushed onto a excel sheet and is huge already with 2,000 records, and lots of errors.

I have 2 areas, internal postings which have a lot more information on them and require multiple pieces of information to be stored. The other is for external postings and requires a lot less data to be captured. Some information is the same on either posting.

There are several data sets that will be very large amount of data, outside of the main record but need to be attached (relationship) to the main record.
1. Next Lower Job Classes that the employer would consider for promotion
2. Position numbers which are unique to the position but not the job. So we could have one posting for Human Resource 1Job code 8888 with 15 open positions and they would each have their own number.
3. Hiring Preferences, This could be anything from a work location, to languages spoken, to licenses required.

The first two are with internal positing's and the last one is for external postings.

Eventually I will need to create reports and queries that are easily customizable for the end use to pull. An example would be how many records are from the Dept of Corrections, Of those how many have issues of some type. We might even eventually drill down to specific issues that we have laid out with the check boxes. I thought check boxes would be easier again to avoid issues with the data later on.

The goal is to provide the data entry people a nice platform to enter the information and the manager that ability to run queries and get reports that we can then base our decision, of who to audit due to identifiable issues with their hiring postings.

Does that make sense? Below is an explanation of each of the tables I have created so far. The forms are designed in a user friendly way and it is my process to see what info I need to capture.

1. Tables
a. Holding Data to use combo boxes
i. TBLAgency
ii. TBLEPRRating
iii. TBLJobCodeTitle
b. Tables I think we would save data into.
i. TBLNextLowerClass—If the position is for an HR analyst 2 then all HR analyst 1’s could apply. This list can get very length with lots of next lower closes
ii. TBLPositionNumber—This is the specific number the vacant position is, sometimes they fill a wide variety or positions with one job posting.
iii. TBLHiringPreference—This could be a county, department, town whatever
iv. TBLIssues—this is where I was thinking we would make it easy for the end user and reports later to all check box designation of common errors and hold any comments made.
v. TBLMain, This is where I was thinking you have to hold all the PKID’s and some data that does not really fit any where else
vi. TBLRrcruitment—this would hold all the data related to the main records for how people can be recruited into the position.

I am also available to call anyone or email, Thanks for the help. I am really stuck on how to store all of this outside of one giant table. I thought I knew but I am bogged down on something.

Go to the top of the page
post Apr 3 2019, 07:19 AM

Posts: 217
Joined: 7-May 14

So I took all your advise and spelled everything out in another fresh post. I hope you can go to it and help me out. I would appreciate it.


Is the URL for the new post.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    17th June 2019 - 06:17 AM