Data entry in a normalized database

[alicevuap]

Hello!

I created a normalized database in Access 2013. It contains the following:
1. tblInstitutions (complete list of institutions participating in the program, with detailed info: code, name, address, postal code, city, country) - the only one containing data
2. tblBA (the one containing info on the specific contract: agreement period, checkbox for signed/not signed to keep track of the process, scanned contract attachment)
3. tblContact (contact details for each BA)
4. tblMajors (majors offered at my institution, and their code)
5. tblSubjectArea (subject area - the name of the major as it appears in the BA [ie the major is architecture-the name of the field in my institution-and the subject area is architecture and planning], and further details on the contract under the specific subject - number of students or stuff)
6. tblLanguages (each subject area could be taught in more than one language + details such as the level required)


+ the junction tables

I have created the following relationships:


As i was saying, the only table containing data is tblInstitutions, therefore i need a data entry form as per BA, friendly for other users, which contains the fields from all the tables in a single form, but i cannot figure out how to deal with the many to many relationships and how to link them to the main table. Do i need to include the junction tables? If i want to be able to insert more contact details or more major for one BA, do i create tabs? How would i relate all the data so that at the end i could generate reports ie filter by a major and show each institution and all the details in the existing BA's under the specific major?

Do you have any suggestions? Or could you guide me towards tutorials that might help (i googled it but i didn't find anything, maybe i'm not using the right keywords)?

Thanks a lot!

[CJ_London]

The rule of thumb is one table, one form.

To represent relationships


1. use a subform(s) -e.g. mainform is tblinstitutions and subform is tblBA and populate the linkchild and linkmaster subform properties with ErasmusCode
2. use combo's and listboxes for lookups, the rowsource for these could include your junction tables

If i want to be able to insert more contact details or more major for one BA, do i create tabs?

No, you would probably have two subforms on the mainform - and set the linkmaster of contacts subform to the selected BA in the BA subform via a hidden text field on the main form.

How would i relate all the data so that at the end i could generate reports ie filter by a major and show each institution and all the details in the existing BA's under the specific major?

too broad a question - but you would use a query controlled by a 'criteria' form where users select the criteria and perhaps which fields they want to see.

google keywords - always precede with the word 'access' or 'access 2010'
try - subforms, nested subforms, tutorials, etc

there is no one answer, but read some links you get, might lead you to refining your search

I note you have what looks like a multivalue field in one of your tables - so google 'access multivalue field' and read about how they should be used and why they are often a bad idea - you will find they limit what you can do.

Also, look to the bottom of this thread for some other threads on much the same subject you are enquiring about