N00b needing help - multi-tables, forms and everything (1 Viewer)

jambon316

New member
Local time
Today, 02:33
Joined
Apr 30, 2017
Messages
3
Ok,
I'm trying to put our job application process online, right now it's literally a printed doc files posted or emailed to applicants, very dated and slow now, and screaming out to be made better.

To that end, I have taken this on as a project but I have now started to hit a struggling point so I just want to check I'm going about this the right way etc.

okay so I have a main form called (wait for it...) Main.
Main has the information that an applicant would only enter one time. The Main table has the following fields:


Primary Key+autonumber ID
Short text, PostAppliedFor2
Short text, SURNAME
Short text, OTHERNAMES
Short text, ADDRESS
Short text, POSTCODE
Short text, EMAIL
Short text, PHONE
Short text, MOBILE
Short text, PersonalStatement
Boolean, WelshSpeaker
Short text, NoticePeriod
Boolean, UkWorkRestricted
Short text, UkRestrictions


In addition to this there are more tables for information that could require more than one entry (Specifically Employers, Schools,References and training courses)

I have the main table mapped out on a form with 5 buttons at the bottom linking to each of the smaller tables. And a finish&Save button.

I want to pass the value of the main.id field into a field called applicantID, as each smaller table may have more than 1 entry by the applicant, it seemed to make sense that I could query the database later for the applicantID, It would return an applicant's main form (All of it) along with each entry they made in the 4 smaller tables.

I'm having problems passing the value from main.id to xxx.applicantID, I know that I need to probably use the 'form on load' event so that the value is passed automatically based on the ID from main. I've tried various ways of doing it but I keep getting error code 2766 when I run it.

Am I doing this the right way? Anyone got any ideas on a better way or the magical syntax that I simply can't seem to get right.

My table names are:
Main
Education
Employment
Training
References

ID is the primary key from Main... each table has a number field called ApplicantID which is where I want to pass the value to.

Cheers
Jamie
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:33
Joined
Jan 23, 2006
Messages
15,364
Jamie,

Start with a clear description of WHAT you are trying to do. Build a model of your proposed tables and relationships based on your business facts. Test the model, then design your database.
Here is a link to database design information.

Don't be too eager to jump into physical MsAccess or other dbms - understand database concepts first.

Good luck.
 

jambon316

New member
Local time
Today, 02:33
Joined
Apr 30, 2017
Messages
3
Yeah agree with you entirely. I didn't just randomly decide on the data structures above, The data has been separated out in a way that makes sense (for me at least). The relationships between the tables are fine.

The main thing I'm trying to achieve is a database which will contain a list of candidates for any given vacancy. Ideally I'd like the applicant to have access to and be able to complete the form from anywhere but obviously they would only be able to see + complete their own form

Main has a 1 to many relationship with all 4 other tables. Each applicant who fills in the form only needs to go through the main form once, the other 4 tables contain data where they will likely have to repeatedly enter data.

If the main applicant form gives each applicant a primary key , which I have called 'ID', then as long as that value is stored in the child tables then all the data they enter can be easily pulled out by matching up the Form "Main" and then any records from the other 4 tables where "ApplicantID" has a matches up to the Main ID value. I'm now just trying to work out the real-world "how do I do this?" bits.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:33
Joined
Feb 28, 2001
Messages
27,001
The "complete the form from anywhere" is going to be a problem since Access normally does not web-enable very well.

You potentially should consider a many-to-many situation - many applicants and some of them could qualify for more than one job. Look up the topic "Junction Table" using this forum's SEARCH function.

When I was still working with the Navy Enterprise Data Center, our relationships between the Pers (personnel) and Billet (job) table allowed a reservist to apply to multiple jobs and each job could be requested by multiple reservists. We had some fancy-schmancy queries to rank-order each applicant for each job to which they had applied. There was some sort of scoring method that told us how many people we had failed to place in any of their preferred billets and there was a way to "downgrade" a score for certain popular billets so that we could place people in their second-choice billets sometimes. The goal was to come out with NO unassigned reservists although sometimes it took more than one iteration to get it right.

The "downgrade" was done on the junction-table entry for a given reservist/billet combo so that we could, in essence, "make room" for someone else. I cannot tell you that I know much about that particular database. I was the systems admin for the host machine but the applications programmers and app support team manipulated the placement weights.

Don't know if that helps or not, but it seems applicable.
 

Cronk

Registered User.
Local time
Today, 13:33
Joined
Jul 4, 2013
Messages
2,770
I'll add a different point to other suggestions already made.

Seasoned developers will name their tables to reflect the entity data contained in them. In your case, rather than Main, call it something like tblApplicants and name the unique field ApplicantID.

This makes it not only easier for you to identify what you are working with, and also for anyone following you, to understand the structure of the database.

A pox on anyone who names their tables/fields Table1, Table2,... Field1, Field2...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:33
Joined
Feb 28, 2001
Messages
27,001
Cronk's right on point here. The only time you should ever see Table1, Table2, ..., Field1, Field2, ...etc is when writing example code here in this forum to demonstrate a point.
 

Users who are viewing this thread

Top Bottom