By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Searching across several tables using Queries

P: n/a
Hi

I'm hoping someone can help me with an access problem I just can't get
my head around. I normally use access as a back end for asp pages,
just to hold data in tables, so queries within access are a mystery to
me, but I can't think of any other way of dealing with the problem.

I have six tables in my db:
tblCompanies (list of companies, primary key CompanyID)
tblOffices (list of office buildings including what company owns/uses
them (CompanyID), primary key OfficeID)
tblPeople (list of people including what office they work in
(OfficeID) and what their job class is (JobClassID), primary key
PersonID)
tblProducts (broad classifications of service lines e.g. banking,
insurance, investments, Primary Key ProductID)
tblCoProducts (CompanyID and ProductID cross-related such that each
line in the table lists one Co and one Product, therefore a Co with
two Prods has two lines in the table)
tblJobClass (broad classifications of job titles e.g. senior exec,
director, middle manager, primary key JobClassID).

Companies can (and usually do) have more than one office, but a person
can only work in one primary office, so to normalise my db I have
included OfficeID as a field within tblPeople and CompanyID as field
within tblOffice. People can only have one Job Class so I have
included JobClassID as a filed within tblPeople.

What I want to do is search for all people with a particular job class
who work for companies that sell a particular class(es) of product.

i.e. I want to match the product(s) in my search form against the
contents of tblCoProducts to generate a list of many companies that
sell that product(s), then filter tblOffice to find all the offices
matching that companyList, then filter tblPeople against that office
list AND whether their job class matches what was in the search form!

Is it possible to create a Query that is pre-defined so that it gets
the right list of people when it is fed the JobClassID and
ProductID(s)?

Many thanks in advance for any input.

Tim
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ti*************@btinternet.com (Tim Pollard) wrote in message news:<f3**************************@posting.google. com>...
Hi

I'm hoping someone can help me with an access problem I just can't get
my head around. I normally use access as a back end for asp pages,
just to hold data in tables, so queries within access are a mystery to
me, but I can't think of any other way of dealing with the problem.

I have six tables in my db:
tblCompanies (list of companies, primary key CompanyID)
tblOffices (list of office buildings including what company owns/uses
them (CompanyID), primary key OfficeID)
tblPeople (list of people including what office they work in
(OfficeID) and what their job class is (JobClassID), primary key
PersonID)
tblProducts (broad classifications of service lines e.g. banking,
insurance, investments, Primary Key ProductID)
tblCoProducts (CompanyID and ProductID cross-related such that each
line in the table lists one Co and one Product, therefore a Co with
two Prods has two lines in the table)
tblJobClass (broad classifications of job titles e.g. senior exec,
director, middle manager, primary key JobClassID).

Companies can (and usually do) have more than one office, but a person
can only work in one primary office, so to normalise my db I have
included OfficeID as a field within tblPeople and CompanyID as field
within tblOffice. People can only have one Job Class so I have
included JobClassID as a filed within tblPeople.

What I want to do is search for all people with a particular job class
who work for companies that sell a particular class(es) of product.

i.e. I want to match the product(s) in my search form against the
contents of tblCoProducts to generate a list of many companies that
sell that product(s), then filter tblOffice to find all the offices
matching that companyList, then filter tblPeople against that office
list AND whether their job class matches what was in the search form!

Is it possible to create a Query that is pre-defined so that it gets
the right list of people when it is fed the JobClassID and
ProductID(s)?

Many thanks in advance for any input.

Tim


To answer your question, Yes. Where I start with databases that get a
bit unwieldly because of the number of tables is to create the
relationships between tables in the relationships window and print
that. Then I can sit there with a piece of paper or a transparency
that I paperclip on top of my entity-relationship diagram and do
simple things like circle the fields I want to project, draw lines
between joins (in case I have other tables I want to join etc) and
then I fill in criteria below each table. It just makes it easier to
get my head around a complex question. The other thing is to take it
ONE step at a time. Build the query incrementally. First filter one
table and run the query and check it, then join to the second
table/query and repeat.

("Umm... yeah, but that's not what I was asking...") Okay, so you've
built your query, and it works - it returns all the right unfiltered
values or when you specify criteria, it works.

Something like:
SELECT tblApartments.AptID, tblApartments.Building,
tblApartments.Unit, tblApartments.AptType, tblApartments.Rentable,
tblRentSchedule.Rent
FROM tblApartments INNER JOIN tblRentSchedule ON tblApartments.AptType
= tblRentSchedule.AptType
WHERE (((tblApartments.AptType)=[Enter apt type (E/1/2):]) AND
((tblApartments.Rentable)=Yes));

then you can substitute the actual values or prompts by pointing your
values at fields on a form.

e.g.

SELECT...
FROM...
WHERE (((tblApartments.AptType)=[Forms]![NameOfYourForm]![cboNameOfYourCombobox])))

If you open your form that you want to get your values from, and then
open your query in design view, you can use the wizard (top right,
looks like a wand) and then you'll see a list of object types on the
left, choose Forms, then your form, then your control name, and the
wizard will do the rest. Then you just put a button on your form that
opens your query and you're done.

Phew! (Easier done than said!)
HTH,
Pieter
Nov 13 '05 #2

P: n/a
Hi Pieter

Thanks for your help. I have taken your advice and built up the query
in stages.

For the benefit of anyone following the thread later, the following
SQL seems to have done the trick on my particular requirements.

SELECT tblPeople.PersonID, tblPeople.Honorific, tblPeople.FName,
tblPeople.LName, tblJobClass.JobClassID, tblJobClass.JobClass,
tblOffices.OfficeID, tblCompany.CompanyID, tblCompany.LongNm,
tblProducts.ProdID, tblxCoProd.YesOrNo
FROM tblProducts INNER JOIN (((tblCompany INNER JOIN tblOffices ON
tblCompany.CompanyID = tblOffices.CompanyID) INNER JOIN (tblJobClass
INNER JOIN tblPeople ON tblJobClass.JobClassID = tblPeople.JobClassNo)
ON tblOffices.OfficeID = tblPeople.OfficeID) INNER JOIN tblxCoProd ON
tblCompany.CompanyID = tblxCoProd.CompanyID) ON tblProducts.ProdID =
tblxCoProd.ProductID
WHERE (((tblJobClass.JobClassID)=2) AND ((tblProducts.ProdID)=1) AND
((tblxCoProd.YesOrNo)="y"));

I don't know if this is the most efficient way to solve the problem,
but it certainly seems to work!

Many thanks again for your help, Pieter.

Regards

TP
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.