Solved How to use indexed fields (1 Viewer)

Cris VS

Member
Local time
Today, 13:45
Joined
Sep 16, 2021
Messages
75
Hello all,

I have created a table called PersonTable with 3 fields: PersonID (primary key), PersonName and JobName. I have set the JobName field to indexed (duplicates OK) and now I need to enable the user to write, through a Form, attributes for each JobName. These attributes are Industry and AverageSalary and will be stored in the JobTable.

I have attached the document where I tried to do this so that you can see what I have done if necessary. As these attributes will be related to other tables in the database, it would be very useful if I could have a table with each job and its information and also because I would avoid duplicating information. I would really appreciate if you could help me out as I am sure this is not the smartest way to use an indexed field.

Thank you very much!

PS: The information of the PersonTable is imported from another document.
 

Attachments

  • Database11.accdb
    1 MB · Views: 371

plog

Banishment Pending
Local time
Today, 07:45
Joined
May 11, 2011
Messages
11,613
Basically, indices make SELECT queries faster. Here's some material on the purposes of indexes:


However, you've got bigger issues.

1. You are not properly using Primary Keys. JobName should not be a field in PersonTable, instead you should have JobID in PersonTable and it will hold the numeric value from JobTable. This concept is called a Foreign key. Here's a link to that material:


2. You are using wrong datatypes. Short text can hold any data value you want--but that doesn't make it the right datatype for the job. PersonID looks all numeric to me, so most likely that should be a numeric field type, as should AverageSalary.
 

mike60smart

Registered User.
Local time
Today, 12:45
Joined
Aug 6, 2017
Messages
1,899
Hi

Normalisation is the key.

Your 2 Tables will not deal with your process.

The Person table should contain details about the Person.

The Person can over time have 1 or More Jobs

The structure you need is along these lines:_

tblPerson
-PersonID - Autonumber - PK
-PersonFirstname
-PersonLastname
-PersonDateOfBirth
-(Other fields describing the Person)

tblJobNames
-NameOfJobID - Autonumber - PK
-JobName - Text
-AverageSalary - Currency
-IndustryID - LongInteger - FK (Linked to PK from tbluIndustries)

tbluIndustries
-IndustryID - Autonumber - Pk
-Industry - Text

Because many People can Have Many Jobs, we need a Junction table along the following lines:-

tblJxtPersonJobs
-PersonJobID - Autonumber - PK
-PersonID - Long Integer - FK (Linked to PK from tblPersons)
-JobNameID - Long Integer - FK (Linked to PK from tblJobNames)
 

Cris VS

Member
Local time
Today, 13:45
Joined
Sep 16, 2021
Messages
75
I have tried these but the problem is that the data I import is already structured into the 3 columns PersonID, PersonName and JobName -and cannot be done in any other way-, so I don't see how to "convert" the list of JobNames to a table with the correct properties that you list above. That's why I thought that if I indexed the JobName values there was a way to do the opposite process... Something like stating the values of a field and then assigning a primary key to them in a separate table that allows me to keep operating normally with the rest of the database. Maybe its easier if I manage to extract the unique values of the jobs before importing the information so that they can be recorded in the JobTable directly and parallelly record each person's job in the PersonJobJunction table, right? Thank you very much to both
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:45
Joined
Feb 19, 2013
Messages
16,555
how to "convert" the list of JobNames
as part of the import process, look up the jobID based on the job name - for example

dlookup("JobID","tblJobs","jonbame='" & [jobName] & "'")

you would need a bit more to cater for the situation where the job does not exist

And I would include a date field (perhaps with a default value of now() or date()) in the linking table
 

mike60smart

Registered User.
Local time
Today, 12:45
Joined
Aug 6, 2017
Messages
1,899
I have tried these but the problem is that the data I import is already structured into the 3 columns PersonID, PersonName and JobName -and cannot be done in any other way-, so I don't see how to "convert" the list of JobNames to a table with the correct properties that you list above. That's why I thought that if I indexed the JobName values there was a way to do the opposite process... Something like stating the values of a field and then assigning a primary key to them in a separate table that allows me to keep operating normally with the rest of the database. Maybe its easier if I manage to extract the unique values of the jobs before importing the information so that they can be recorded in the JobTable directly and parallelly record each person's job in the PersonJobJunction table, right? Thank you very much to both
Are you Importing data on a regular basis or is the Import just a one off?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:45
Joined
Feb 28, 2001
Messages
27,001
I have tried these but the problem is that the data I import is already structured into the 3 columns PersonID, PersonName and JobName -and cannot be done in any other way-, so I don't see how to "convert" the list of JobNames to a table with the correct properties that you list above. That's why I thought that if I indexed the JobName values there was a way to do the opposite process... Something like stating the values of a field and then assigning a primary key to them in a separate table that allows me to keep operating normally with the rest of the database. Maybe its easier if I manage to extract the unique values of the jobs before importing the information so that they can be recorded in the JobTable directly and parallelly record each person's job in the PersonJobJunction table, right? Thank you very much to both

Whenever I see the phrase "cannot be done in any other way" I cringe - because that CANNOT be correct. Part of the problem is that you don't realize Access can break down the "import" phase into steps that allow you to import things ANY DAMNED WAY YOU PLEASE. So if you have one of these "cannot be done any other way" cases, the first question is "WHY can't it be done any other way?"

If this is a school assignment, that's one thing. If this is a business assignment, WHY do you have to make YOUR tables match a bad data structure that probably came from Excel or from a dumb program whose programmer doesn't understand report-making?

Tell us your limitations. If you can ONLY get data in a certain format, remember that Access can help you with reformatting and rearranging just as easily as it can help you in later reporting.

I'm not trying to be hard on you, but a lot of problems that we see turn out to be self-imposed and we need to know whether we have such a case here, because that will guide us in the direction we can go.
 

Users who are viewing this thread

Top Bottom