Help Please with complicated Query

[Toxalot]

Access 2003

User can choose options in a form to generate a report based on
specific criteria. Originally, user was required to choose one book
and one profile type and could choose one province if desired. I have
this working using a where clause in the DoCmd.OpenReport. But now
user is requesting many more choices and I just can't seem to wrap my
head around it.

Listed below are the applicable tables narrowed down to just the
applicable fields. PK designates the primary key and in the case of
tblProfilePeople it is the combination of two fields.

tblCompanies
lngCompanyId (PK)
txtCompanyName
txtProvinceCode

tblProfiles
lngProfileId (PK)
lngCompanyId
intBookId
intProfileTypeId

tblProfilePeople
lngProfileId (PK)
intRoldId (PK)
lngPersonId

tblCompanyPeople
lngPersonId (PK)
txtFirstName
txtLastName

User must choose intBookId and intProfileTypeId.

User wants to have any combination of the following options:
- one txtProvinceCode
- NOT multiple intBookId
- blnAdvertiser = True
- multiple intPersonId
- sort by txtCompanyName or txtProvinceCode

The result must be unique lngProfileId, lngPersonId

Each company has multiple profiles. Each company has multiple people,
but the same person may fill multiple roles.

I think one thing that is stumping me is finding companies that are in
a specific book and also not in another specific book. I can build two
separate queries and then find the unmatched records, but how do I
allow the user to choose the books?

Am I going to have to generate several temp tables or is there another
way I am not seeing?

Jennifer

 

[strive4peace]

Hi Jennifer,

what is the SQL for your report?

"finding companies that are in a specific book and also not in another
specific book"

create a cartesion query that shows all companies and all books

Name--> qAllCompaniesBooks
SELECT lngCompanyId, intBookId
FROM tblCompanies, tblBooks

I am assuming you have a table named tblBooks with Primary Key (PK) =
intBookId

If BookID is an autonumber in this table, it should be a Long Integer in
related tables

once you have the qAllCompaniesBooks query set up, you can find the
companies that do not have books with this:

Name --> qCompanyNotInBook
SELECT
qAllCompaniesBooks.lngCompanyId
, qAllCompaniesBooks.intBookId
FROM qAllCompaniesBooks
LEFT JOIN tblProfiles
ON qAllCompaniesBooks.lngCompanyId = tblProfiles.lngCompanyId
WHERE tblProfiles.lngCompanyId Is Null


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[kevin]

bonjour

 

[Toxalot]

I do have a table for books. The PK is not an autonumber because there
are only 10 books. Though the client may want to add 1 or 2 books in
the future, the id number means something to them. It's always a
unique number so I didn't bother with an autonumber.

tblListBooks is simply
intBookId
txtTitle
txtShortTitle

tblListProfileTypes is simply
intProfileTypeId
txtProfileTypeName

There are only two profile types and if that ever changed it would
require a lot of other changes and so would only be done manually.

A profile is a profile type in a specific book. A company can have
multiple profiles in the same book and/or in multiple books. All
companies have at least one profile and are therefore connected to at
least one book.

The queries you gave would give me a company that is not in any books,
but I need a list of all profiles where the company is in a certain
book and not in a certain other book(s).

Example
tblCompanies
lngCompanyId txtCompanyName
1 XYZ
2 ABC

tblProfiles
lngProfileId lngCompanyId intBookId intProfileTypeId
1 1 1 1
2 1 1 2
3 1 2 1
4 1 4 1
5 2 1 1
6 2 3 1

I want all the profile ids where the company has a type 1 profile in
book 1, but does not have a profile in book 2 nor a profile in book 4
which should return profile id 5.

Does this make sense? I'm having trouble explaining it which means
that it really is convoluted or I am making it more difficult than it
needs to be.

Jennifer

 

[Toxalot]

what is the SQL for your report?

SELECT tblProfiles.lngProfileId, tblProfilePeople.lngPersonId,
tblProfilePeople.intRoleId, tblProfiles.intBookId,
tblProfiles.intProfileTypeId, tblCompanies.txtCompanyName,
tblCompanies.txtProvinceCode, tblProfiles.blnAdvertiser
FROM (tblProfiles LEFT JOIN tblCompanies ON
tblProfiles.lngCompanyId = tblCompanies.lngCompanyId)
LEFT JOIN tblProfilePeople ON
tblProfiles.lngProfileId = tblProfilePeople.lngProfileId;

lngProfileId and lngPersonId are used to link to several sub reports,
intRoleId, intBookId, intProfileTypeId, txtProvinceCode, blnAdvertiser
are all (conditionally) used as part of the where clause,
txtCompanyName and txtProvinceCode are (conditionally) used for the
Order By. The where clause is built on the click event of a button on
the form and used to open the report. Which sort to use is sent to the
report in OpenArgs.

This all works fine, but if I try to add in multiple people (that need
to be unique) and not in multiple books, it becomes nightmarish (at
least for me).

Jennifer

 

[Toxalot]

OK. So I think I have the multiple people working. I won't be able to
use a filter on the report because I need to get Distinct lngPersonId,
lngProfileId and filter doesn't work on anything not in the result
set.

I'm building the whole SQL string on the fly and then setting the
report's recordsource to that string.

The only thing left is the "not in book(s)".

I can't see any way around a temp table with all the lngCompanyId that
are in the not books.

Jennifer

 

[strive4peace]

Hi Jennifer,

you can use a subquery in your criteria for companies not in a book...

field --> lngCompanyId
table --> tblProfiles
criteria -->
IN (SELECT
qAllCompaniesBooks.lngCompanyId
FROM qAllCompaniesBooks
LEFT JOIN tblProfiles P
ON qAllCompaniesBooks.lngCompanyId = P.lngCompanyId
WHERE P.lngCompanyId Is Null AND qAllCompaniesBooks.intBookId =
tblProfiles.intBookId)



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*