Parameter Query Problem

C

CollegeTry

This is for a simple database I'm creating at home for my daughter to help
her in her college search.

I have 5 columns in the DB: College Name, and then four columns listing
majors - Animation, Film, Photo, and Video. The College Name column lists
the college names. The major columns have a 1 if the college has that major
and a blank if it does not.

I want to set up a Parameter Query so my daughter can select any combination
of majors, and see which colleges have that combination. For example, if she
wants to see those colleges that have Film, Photo, and Video, I want her to
be able to select those three majors and have Access come back with a list of
colleges that have those three majors only.

I can create a parameter query that selects the colleges that have all four
majors using design view. Here's the SQL statement:

SELECT All2.College, All2.Animation, All2.Film, All2.Photo, All2.Video
FROM All2
WHERE (((All2.Animation)=[Enter 1 for Animation]) AND ((All2.Film)=[Enter 1
for Film]) AND ((All2.Photo)=[Enter 1 for Photo]) AND ((All2.Video)=[Enter 1
for Video]))

But, try as I might, I can't figure out how to create a query statement that
selects any combination of majors.

So, is there an easy way to do this?
 
J

John Spencer

The simplest solution may be

SELECT All2.College, All2.Animation, All2.Film, All2.Photo, All2.Video
FROM All2
WHERE (All2.Animation=[Enter 1 for Animation] or [Enter 1 for Animation] is Null)
AND (All2.Film=[Enter 1 for Film] OR [Enter 1 for Film] is Null
AND (All2.Photo=[Enter 1 for Photo] OR [Enter 1 for Photo])
AND (All2.Video=[Enter 1 for Video] OR [Enter 1 for Video])

Note this is the simplest, not necessarily the best. Access will reformat this
query, but it should still work. If you get a Too Complex error returned then
post back.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
S

scubadiver

I could explain to you what I have done but if you want to give me your
email address I will email it to you.
 
C

CollegeTry

Thanks John. This did the job.

CollegeTry

John Spencer said:
The simplest solution may be

SELECT All2.College, All2.Animation, All2.Film, All2.Photo, All2.Video
FROM All2
WHERE (All2.Animation=[Enter 1 for Animation] or [Enter 1 for Animation] is Null)
AND (All2.Film=[Enter 1 for Film] OR [Enter 1 for Film] is Null
AND (All2.Photo=[Enter 1 for Photo] OR [Enter 1 for Photo])
AND (All2.Video=[Enter 1 for Video] OR [Enter 1 for Video])

Note this is the simplest, not necessarily the best. Access will reformat this
query, but it should still work. If you get a Too Complex error returned then
post back.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
This is for a simple database I'm creating at home for my daughter to help
her in her college search.

I have 5 columns in the DB: College Name, and then four columns listing
majors - Animation, Film, Photo, and Video. The College Name column lists
the college names. The major columns have a 1 if the college has that major
and a blank if it does not.

I want to set up a Parameter Query so my daughter can select any combination
of majors, and see which colleges have that combination. For example, if she
wants to see those colleges that have Film, Photo, and Video, I want her to
be able to select those three majors and have Access come back with a list of
colleges that have those three majors only.

I can create a parameter query that selects the colleges that have all four
majors using design view. Here's the SQL statement:

SELECT All2.College, All2.Animation, All2.Film, All2.Photo, All2.Video
FROM All2
WHERE (((All2.Animation)=[Enter 1 for Animation]) AND ((All2.Film)=[Enter 1
for Film]) AND ((All2.Photo)=[Enter 1 for Photo]) AND ((All2.Video)=[Enter 1
for Video]))

But, try as I might, I can't figure out how to create a query statement that
selects any combination of majors.

So, is there an easy way to do this?
Click to expand...
Click to expand...
 
C

CollegeTry

If there is a nifty way to do this, I'd love to see it. Here's my e-mail
address: (e-mail address removed).

CollegeTry

scubadiver said:
I could explain to you what I have done but if you want to give me your
email address I will email it to you.



CollegeTry said:
This is for a simple database I'm creating at home for my daughter to help
her in her college search.

I have 5 columns in the DB: College Name, and then four columns listing
majors - Animation, Film, Photo, and Video. The College Name column lists
the college names. The major columns have a 1 if the college has that major
and a blank if it does not.

I want to set up a Parameter Query so my daughter can select any combination
of majors, and see which colleges have that combination. For example, if she
wants to see those colleges that have Film, Photo, and Video, I want her to
be able to select those three majors and have Access come back with a list of
colleges that have those three majors only.

I can create a parameter query that selects the colleges that have all four
majors using design view. Here's the SQL statement:

SELECT All2.College, All2.Animation, All2.Film, All2.Photo, All2.Video
FROM All2
WHERE (((All2.Animation)=[Enter 1 for Animation]) AND ((All2.Film)=[Enter 1
for Film]) AND ((All2.Photo)=[Enter 1 for Photo]) AND ((All2.Video)=[Enter 1
for Video]))

But, try as I might, I can't figure out how to create a query statement that
selects any combination of majors.

So, is there an easy way to do this?
Click to expand...
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The "easy" way to do this is the standard way. First you have to
Normalize your table:

ALL2:
College Text
Course Text

Then fill the table:

College Course
======= ======
Harvard Animation
Harvard Biology
Harvard Business Admin
Texas A&M Large Animal Husbandry
Texas A&M Farm Administration
Texas A&M Organic Chemistry
.... etc. ...

Then you write a query like this:

PARAMETERS [Type a list of courses, like this "Biology,History,Math"]
Text;
SELECT College
FROM All2
WHERE Instr([Type a list of courses, like this "Biology,History,Math"],
Course)>0
OR [Type a list of courses, like this "Biology,History,Math"] IS NULL

Note that the PARAMETERS list in quotes is separated by commas and
doesn't have any space between the entries. This format must be used
when running the query. The WHERE clause uses the InStr() "trick,"
documented in the Access Query Sample db (on MS KnowledgeBase site
[somewhere]).

If the user just hits Enter, all the rows in the table will be returned.

The query returns just those colleges that have at least one, or more,
of the parameters, not colleges that have all of the parameters - that's
a query of a different color :).

The advantage of a set up like this is you can add more Courses without
changing the structure of the table and the query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSGN2DYechKqOuFEgEQK3bQCcCjFSV6J1QX0OU+S4uyXd7A4y6BwAnijI
Kp2q+ErUdfXxf1U8rjXAItAl
=uHnP
-----END PGP SIGNATURE-----
If there is a nifty way to do this, I'd love to see it. Here's my e-mail
address: (e-mail address removed).

CollegeTry

scubadiver said:
I could explain to you what I have done but if you want to give me your
email address I will email it to you.



CollegeTry said:
This is for a simple database I'm creating at home for my daughter to help
her in her college search.

I have 5 columns in the DB: College Name, and then four columns listing
majors - Animation, Film, Photo, and Video. The College Name column lists
the college names. The major columns have a 1 if the college has that major
and a blank if it does not.

I want to set up a Parameter Query so my daughter can select any combination
of majors, and see which colleges have that combination. For example, if she
wants to see those colleges that have Film, Photo, and Video, I want her to
be able to select those three majors and have Access come back with a list of
colleges that have those three majors only.

I can create a parameter query that selects the colleges that have all four
majors using design view. Here's the SQL statement:

SELECT All2.College, All2.Animation, All2.Film, All2.Photo, All2.Video
FROM All2
WHERE (((All2.Animation)=[Enter 1 for Animation]) AND ((All2.Film)=[Enter 1
for Film]) AND ((All2.Photo)=[Enter 1 for Photo]) AND ((All2.Video)=[Enter 1
for Video]))

But, try as I might, I can't figure out how to create a query statement that
selects any combination of majors.

So, is there an easy way to do this?
Click to expand...
Click to expand...
Click to expand...
 
C

CollegeTry

Thanks. Worked very nicely. Interesting the number of solutions that have
worked for the same problem.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The "easy" way to do this is the standard way. First you have to
Normalize your table:

ALL2:
College Text
Course Text

Then fill the table:

College Course
======= ======
Harvard Animation
Harvard Biology
Harvard Business Admin
Texas A&M Large Animal Husbandry
Texas A&M Farm Administration
Texas A&M Organic Chemistry
.... etc. ...

Then you write a query like this:

PARAMETERS [Type a list of courses, like this "Biology,History,Math"]
Text;
SELECT College
FROM All2
WHERE Instr([Type a list of courses, like this "Biology,History,Math"],
Course)>0
OR [Type a list of courses, like this "Biology,History,Math"] IS NULL

Note that the PARAMETERS list in quotes is separated by commas and
doesn't have any space between the entries. This format must be used
when running the query. The WHERE clause uses the InStr() "trick,"
documented in the Access Query Sample db (on MS KnowledgeBase site
[somewhere]).

If the user just hits Enter, all the rows in the table will be returned.

The query returns just those colleges that have at least one, or more,
of the parameters, not colleges that have all of the parameters - that's
a query of a different color :).

The advantage of a set up like this is you can add more Courses without
changing the structure of the table and the query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSGN2DYechKqOuFEgEQK3bQCcCjFSV6J1QX0OU+S4uyXd7A4y6BwAnijI
Kp2q+ErUdfXxf1U8rjXAItAl
=uHnP
-----END PGP SIGNATURE-----
If there is a nifty way to do this, I'd love to see it. Here's my e-mail
address: (e-mail address removed).

CollegeTry

scubadiver said:
I could explain to you what I have done but if you want to give me your
email address I will email it to you.



:

This is for a simple database I'm creating at home for my daughter to help
her in her college search.

I have 5 columns in the DB: College Name, and then four columns listing
majors - Animation, Film, Photo, and Video. The College Name column lists
the college names. The major columns have a 1 if the college has that major
and a blank if it does not.

I want to set up a Parameter Query so my daughter can select any combination
of majors, and see which colleges have that combination. For example, if she
wants to see those colleges that have Film, Photo, and Video, I want her to
be able to select those three majors and have Access come back with a list of
colleges that have those three majors only.

I can create a parameter query that selects the colleges that have all four
majors using design view. Here's the SQL statement:

SELECT All2.College, All2.Animation, All2.Film, All2.Photo, All2.Video
FROM All2
WHERE (((All2.Animation)=[Enter 1 for Animation]) AND ((All2.Film)=[Enter 1
for Film]) AND ((All2.Photo)=[Enter 1 for Photo]) AND ((All2.Video)=[Enter 1
for Video]))

But, try as I might, I can't figure out how to create a query statement that
selects any combination of majors.

So, is there an easy way to do this?
Click to expand...
Click to expand...
Click to expand...
 
R

raskew via AccessMonster.com

Neat solution!
I took the liberty of modifying it to display those colleges that
offered at least 3 of the desired courses.
***************************************************************************
PARAMETERS ["Animation,Film,Photo,Video"] Text;
SELECT
College
, Count(College) AS CountOfCollege
FROM
All2
WHERE
(((InStr(["Animation,Film,Photo,Video"],[Course]))>0))
GROUP BY
College
HAVING
(((Count(College))>=3));
***************************************************************************
I actually tested this using a query based on Northwind. It seemed
to perform as intended.
PARAMETERS ["Ikura,Geitost,Tofu,Chang,Pavlova"] Text;
SELECT
Orders.OrderID
, Count(Orders.OrderID) AS CountOfOrderID
FROM
(Orders
LEFT JOIN
[Order Details]
ON
Orders.OrderID = [Order Details].OrderID)
LEFT JOIN
Products
ON
[Order Details].ProductID = Products.ProductID
WHERE
(((InStr(["Ikura,Geitost,Tofu,Chang,Pavlova"],[ProductName]))>0))
GROUP BY
Orders.OrderID
HAVING
(((Count(Orders.OrderID))>=2));
***************************************************************************
In playing with this, found an interesting alternative which you might
want to take a look at:
http://www.fabalou.com/Access/Queries/MultipleParameters.asp

Thanks again for the neat solution.

Bob said:
The "easy" way to do this is the standard way. First you have to
Normalize your table:

ALL2:
College Text
Course Text

Then fill the table:

College Course
======= ======
Harvard Animation
Harvard Biology
Harvard Business Admin
Texas A&M Large Animal Husbandry
Texas A&M Farm Administration
Texas A&M Organic Chemistry
... etc. ...

Then you write a query like this:

PARAMETERS [Type a list of courses, like this "Biology,History,Math"]
Text;
SELECT College
FROM All2
WHERE Instr([Type a list of courses, like this "Biology,History,Math"],
Course)>0
OR [Type a list of courses, like this "Biology,History,Math"] IS NULL

Note that the PARAMETERS list in quotes is separated by commas and
doesn't have any space between the entries. This format must be used
when running the query. The WHERE clause uses the InStr() "trick,"
documented in the Access Query Sample db (on MS KnowledgeBase site
[somewhere]).

If the user just hits Enter, all the rows in the table will be returned.

The query returns just those colleges that have at least one, or more,
of the parameters, not colleges that have all of the parameters - that's
a query of a different color :).

The advantage of a set up like this is you can add more Courses without
changing the structure of the table and the query.
If there is a nifty way to do this, I'd love to see it. Here's my e-mail
address: (e-mail address removed).
Click to expand...
[quoted text clipped - 31 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update a field by inputting data in a different table 1
Please Help - Pivot Table Trouble 0
Pivot Table Averages 0
Distinct values 1
Counting Text Entries In Query 1
Create a Not In query, but show only certain records 3
linking fields from different tables in same database 4
Linking fields in different table in same database 1

Top