473,705 Members | 2,333 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

query based on lookup value

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 =----
Nov 13 '05 #1
1 3251
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
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
471
by: John Bailo | last post by:
I wrote a webservice to output a report file. The fields of the report are formatted based on information in an in-memory XmlDocument. As each row of a SqlDataReader are looped through, a lookup is done, and format information retrieved. The performance was extremely poor -- producing about 1000 rows per minute. However, when I used tracing/logging, my results were inconclusive. First of all, based on the size of the data and the...
3
6611
by: Richard | last post by:
Hi, I have a form based on a table. When I filter the form I want to run a report based on the same table with the same filter as the form. No problem until I want to filter a combo box where the text value is on a different table. The me.filter is then a text instead of the id-number from the lookup table. This causes the report to prompt for the parameter. How do I get by this problem? Do I need to create a temporary table? I rather...
3
1765
by: JC Mugs | last post by:
Help needed for project-Access 2002(office xp) PROBLEM: Figuring out how to lookup a record and DDate field in Table1 - Take that DDate-field data from record looked up and assign it to Date field in a new record on a form using Table2. BASIC Table1 Fields: DID -Primary KEY DDate --Information I need to move to new form
3
579
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to make a query that selects from a table as desribed below .. I have a table (Volunteer) that has a member field (memnumber) and a number of fields that are headed in various categories and are yes/no formated
2
1575
by: Tom | last post by:
Hi all: I've run into a problem that is confounding me. Hope you can help. I have the following query (having removed alot of stuff for clarity: SELECT tblComponent.TagNumber, First(nz(tblcomponent.CompStream,"N/A")) AS CompStream, ... FROM ... WHERE ...
6
4845
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time
4
1784
by: Geoff | last post by:
I need to produce a report based on a query. Cost is a calculated field and its value is dependent on another field, in the query, called Session. There are 5 different Session codes each generating a different Cost. What is the simplest way to set the correct Cost, in this calculated field,
2
3996
by: dath | last post by:
Hi, Searched the forum and found a lot on passing form input to aquery but not the other way around. Here is the situation: I have a timesheet form based on a subform query that asks the user for the Month and their last name. The fields in the table being updated include: FY, Month, Employee ID, Task Number, hours. The query then displays the results in data sheet view on the form. I have been asked by users to display total they...
3
1623
by: shorti | last post by:
Is there a way to use SELECT to reassign the value of a column so it displays differently? For instance, I have a column called status. It is an integer value in the database (i.e. valid values are 1, 2, 3, 4). I want to display them for what them mean (i.e. 1 = NORMAL, 2 = DEGRADED, 3 = ERROR, 4 = DEAD). So I want to display them for the name or maybe an initial (N = normal). So if my table looks like this
0
8767
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9273
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9030
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8979
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7893
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6605
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5933
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4439
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
2081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.