473,856 Members | 1,710 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with using a subquery to filter on multiple tables using asingle form

Hi all--
Does anyone have any insight as to how I might create a search form
that allows a user to select criteria based on any related table in
the whole database. The search form I have now only allows me to
filter based on variables in a single table. I would like to have a
search form where I can select multiple variables (from various linked
tables) to filter by, and return results based on this multi-table
filter.

Allen Browne has a wonderful description of how to create a simple
search form: <http://www.allenbrowne .com/ser-62.html>, and he even
alludes to a way "to filter on other tables that are not even in the
form's RecordSource, use a subquery" -- on his subquery page (<http://
www.allenbrowne .com/subquery-01.html#Search> ), he describes using
subqueries in the context of his simple search form to create a search
form "where the user can select criteria based on any related table in
the whole database". I was wondering if anyone had some thoughts as to
how I might actually code something like that (basically, how to code
a search form similar to that which he has on the bottom of his
subquery-01.html page).

thanks,
-jason
Dec 20 '07 #1
1 4176
Hi Jason

There is a significant amount of code to write to handle such as search
form.

For example, the screenshot shows the Donations, so the code has to visit
each of the contorls you see on that page, and build a WHERE clause to use
in the subquery for the Donations table that covers each of those
conditions. You actually build up the WHERE clause for the subquery in the
same way you built the strWhere string for the search form here:
http://allenbrowne.com/ser-62.html

You then create a massive top-level query statement for the results. Its
WHERE clause contains all the subqueries for the tables the user chose to
put criteria in. So in the end, the query statement ends up like this:
SELECT tblClient.* FROM tblClient
WHERE EXISTS (SELECT DonationID FROM tblDonation
WHERE tblDonation.Cli entID = tblClientID
AND tblDonation.Don ationDate #1/1/2007#)
AND EXISTS (SELECT ...

--
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.

<jc****@gmail.c omwrote in message
news:12******** *************** ***********@d21 g2000prf.google groups.com...
Hi all--
Does anyone have any insight as to how I might create a search form
that allows a user to select criteria based on any related table in
the whole database. The search form I have now only allows me to
filter based on variables in a single table. I would like to have a
search form where I can select multiple variables (from various linked
tables) to filter by, and return results based on this multi-table
filter.

Allen Browne has a wonderful description of how to create a simple
search form: <http://www.allenbrowne .com/ser-62.html>, and he even
alludes to a way "to filter on other tables that are not even in the
form's RecordSource, use a subquery" -- on his subquery page (<http://
www.allenbrowne .com/subquery-01.html#Search> ), he describes using
subqueries in the context of his simple search form to create a search
form "where the user can select criteria based on any related table in
the whole database". I was wondering if anyone had some thoughts as to
how I might actually code something like that (basically, how to code
a search form similar to that which he has on the bottom of his
subquery-01.html page).

thanks,
-jason
Dec 21 '07 #2

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

Similar topics

8
19610
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a couple of tables in my database using INNER JOINS and the WHERE clause to specify the required constraints. However, I also want to read two fields from a *single* record from a table called 'Locations' and then apply one of these field's values...
5
2207
by: Steve Patrick | last post by:
Hi All You guys are my last hope, despite spending money on books and hours reading them I still can not achieve the results I need. I have designed a database in Access 2000 based on 1 table, all has gone very well with one exception. The table is based on applications made by potential customers looking to buy franchise rights to particular locations and as part of the process they are asked to list their preferred locations 1 to 4....
4
3184
by: Kathy | last post by:
What is the standard technique for handling the fields in the following scenario on a continuous form? Multiple Divisions. Each Division has multiple Buildings. Each Building has a Supervisor. Tables: (abbreviated) TblDivision DivisionID
15
2157
by: shaqattack1992-newsgroups | last post by:
Hello Everyone, Let me explain my problem. I have included 2 dashes between each pair of records to make it easier to see what goes together. In reality, it is just a long list of results from my query. Contract--------QTY--------COMPONENT--------LENGTH-------PCS
7
14178
by: Jeff | last post by:
Hi - For my VB.NET app, I have a SQL2K database that I use to create a dataset with multiple data tables. I've created a dataview (dvReportsTo) of one of the tables, SCPMaster, and I've bound a combobox control to that dataview. I'm trying to filter the dataview based on values in a second table (ReportsTo) in the same dataset, and it's not working. If it's possible to do this, please help me figure out what I've done wrong and how...
0
1275
by: lordofhyphens | last post by:
I'm trying to form a correlated subquery (right now, it's MS Access running to linked tables in MS SQL, although I have tried to make the same query against the MS SQL tables directly). The goal in the query below is to filter out near-duplicates. What I have is similar rows in my database, and the differences between the different rows lies in a date field. I want to be able to filter out the "newest" dates
0
3397
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server 2005 and the likes of it. So This one works with SQL2000 What do you think?
0
2462
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in the whole. I really need MySQL wizz to give me a hand (and maybe refer me to books to get me to the wizz level myself). First off, english is a second language to me and sometimes my sentences might be a little awkward. Please forgive me. Mon...
3
2711
by: jcf378 | last post by:
Hi all-- Does anyone have any insight as to how I might create a search form that allows a user to select criteria based on any related table in the whole database. The search form I have now only allows me to filter based on variables in a single table. I would like to have a search form where I can select multiple variables (from various linked tables) to filter by, and return results based on this multi-table filter. Allen Browne has a...
0
9903
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
9755
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11048
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...
0
10376
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
9526
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
7928
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
7084
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
5754
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
3194
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.