query based on lookup value

[Robert]

I am trying to create a db for service providers by county. I'm relatively
new to db programming, but I have done quite a bit of programming ranging
from the old basic days up to doing some programming in the HotDocs
software. I've kind of accomplished my goal in access, but I'm not quite
there yet and figure I've really screwed something up.

The db consists of 5 tables I believe. T1 is the main contact info, with an
autonumber ID Key. T2 is a list of all of the counties in the state. T3 is
a list of the services I want to search for. T2 & T3 both have their own
autonumber ID Keys and a separate column for either the county or the
service depending on the table and nothing else. T4 is a table that has its
own autonumber entry, a column that is linked to a T1 ID Key, and a column
for the counties. The counties are entered in the form view via a lookup
list based on T2. T5 is the same as T4 except that it relates to T1 and T3,
again with a lookup list that looks into T3 for the data.

I created a form (F1) based on T1. F1 uses two child forms (F2 and F3) that
are based on T4 and T5. This causes access to autolink data entered in F2
and F3 to the parent form F1.

Now I want a query that allows me to enter a county name and it will find
the service providers in that county. Ideally, I would like it to do a
double query, the first to find by county and the second to find by service
so that only those providers of a specific service in a particular county
are listed. This is where I'm running into problems.

The first thing I notice when I run my query is that I type in the county I
want to search for and it gives me an error. After some time I figured out
that for some reason the tables are switching the lookup values to numbers.
So, I figured out that I want to find County D and I type in the
corresponding number, 4, and it retrieves those entries of providers in
County D and lists the county by its name and not the number I just
searched. I tried to set up a query using the service table as well and it
did the same thing. I could change my T1 table to include checkboxes for
the info on the services provided if that would make that part of the query
any easier, but with 93 counties, it's kind of difficult to do that with the
T2/T4 data. And if I don't have to, I really don't want to set it up that
way for T3/T5.

The second thing I've noticed is that when I run the query using the number
search, it will find show multiple results for the same provider. Say
provider A provides services X,Y, and Z in Counties 1,2 and 3. If I search
under County 3, it will show three results for provider A, one for each
entry of X,Y, and Z. Would really like this set up so that I only have to
look at one entry for the same provider if possible.

Any suggestions? I've looked through the reference manual I purchased and
haven't totally found my answer. I've searched the newsgroups and am still
at a loss. I would appreciate responses that are specific on the
programming side so that I can understand exactly what is going on. And
thanks in advance.

Robert

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Rob Parker]

Assuming that the autonumber fields in T1, T2 and T3 are named
ContactID, CountyID and ServiceID respectively, the following query
(with appropriate variation in the WHERE clause) will let you enter
the County or Service directly, rather than via the ID number:

SELECT T1.Contact, T2.County, T3.Service
FROM (T2 INNER JOIN (T1 INNER JOIN T4 ON T1.ContactID = T4.ContactID)
ON T2.CountyID = T4.CountyID) INNER JOIN (T3 INNER JOIN T5 ON
T3.ServiceID = T5.ServiceID) ON T1.ContactID = T5.ContactID
WHERE (((T2.County)="county 1")) OR (((T3.Service)="service x"));

If you enter this in the SQL View, and then look at it in the query
grid design view, you'll find it easy to see what I've done. This is
useful if you need to query on both County and Service together; it
will return multiple entries for a particular contact if they have
entries for multiple counties and services. In the QBE grid, entries
in the same criteria row will produce "AND" in the WHERE clause;
entries in different rows will produce "OR" - you may need to enter
the same thing in multiple criteria rows for a particular field to
give required combinations of AND and OR.

To query for Contacts in a particular County, and show each one only
once, you will need to use the DISTINCT keyword, eg:

SELECT DISTINCT T1.Contact, T2.County
FROM (T2 INNER JOIN (T1 INNER JOIN T4 ON T1.ContactID = T4.ContactID)
ON T2.CountyID = T4.CountyID) INNER JOIN (T3 INNER JOIN T5 ON
T3.ServiceID = T5.ServiceID) ON T1.ContactID = T5.ContactID
WHERE (((T2.County)="county 1"));

You can add this either directly in the SQL view, or via the query
properties dialog box (Unique Values = Yes). Note that the Service
field must not appear in the query, otherwise multiple rows will still
appear; if you are working in the QBE grid, simply turn off the "Show"
checkbox for that field.

BTW, your current table/relationship structure will not cater for a
particular contact supplying different services in different counties.
If you need that, then all three fields (ContactID, CountyID and
ServiceID) need to be combined in a single table. I've done that, and
called it T6; the SQL query to return its records is:

SELECT T1.Contact, T2.County, T3.Service
FROM ((T6 INNER JOIN T1 ON T6.ContactID = T1.ContactID) INNER JOIN T2
ON T6.CountyID = T2.CountyID) INNER JOIN T3 ON T6.ServiceID =
T3.ServiceID;

Note that if you set the primary key for this table to contain all
three fields, you will not be able to enter duplicate records (which
would, of course, be pointless); you could use this to trap such
entries from your form in code and tell the users that the entry
already exists.

HTH,

Rob