Just thinking ahead, and trying to get my head around the
next stage of my database project.
At the moment I have the following tables :
Candidates
---------------
Candidate_ID (autonumber)
Name (text)
...
1, Iain
2, Fi
3, Rob
Vacancies
--------------
Vacancy_ID (autonumber)
Vacancy (text)
...
1, Cartographer
2, Gardener
3, Occupational therapist
4, Web designer
5, Recruitment manager
Profiles
-----------
Profile_ID (autonumber)
Profile (text)
...
1, Map making
2, Web design
3, Gardening
4, Hand therapy
5, Recruitment
6, Aviation
7, Sport
8, Travel
VacancyProfiles
----------------------
Vacancy_ID (number)
Profile_ID (number)
...
1,1
1,2
4,2
2,3
3,4
5,5
5,6
CandidateProfiles
--------------------
Candidate_ID (number)
Vacancy_ID (number)
...
1,1
1,2
1,7
1,8
2,3
2,4
2,8
3,5
3,6
3,7
and from there created two queries
CandidatesQuery
--------------------
SELECT Candidates.Candidate_ID, Candidates.Name,
Profiles.Profile_ID, Profiles.Profile
FROM Profiles INNER JOIN (Candidates INNER JOIN
CandidateProfiles ON Candidates.Candidate_ID =
CandidateProfiles.Candidate_ID) ON Profiles.Profile_ID =
CandidateProfiles.ProfileID;
1, Iain, 1, Map making
1, Iain, 2, Web design
1, Iain, 7, Sport
1, Iain, 8, Travel
2, Fi, 3, Gardening
2, Fi, 4, Hand therapy
2, Fi, 8, Travel
3, Rob, 5, Recruitment
3, Rob, 6, Aviation
3, Rob, 7, Sport
and
Vacancies_Query
-------------------
SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy,
Profiles.Profile_ID, Profiles.Profile
FROM Profiles INNER JOIN (Vacancies INNER JOIN
VacancyProfiles ON Vacancies.Vacancy_ID =
VacancyProfiles.VacancyID) ON Profiles.Profile_ID =
VacancyProfiles.ProfileID;
...
1, Cartographer, 1, Map making
1, Cartographer, Web design
2, Gardener, 3, Gardening
3, Occupational therapist, 4, Hand therapy
4, Web designer, 2, Web design
5, Recruitment manager, 5, Recruitment
5, Recruitment manager, 6, Aviation
So from these, I have a Candidates page that is based on the
Candidates table listing just ID, name and tel in a repeat region,
with a link to a details page based on the CandidatesProfiles query
above, and likewise with the Vacancies.
I'm just wondering how this works with editing and adding
records - as on those pages, I want to be able to add /edit fields
from the Candidates and Vacancies tables, but also add/edit the
profiles for each - do i just need my Add/Edit pages to be based on
the same CandidatesProfiles and VacanciesProfiles query / SQL
above?
I get how the page retrieving the data can display the
existing records, including multiple profiles for each in a repeat
region - but am less sure about adding / editing multiple profiles?
Or would it be a two step process - ie have a page to add a
Candidate, and then have a page that displayed the Candidate's
details with a subform where you could add/attach profiles to each
Candidate?
Hope that makes some sense, and someone can point me in the
right direction.
Cheers,
Iain
1
Reply
AdChoices