One-to-many problem, multiple row per record

[lostwithaccess]

Hi All,

I have been self-teaching access for about 2 weeks now, but am a bit stuck now on how to make things work!

Basically I am making a staff database, recording personal staff details, and multiple companies they have previously had involvement with. It feels like I need multiple rows per record but I know obviously this is not the way to go or else I end up with many records for one staff member. Also, for each company they have worked with I need to record 4 pieces of information (tickbox or yes/no is fine).

Like this:

Last_Name Company Recruitment Travel Finance Education

Bloggs_________EDF Yes Yes No No
______________Oracle No Yes Yes No
Williams________XYZ Yes No No No
______________Aston No No Yes Yes


I have tried creating extra tables and one-to-many relationships with junction tables etc, but with no joy.

Also self teaching has meant I do not know coding, I hope this is possible without this!

I would very much appreciate your help, many thanks in advance!

 

[jzwp22]

If a person can be associated with many companies and a company can be associated with more than 1 person than you have a many-to-many relationship.


tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName


tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

Now the junction table

tblPeopleCompany
-pkPeopleCompanyID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkCompanyID foreign key to tblCompany


Now you have many items that are associated with each people/company combination and one of those items might apply to many people/company combinations, so you have another many-to-many relationship

A table to hold the items Recruitment Travel Finance Education
(each as a record)

tblItems (you can probably give this a better name)
-pkItemID primary key, autonumber
-txtItem

Now associate each of the applicable items to the people/company combination. I would just associate the ones that would be the equivalent of having the yes box checked

tblPeopleCompanyItems
-pkPeopleCompanyItemID primary key, autonumber
-fkPeopleCompanyID foreign key to tblPeopleCompany
-fkItemID foreign key to tblItems

 

[lostwithaccess]

Thanks jzwp22!

I had posted another message with lots of questions, but after posting I spent a good few hours playing with the database and had a bit of a breakthrough - based exactly on what you said, it just came down to me still not quite understanding! Steep learning curve!

The tables are now relating to each other, cross referencing the multiple selections in different tables by staff number. My only thought now is if it is possible to instead reference the information by staff name rather than the new staffID number?

I am still working on adding the yes/no options for each country, but I'll keep trying before I waste your time on my project!

Cant thank you enough,

Kind regards, Notsolostwithaccessfornow

 

[jzwp22]

I would probably use 1 form to enter all possible countries. I would create a form in datasheet view based on the junction table and use a combo box to populate the fkcountryID. The combo box would be based on your country table. Then I would create another form based on the staff table. Then while in design view drag and drop the form you created based on the junction table. If you have your relationships established in the relationship window Access should automatically make the join between the main staff form and the subform based on the junction table.

In terms of data entry, you would enter your staff member and then select the applicable countries in the subform that apply to that staff member. You can have any number of countries tied to that staff member. You would not show all countries for each staff member, only those that apply, so you would not need to mess with check boxes.

My only thought now is if it is possible to instead reference the information by staff name rather than the new staffID number?

The joins between tables should be made with the staffID which should be the autonumber field in your staff table. The autonumber field is there for use by Access and should have no meaning to your users, in fact, you should hide the staffID field in your forms. Joining via the number fields is more efficient than joining via a text field like the person's name. If you want to view data that is housed in the junction table with the respective person's name and country names, you would use a query that joins the two tables and select the fields you want to see.

 

[lostwithaccess]

I have created a form to enter staff information, and a subform with it linking to the tblStaffCountry, but then I tried to add 4 more fields to tblStaffCountry to store yes/no options for each country (Research Yes/No, Study abroad Yes/No, Employability Yes/No, Recruitment Yes/No), but when I do this and reopen the form I get this error message "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

And I cant seem to get rid of it even if I remove the yes/no fields. With this error, once the form opens, the main form displays ok but the subform just displays as a blank box...

Although not really what I want, I did try a list box with multiple options possible just in case it worked as an alternative, but this did not help either. Any ideas?

If it helps explain the problem I have attached my database as it is so far.

Many thanks

 

[jzwp22]

Research Yes/No, Study abroad Yes/No, Employability Yes/No, Recruitment Yes/No

If these items apply to the person relative to the country then you have another one-to-many relationship (many items to the person/country combination) which should be captured in another table

 

[lostwithaccess]

I have had to leave the extra info yes/no options for now because although I need it I cant get past the country entry problem. I've tracked the error message back to any entry in my subform.

If I try and make a new staff record, it is all fine with the information and multiple country entry, but then close the form and try and reopen it and I get the error message.

Clearing tblStaffCountry removes the error but also the very info I need! If I can fix that I would like to add 4 more fields for yes/no on the same table, but I am not sure what to do?

 

[jzwp22]

I assume that you have modified your database. Can you post it again and I will try to figure out the issue. I only have Access 2003 here at work, so I won't be able to look at it until I get home this evening.

 

[lostwithaccess]

Latest version should be attached, as you'll see the form "table and subform" has the space for staff data entry, and the subform for country entry, but returns an error when you enter a country, but clear tblStaffCountry and reopen Form "Table and Subform" and it should be ok again.

Thanks again

 

[jzwp22]

OK, I'll have to take a look at it tonight.

 

[jzwp22]

I could not get your form to work properly, and you still have several structural issues. For example, how does a funding method (in the staff table) relate to a person ? I would think the funding method is related to some program that the staff member participates in. I also noticed that you had lookup fields (combo/list boxes) at the table level. Having them causes problems as described on this site You also had the wrong data type for several of the foreign key fields; they should all be long integer number fields.

I have corrected the structural issues, got rid of the table level lookup fields and created a new form. You still have work to do on the table structure, so I would not spend much time on forms yet. The corrected DB is attached.

 

[lostwithaccess]

Thank you for taking the time to look at it, very much appreciated, and thanks for clearing up the relationships, now they look much more manageable!

Now I understand better how it should operate, but when I look to make it possible to enter the related information for each staff members' country I keep going in circles, and cant work out how to make it happen.

Your point about the funding is very true, I will most likely have to make space for multiple future trip entries and related funding. But until then I just cant get my head around how to enter the nature of visit info whilst making it related to the staff member and country - all via a form...

 

[jzwp22]

I would not worry about forms yet. Getting the tables structured properly is more important at this point.

When a person visits a country what type of data do you want to capture for that visit?

Can a person visit the same country multiple times?

 

[lostwithaccess]

When a person is linked to a country the data I need is as follows:

Name - Joe
Link 1 - Sydney Uni, Sydney, Australia, Nature of relationship (4options), Contact link & position, 3 x notes fields.
Link 2 - Florida Uni, Florida, USA,....

Each staff member will only be linked with an institution once, as the info will be just a snapshot of all past activity in one, but they can be linked to more than one institution per country, and obviously more than one country.

 

[jzwp22]

Each staff member will only be linked with an institution once, as the info will be just a snapshot of all past activity in one, but they can be linked to more than one institution per country, and obviously more than one country.

The above changes everything. The person is directly related to the institution. The institution is related to the country.

tblCountry
-pkCountryID primary key, autonumber
-txtCountryName

tblInstitutions
-pkInstitutionID primary key, autonumber
-txtInstitutionName
-txtCity
-fkCountryID foreign key to tblCountry

tblStaff
-pkStaffID primary key, autonumber
-txtFName
-txtLName

tblStaffInstitutions
-pkStaffInstID primary key, autonumber
-fkStaffID foreign key to tblStaff
-fkInstitutionID foreign key to tblInstitutions

 

[lostwithaccess]

Updated tables and relationships attached. There is currently no 'natureofrelationship' part, do I add a fkNatureOfRelationship to tblStaffInstitutions, with a field for each piece of information?

I am trying to stay away from making forms as you said, but I am just intrigued as to how data will be inputed across all tables once the structuring is finished. Will this all be done through a main form and subforms? Many thanks

 

[jzwp22]

Will this all be done through a main form and subforms?

yes

There is currently no 'natureofrelationship' part, do I add a fkNatureOfRelationship to tblStaffInstitutions, with a field for each piece of information?

What is meant by nature of relationship? If it refers to a person's role with respect to the institution then yes it would go in tblStaffInstitutions. But can a person have many relationships with the particular institution.

 

[lostwithaccess]

I will have to allow for the nature of relationship to more than one of the given reasons. I have made tblNatureOfRelationship, holding 4 possible options. Should I then link pkNatureOfRelationshipID to fkNatureOfRelationship in tblStaffInstitutions?

 

[jzwp22]

...nature of relationship to more than one

Based on what you say above, you are describing a one-to-many relationship, so the possible choices must be in a separate but related table.

tblNatureOfRelationship
-pkNatureOfRelationshipID
-txtNatureofRelationship

tblStaffInstitutionNatureOfRelationship
-pkStaffInstNatureRelateID primary key, autonumber
-fkStaffInstitutionID foreign key to tblStaffInstitution
-fkNatureOfRelationshipID

 

[lostwithaccess]

I have now worked through all of the relationships on my database, and i think every table is now properly related, so to the best of my knowledge the basic structure should be good, so I am now starting to work on the data input/form. Hopefully this will seem simpler than where I started! I have attached a relationship report if you are interested.

Thanks again for all your help, I really appreciate it! Hopefully I wont have too many questions about the data input!