Populating fields in a form based on another table.

[daverskully]

The tables are as follows:

This table has about 100 records that will remain virutally the same forever, however there may be slight changes to some records.

1) Table Name: DepartmenInfo
Department
DepartmentNumber (Unique Identifier, one department number for each department [100 departments])
AccountExecutive (One account executive looking after many departments)
7 Text boxes that relate to each department and will not be changed

2) Table Name: Data
Department
DepartmentNumber
AccountExecutive
7 Text boxes that relate to each department and will not be changed
Only new fields: 6 memo boxes to input information

Information about these departments will be inserted bi-weekly and the 6 memo boxes are the only thing that will be entered.

What I am looking to do is have a form, based on the table Data, so that once I click on the Department or Department Number from a combo box, it will populate all fields that relate to that department based on the information from the DepartmentInfo table, thus leaving the user to input only the 6 memo boxes that are available in the Data table. Once inputted, I want all of the information to go on a record in the Data table.
I have been specifically asked to do it this way, I realize there are normalization and redundancy issues, but if possible can somebody please throughly explain a method about how this can be done? If you require any further information just ask, I will be very quick to repsond. Thanks in advance

 

[Mile-O]

I have been specifically asked to do it this way, I realize there are normalization and redundancy issues...

This is something that always interests me when people say they have been specifically asked to do something in a way that is known to be wrong. The first question you should be asking is what difference is makes to whoever i asking what way you do it, since it's not likely the inner workings go on show and, as long as the functionality is there, they should keep their beaks out of it.

 

[Mile-O]

From your PM, as I don't answer them if they are Access related:

Do you know if my question can be answered or does the database need to be normalized? All I'm looking to do is transfer data from one table into a new record on a form for another table. I want to be able to click a combo box for a certain field (based on Table1) and have all of the other fields that are corresponding to that field in Table1 be entered into the form for the Table2.

As far as I can see it, you database has redundancies and needs to be normalised. What you want probably could be done without normalisation but it makes sense to put in that extra work and get it ship-shape to ensure that things are easy to change in future, to remove redundant information (i.e. that field called Department in both tables), and to make life easy. If sponsors/clients want a database in a certain way, they are only thinking of the output, no matter what they say. If they knew how they wanted it to work they would do it themselves, since they are experts.

I don't know what the seven fields relative to departments are, but I'm guessing they are attributes of a sort. And I would guess that not all departments have all the attributes, so it would make sense to create an attributes table. Similarly, an account manager table. Finally, two junction tables, one for linking account managers to departments, and one linking attributes to departments.

Here, then, is my skeleton layout:

tblDepartments
DepartmentID (Autonumber)
DepartmentName (Text)

tblAccountManagers
AccountManagerID (Autonumber)
Forename (Text)
Surname (Text)

tblAttributes
AttributeID (Autonumber)
Attribute (Text)

tblDepartmentsToAttributes
DepartmentID (Number)
AttributeID (Number)

tblDepartmentsToManagers
DepartmentID (Number)
AccountManagerID (Number)
DateAssigned (Date/Time)
DateUnassigned (Date/Time)​

Obviously, around the two junction tables you can create rules around how you would want to build a history, so you have records of incoming/outgoing account managers, and those switching from one department to another, etc.

And by using a junction table for departmental attributes, you save yourself the hassle of adding new ones, in future, should they occur (and no matter what anyone says, they do and will), so that you need not add new fields to tables and go through the rigmarole of adding these fields to queries, squeezing the extra field onto forms, and reports, and adapting any VBA to accommodate them too.

 

[daverskully]

I'm going to do this asap, thanks Mile-o