Adding to a form (1 Viewer)

[jk42]

Hi Friends!

I feel like for every one thing I learn how to do in Access, it uncovers 50,000 more things that I need to learn! My issue is this:

I have all of our employees in a form that also shows which projects they are working on, as well as their location. I wanted to add to this form a spot where I could list their experience So I could search for "an employee who lives in Florida and has experience in _____" Each employee will have anywhere from 5-50 "experience" items.

1. Should I do this in a completely separate database from the one that I currently have detailing our companies work experience?

2. Is it possible (easy) to add this experience item to the table/query/form that already has all of the employees other information on it?

3. If I do #2, can I do it in a way where I can select multiple items?

4. I am open to any suggestions. I have been sitting here for awhile playing around in my database with different scenarios, but while i"m sure I could get something to work, I want it to be the best way to do it.

Thanks!

 

[Estuardo]

G'd morning,
Answering your questions in the same order:
1. No
2. Yes
3. Yes

In a little more detail:
1. You don't need another database. Even if you already have one with all the information related to their experience, you can link that table to your db. The difference is that you will need to carry all your mdb files together.
Or you can import/create the necessary tables to relate the information.

2. To load too many tables and/or rows in a single form will make your form to load and perform slow.
I don't know why i'd been seen this "mistake" more often of what i expect. I'm talking about the multitask form. Build one form to enter/modify data and do something completely different to search/analyze.
Then you can go back to the main form and filter its data via filter or query.
G'd luck

 

[Pat Hartman]

Is question 1 really asking if you should create a second table? If so, the answer is Yes. The experience items belong in a separate table where you can have as many as you need for each employee. You may or may not want to add an additional subform to your existing form. I probably wouldn't or I might make the form tabbed and put the experience subform on a speparate tab.

 

[jk42]

So if I do create a new table, would I just have their name, and then would each experience item need it's own field for it to work best? So I would have :

NAME: Experience 1 Experience 2 Experience 3 .... and so on up to 50 or so


Or is there a smarter way to do it? I have seen some multivalue fields, but I am nervous about those.

Thanks!

 

[Pat Hartman]

The table would be something like:
tblEmpExperience
EmpExperienceID (autonumber PK)
EmpID (foreign key to tblEmployee)
ExperienceID (foreign key to tblExperience)
ExperienceLevel
CertificationDate
...
Each experience item would be a separate row. I would avoid multi-value fields. They are a crutch and they don't upsize to SQL Server. Use a subform to display the experience choices for each employee.

 

[jk42]

Thanks, Pat! I'll give it a try soon!