hillary123
Programmer
Hi -
I'm building a database in Access 2003 that will serve as an "expertise directory" for staff. I have 3 tables with 3 corresponding forms, each set up as follows:
1) contact information (staff_info)
2) current work projects - (portfolio) up to 5 different items, with the titles entered by the user in 5 different fields (port1, port2, etc.)
3) expertise areas - (expertise) up to 5 different items, and for each area the user will input:
a) program area (combo-box) (area1, area2...)
b) brief description (text) (expinfo1, expinfo 2...)
c) start year (yyyy) (expst1, expst2...)
d) end year (yyyy) (expend1, expend2...)
e) name of associated document (if there is one) (text) (doc1, doc2...)
The "profile" entry form is staff_info as the main form and portfolio and expertise as subforms. They are linked through the p.k. staff_id.
My goal is this: Staff will be able to 1) enter all of this in one form (making up a profile), 2) find and edit their profile at any time, and 3) search the directory through a search-form (including drop-downs, < or > year range & wildcards) for different experts. The experts should show up on a basic list (subform) where the user can then click on each of them to open the full profile or can create a report of all of them.
I've done the entry form and for the edit form I'm hoping to set it up a pop-up to search for the email address and open the form for that profile.
But the search form is where I'm really stuck. I don't know how to tie area1, expinfo1, expst1, expend1, doc1 (3.a-e from the list above) together as ONE item for each of the 5 expertise areas (3a-e for item 1, 3a-e for item 2, etc.) so for example a search for experience in a specific program area after 2005 only returns profiles where the area and year match WITHIN THE SAME ITEM, and not profiles with 2005 in any of the expertise items and a matching program area also in any of the expertise items. I realise I'm not explaining this perfectly but I hope it's clear what I'm trying to do...
I think I may need to rework my table structure but I'm not sure how to do it so the searches will work. I've put this post in Access Tables and Access Forms forums because it seems to be a bit of both.
Any help here is really appreciated - I've been working on this for ages and have until the end of the week to get it going!
I'm building a database in Access 2003 that will serve as an "expertise directory" for staff. I have 3 tables with 3 corresponding forms, each set up as follows:
1) contact information (staff_info)
2) current work projects - (portfolio) up to 5 different items, with the titles entered by the user in 5 different fields (port1, port2, etc.)
3) expertise areas - (expertise) up to 5 different items, and for each area the user will input:
a) program area (combo-box) (area1, area2...)
b) brief description (text) (expinfo1, expinfo 2...)
c) start year (yyyy) (expst1, expst2...)
d) end year (yyyy) (expend1, expend2...)
e) name of associated document (if there is one) (text) (doc1, doc2...)
The "profile" entry form is staff_info as the main form and portfolio and expertise as subforms. They are linked through the p.k. staff_id.
My goal is this: Staff will be able to 1) enter all of this in one form (making up a profile), 2) find and edit their profile at any time, and 3) search the directory through a search-form (including drop-downs, < or > year range & wildcards) for different experts. The experts should show up on a basic list (subform) where the user can then click on each of them to open the full profile or can create a report of all of them.
I've done the entry form and for the edit form I'm hoping to set it up a pop-up to search for the email address and open the form for that profile.
But the search form is where I'm really stuck. I don't know how to tie area1, expinfo1, expst1, expend1, doc1 (3.a-e from the list above) together as ONE item for each of the 5 expertise areas (3a-e for item 1, 3a-e for item 2, etc.) so for example a search for experience in a specific program area after 2005 only returns profiles where the area and year match WITHIN THE SAME ITEM, and not profiles with 2005 in any of the expertise items and a matching program area also in any of the expertise items. I realise I'm not explaining this perfectly but I hope it's clear what I'm trying to do...
I think I may need to rework my table structure but I'm not sure how to do it so the searches will work. I've put this post in Access Tables and Access Forms forums because it seems to be a bit of both.
Any help here is really appreciated - I've been working on this for ages and have until the end of the week to get it going!
