help with a simple query

[jim Bob]

Hi, Can someone help with a simple query?

I have a table with the following.
[maintable]
Firstname
Lastname
InterviewerID1
InterviewerID2
InterviewerID3
...
InterviewerID9
InterviewerID10

In another table I have:
[Interviewer]
InterviewerID
InterviewerName

when I create a query to show the full name with all the interviewers, I
need to add the Interviewer table 10 times. Is this correct?

I have a feeeling it is coz it worked initially. Then as the data
increases, it takes forever to return the results..

I have indexed the tables etc so at a lost as to why it takes almost
forever to return.

Thanks
*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

No, that is the wrong structure.

You need 3 tables: one for person, one the the interviews, and one for the
interviews:

Client table (one record per person):
ClientID AutoNumber primary key
FirstName Text
LastName Text
ApplicationDate Date/Time (when person first applied for an interview)
...

Interviewer table (one record per interviewer):
PersonID AutoNumber primary key
FirstName Text
LastName Text
...

Interviews table (one record for each interview):
ClientID who was interviewed
InterviewerID who conducted the interview
InterviewDateTime when the interview was conducted.

You can now have as many or as few interviews as you need for any person,
with any interviewer, and sort them by the InterviewDateTime. You can also
do other stuff such as querying how many times a person has been
interviewed, count how many interviews each interviewer conducted in a
month, and heaps more.

Whenever you see repeating fields such as InterviewID1, Interviewer2, ...,
it *always* means that you need a related table with one column to hold that
info, instead of repeating fields in one table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jim Bob" <fr****@gmail.com> wrote in message
news:4B****************@news.uswest.net...

Hi, Can someone help with a simple query?

I have a table with the following.
[maintable]
Firstname
Lastname
InterviewerID1
InterviewerID2
InterviewerID3
..
InterviewerID9
InterviewerID10

In another table I have:
[Interviewer]
InterviewerID
InterviewerName

when I create a query to show the full name with all the interviewers, I
need to add the Interviewer table 10 times. Is this correct?

I have a feeeling it is coz it worked initially. Then as the data
increases, it takes forever to return the results..

I have indexed the tables etc so at a lost as to why it takes almost
forever to return.

[jim Bob]

Thanks for your reply Allen.

The reason why I had it like this was because in my table i actually
have
[Maintable]
DBID
LastName
FirstName
DOB
CampusInterviwerID1 ->staff Table
CampusInterviwerID2 ->staff Table
CampusInterviwerID3 ->staff Table
OnsiteInterviewerID1 ->staff Table
OnsiteInterviewerID2 ->staff Table
OnsiteInterviewerID3 ->staff Table
SitePreferenceID1 ->site pref Table
SitePreferenceID2 ->site pref Table
SitePreferenceID3 ->site pref Table
ManagerID ->staff Table
BuddyID ->staff Table

[staffTable]
StaffID
StaffName

[siteTable]
SiteID
SiteName.

are you saying that this structure is not correct? Because at the moment
it works ok. When I do a query in order to show all the information just
takes a long time.

Your help is much appreciated.
Thanks

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

That stucture is not correctly normalized, i.e. there are better ways to
design this.

If you do not wish to design it correctly and gain the simplicity,
flexibility, and queriability that a normalized structure would give, you
should still be able to get workable performance out of this structure.
Presumably the StaffID is primary key in staffTable? If so, you could open
the Relationships window (Tools menu), and drag the Staff table in multiple
times. Access will alias these copies as staffTable_1, staffTable_2, etc.
You can then create a relationship between one of the fields in Maintable
and one of the copies of staffTable, until all fields have been related. Be
sure to check the box for Referential Integrity in each case.

That will cause Access to create the foreign key indexes. You should now get
good performance, unless you have criteria that do not allow the indexe to
be used. Or - quite possibliy with your awkward structure - the critiera
might be very convoluted to get the answers you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jim Bob" <fr****@gmail.com> wrote in message
news:Wy****************@news.uswest.net...

Thanks for your reply Allen.

The reason why I had it like this was because in my table i actually
have
[Maintable]
DBID
LastName
FirstName
DOB
CampusInterviwerID1 ->staff Table
CampusInterviwerID2 ->staff Table
CampusInterviwerID3 ->staff Table
OnsiteInterviewerID1 ->staff Table
OnsiteInterviewerID2 ->staff Table
OnsiteInterviewerID3 ->staff Table
SitePreferenceID1 ->site pref Table
SitePreferenceID2 ->site pref Table
SitePreferenceID3 ->site pref Table
ManagerID ->staff Table
BuddyID ->staff Table

[staffTable]
StaffID
StaffName

[siteTable]
SiteID
SiteName.

are you saying that this structure is not correct? Because at the moment
it works ok. When I do a query in order to show all the information just
takes a long time.

Your help is much appreciated.
Thanks

[jim Bob]

Hi Allen,
I understand your first example fine.

I agree that in my second example it is not correctly normalized. I have
racked my brains out to try to find a better way. Can you give me an
example of how to normalize it properly?

Is it something that is straight forward and I have completely missed or
does it take a bit of time?

Thanks a lot!

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Please read my first reply for an example of how to normalize it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jim Bob" <fr****@gmail.com> wrote in message
news:Az***************@news.uswest.net...

Hi Allen,
I understand your first example fine.

I agree that in my second example it is not correctly normalized. I have
racked my brains out to try to find a better way. Can you give me an
example of how to normalize it properly?

Is it something that is straight forward and I have completely missed or
does it take a bit of time?