Simple to Extreme Criteria Queries

C

CorporateQAinTX

So far everyone has been so helpful. Thanks.

Here goes another issue.

I've built a form for entering parameters into a query. The query is setup
to accept the data entered into the different controls on the form. In all
there are 20 possible variables broken up into 7 sections. Each section can
give a specific parameter or there is an option to enter all (haven't quite
figured out how to set up that in code yet). Most of the choices are based in
Option Groups, so there is only one choice that can be chosen. However, if
you choose a specific option then a combo box is enabled so you can be even
more specific. Or you can leave it blank, which I also think is causing a
problem.

Confused yet?

So you can look for a certain product or generic product type, looking in
all departments or a specific department, on all shifts or a specific one,
and yada yada yada.

I can't figure out if it's possible to make a query that will accept all of
these variables or be able to distinguish if one is null and to disregard it.
Should I just create 15 or 20 different queries? My problem is, what if
someone wants to look at a specific product produced within a time frame from
a certain shift and machine? If I can create a query that will do that as
well as a generic what items are in the table in this time frame it would be
great.

Any suggestions?
 
P

Pete D.

Switch to SQL view and post the code. I think you may have some missing
code as you know from you question, which I also think is causing a problem.
Get some more eyes on the sql code we may be able to see it.
 
C

CorporateQAinTX

SELECT Holdware.TicketNumber, Holdware.DateHeld, Holdware.DefectDescription,
Holdware.Location, Holdware.ProductType, Holdware.ProductCapacityStyle,
Holdware.PreformCode, Holdware.MachineType, Holdware.MachineNumber,
Holdware.ShiftID, Holdware.QuantityHeld, Holdware.Disposition1,
Holdware.TotalDisposition1, Holdware.Disposition2,
Holdware.TotalDisposition2, Holdware.Disposition3,
Holdware.TotalDisposition3, Holdware.DateCompleted, Holdware.Closed
FROM Holdware
WHERE (((Holdware.DateHeld) Between [Forms]![Create A Report]![txtStartDate]
And [Forms]![Create A Report]![txtEndDate]) AND
((Holdware.Location)=[Forms]![Create A Report]![Location]) AND
((Holdware.ProductType)=[Forms]![Create A Report]![ProductType]) AND
((Holdware.ProductCapacityStyle)=[Forms]![Create A Report]![BottleType]) AND
((Holdware.PreformCode)=[Forms]![Create A Report]![PreformType]) AND
((Holdware.MachineType)=[Forms]![Create A Report]![Machine]) AND
((Holdware.MachineNumber)=[Forms]![Create A Report]![MachineNumber]) AND
((Holdware.ShiftID)=[Forms]![Create A Report]![Shift]) AND
((Holdware.Closed)=[Forms]![Create A Report]![TicketStatus]))
ORDER BY Holdware.QuantityHeld DESC;

I'm not very good at SQL. I'm just using the Query Design View, so let me
know if this is useful. Thanks.
 
P

Pete D.

Ouch, a simple query will not work for this as you are over the var numbers.
Can you do it, yes but your going to have to build the SQL in VB/A to make
it work. Me personnally I would reduce the need to by all departments or
(note I didn't say And) then break down departments. Can you do it all with
one query (SQL VBA twist) yes. But presenting all those to the user will
probally confuse them, better to take them down a few levels first. Better
to have 10 queries then one that no one can figure out how to get the info
they need. Department, Date, etc.
CorporateQAinTX said:
SELECT Holdware.TicketNumber, Holdware.DateHeld,
Holdware.DefectDescription,
Holdware.Location, Holdware.ProductType, Holdware.ProductCapacityStyle,
Holdware.PreformCode, Holdware.MachineType, Holdware.MachineNumber,
Holdware.ShiftID, Holdware.QuantityHeld, Holdware.Disposition1,
Holdware.TotalDisposition1, Holdware.Disposition2,
Holdware.TotalDisposition2, Holdware.Disposition3,
Holdware.TotalDisposition3, Holdware.DateCompleted, Holdware.Closed
FROM Holdware
WHERE (((Holdware.DateHeld) Between [Forms]![Create A
Report]![txtStartDate]
And [Forms]![Create A Report]![txtEndDate]) AND
((Holdware.Location)=[Forms]![Create A Report]![Location]) AND
((Holdware.ProductType)=[Forms]![Create A Report]![ProductType]) AND
((Holdware.ProductCapacityStyle)=[Forms]![Create A Report]![BottleType])
AND
((Holdware.PreformCode)=[Forms]![Create A Report]![PreformType]) AND
((Holdware.MachineType)=[Forms]![Create A Report]![Machine]) AND
((Holdware.MachineNumber)=[Forms]![Create A Report]![MachineNumber]) AND
((Holdware.ShiftID)=[Forms]![Create A Report]![Shift]) AND
((Holdware.Closed)=[Forms]![Create A Report]![TicketStatus]))
ORDER BY Holdware.QuantityHeld DESC;

I'm not very good at SQL. I'm just using the Query Design View, so let me
know if this is useful. Thanks.

Pete D. said:
Switch to SQL view and post the code. I think you may have some missing
code as you know from you question, which I also think is causing a
problem.
Get some more eyes on the sql code we may be able to see it.
Click to expand...
Click to expand...
 
C

CorporateQAinTX

Is it possible to use more than one query at a time?

Pete D. said:
Ouch, a simple query will not work for this as you are over the var numbers.
Can you do it, yes but your going to have to build the SQL in VB/A to make
it work. Me personnally I would reduce the need to by all departments or
(note I didn't say And) then break down departments. Can you do it all with
one query (SQL VBA twist) yes. But presenting all those to the user will
probally confuse them, better to take them down a few levels first. Better
to have 10 queries then one that no one can figure out how to get the info
they need. Department, Date, etc.
CorporateQAinTX said:
SELECT Holdware.TicketNumber, Holdware.DateHeld,
Holdware.DefectDescription,
Holdware.Location, Holdware.ProductType, Holdware.ProductCapacityStyle,
Holdware.PreformCode, Holdware.MachineType, Holdware.MachineNumber,
Holdware.ShiftID, Holdware.QuantityHeld, Holdware.Disposition1,
Holdware.TotalDisposition1, Holdware.Disposition2,
Holdware.TotalDisposition2, Holdware.Disposition3,
Holdware.TotalDisposition3, Holdware.DateCompleted, Holdware.Closed
FROM Holdware
WHERE (((Holdware.DateHeld) Between [Forms]![Create A
Report]![txtStartDate]
And [Forms]![Create A Report]![txtEndDate]) AND
((Holdware.Location)=[Forms]![Create A Report]![Location]) AND
((Holdware.ProductType)=[Forms]![Create A Report]![ProductType]) AND
((Holdware.ProductCapacityStyle)=[Forms]![Create A Report]![BottleType])
AND
((Holdware.PreformCode)=[Forms]![Create A Report]![PreformType]) AND
((Holdware.MachineType)=[Forms]![Create A Report]![Machine]) AND
((Holdware.MachineNumber)=[Forms]![Create A Report]![MachineNumber]) AND
((Holdware.ShiftID)=[Forms]![Create A Report]![Shift]) AND
((Holdware.Closed)=[Forms]![Create A Report]![TicketStatus]))
ORDER BY Holdware.QuantityHeld DESC;

I'm not very good at SQL. I'm just using the Query Design View, so let me
know if this is useful. Thanks.

Pete D. said:
Switch to SQL view and post the code. I think you may have some missing
code as you know from you question, which I also think is causing a
problem.
Get some more eyes on the sql code we may be able to see it.

message So far everyone has been so helpful. Thanks.

Here goes another issue.

I've built a form for entering parameters into a query. The query is
setup
to accept the data entered into the different controls on the form. In
all
there are 20 possible variables broken up into 7 sections. Each section
can
give a specific parameter or there is an option to enter all (haven't
quite
figured out how to set up that in code yet). Most of the choices are
based
in
Option Groups, so there is only one choice that can be chosen. However,
if
you choose a specific option then a combo box is enabled so you can be
even
more specific. Or you can leave it blank, which I also think is causing
a
problem.

Confused yet?

So you can look for a certain product or generic product type, looking
in
all departments or a specific department, on all shifts or a specific
one,
and yada yada yada.

I can't figure out if it's possible to make a query that will accept
all
of
these variables or be able to distinguish if one is null and to
disregard
it.
Should I just create 15 or 20 different queries? My problem is, what if
someone wants to look at a specific product produced within a time
frame
from
a certain shift and machine? If I can create a query that will do that
as
well as a generic what items are in the table in this time frame it
would
be
great.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
P

Pete D.

it isn't unheard of to have a query based on other queries or chain table
builds from other queries. There is a response time impact so depends on
amount of data you are dealing with.
CorporateQAinTX said:
Is it possible to use more than one query at a time?

Pete D. said:
Ouch, a simple query will not work for this as you are over the var
numbers.
Can you do it, yes but your going to have to build the SQL in VB/A to
make
it work. Me personnally I would reduce the need to by all departments or
(note I didn't say And) then break down departments. Can you do it all
with
one query (SQL VBA twist) yes. But presenting all those to the user will
probally confuse them, better to take them down a few levels first.
Better
to have 10 queries then one that no one can figure out how to get the
info
they need. Department, Date, etc.
CorporateQAinTX said:
SELECT Holdware.TicketNumber, Holdware.DateHeld,
Holdware.DefectDescription,
Holdware.Location, Holdware.ProductType, Holdware.ProductCapacityStyle,
Holdware.PreformCode, Holdware.MachineType, Holdware.MachineNumber,
Holdware.ShiftID, Holdware.QuantityHeld, Holdware.Disposition1,
Holdware.TotalDisposition1, Holdware.Disposition2,
Holdware.TotalDisposition2, Holdware.Disposition3,
Holdware.TotalDisposition3, Holdware.DateCompleted, Holdware.Closed
FROM Holdware
WHERE (((Holdware.DateHeld) Between [Forms]![Create A
Report]![txtStartDate]
And [Forms]![Create A Report]![txtEndDate]) AND
((Holdware.Location)=[Forms]![Create A Report]![Location]) AND
((Holdware.ProductType)=[Forms]![Create A Report]![ProductType]) AND
((Holdware.ProductCapacityStyle)=[Forms]![Create A
Report]![BottleType])
AND
((Holdware.PreformCode)=[Forms]![Create A Report]![PreformType]) AND
((Holdware.MachineType)=[Forms]![Create A Report]![Machine]) AND
((Holdware.MachineNumber)=[Forms]![Create A Report]![MachineNumber])
AND
((Holdware.ShiftID)=[Forms]![Create A Report]![Shift]) AND
((Holdware.Closed)=[Forms]![Create A Report]![TicketStatus]))
ORDER BY Holdware.QuantityHeld DESC;

I'm not very good at SQL. I'm just using the Query Design View, so let
me
know if this is useful. Thanks.

:

Switch to SQL view and post the code. I think you may have some
missing
code as you know from you question, which I also think is causing a
problem.
Get some more eyes on the sql code we may be able to see it.

message So far everyone has been so helpful. Thanks.

Here goes another issue.

I've built a form for entering parameters into a query. The query is
setup
to accept the data entered into the different controls on the form.
In
all
there are 20 possible variables broken up into 7 sections. Each
section
can
give a specific parameter or there is an option to enter all
(haven't
quite
figured out how to set up that in code yet). Most of the choices are
based
in
Option Groups, so there is only one choice that can be chosen.
However,
if
you choose a specific option then a combo box is enabled so you can
be
even
more specific. Or you can leave it blank, which I also think is
causing
a
problem.

Confused yet?

So you can look for a certain product or generic product type,
looking
in
all departments or a specific department, on all shifts or a
specific
one,
and yada yada yada.

I can't figure out if it's possible to make a query that will accept
all
of
these variables or be able to distinguish if one is null and to
disregard
it.
Should I just create 15 or 20 different queries? My problem is, what
if
someone wants to look at a specific product produced within a time
frame
from
a certain shift and machine? If I can create a query that will do
that
as
well as a generic what items are in the table in this time frame it
would
be
great.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

You could take a look at Allen Browne's solution. It might give you some
ideas on handling multiple criteria.
http://allenbrowne.com/ser-62.html

You can use a form (and VBA) to build the query string on the fly.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
it isn't unheard of to have a query based on other queries or chain table
builds from other queries. There is a response time impact so depends on
amount of data you are dealing with.
CorporateQAinTX said:
Is it possible to use more than one query at a time?

Pete D. said:
Ouch, a simple query will not work for this as you are over the var
numbers.
Can you do it, yes but your going to have to build the SQL in VB/A to
make
it work. Me personnally I would reduce the need to by all departments or
(note I didn't say And) then break down departments. Can you do it all
with
one query (SQL VBA twist) yes. But presenting all those to the user will
probally confuse them, better to take them down a few levels first.
Better
to have 10 queries then one that no one can figure out how to get the
info
they need. Department, Date, etc.
message SELECT Holdware.TicketNumber, Holdware.DateHeld,
Holdware.DefectDescription,
Holdware.Location, Holdware.ProductType, Holdware.ProductCapacityStyle,
Holdware.PreformCode, Holdware.MachineType, Holdware.MachineNumber,
Holdware.ShiftID, Holdware.QuantityHeld, Holdware.Disposition1,
Holdware.TotalDisposition1, Holdware.Disposition2,
Holdware.TotalDisposition2, Holdware.Disposition3,
Holdware.TotalDisposition3, Holdware.DateCompleted, Holdware.Closed
FROM Holdware
WHERE (((Holdware.DateHeld) Between [Forms]![Create A
Report]![txtStartDate]
And [Forms]![Create A Report]![txtEndDate]) AND
((Holdware.Location)=[Forms]![Create A Report]![Location]) AND
((Holdware.ProductType)=[Forms]![Create A Report]![ProductType]) AND
((Holdware.ProductCapacityStyle)=[Forms]![Create A
Report]![BottleType])
AND
((Holdware.PreformCode)=[Forms]![Create A Report]![PreformType]) AND
((Holdware.MachineType)=[Forms]![Create A Report]![Machine]) AND
((Holdware.MachineNumber)=[Forms]![Create A Report]![MachineNumber])
AND
((Holdware.ShiftID)=[Forms]![Create A Report]![Shift]) AND
((Holdware.Closed)=[Forms]![Create A Report]![TicketStatus]))
ORDER BY Holdware.QuantityHeld DESC;

I'm not very good at SQL. I'm just using the Query Design View, so let
me
know if this is useful. Thanks.

:

Switch to SQL view and post the code. I think you may have some
missing
code as you know from you question, which I also think is causing a
problem.
Get some more eyes on the sql code we may be able to see it.

message So far everyone has been so helpful. Thanks.

Here goes another issue.

I've built a form for entering parameters into a query. The query is
setup
to accept the data entered into the different controls on the form.
In
all
there are 20 possible variables broken up into 7 sections. Each
section
can
give a specific parameter or there is an option to enter all
(haven't
quite
figured out how to set up that in code yet). Most of the choices are
based
in
Option Groups, so there is only one choice that can be chosen.
However,
if
you choose a specific option then a combo box is enabled so you can
be
even
more specific. Or you can leave it blank, which I also think is
causing
a
problem.

Confused yet?

So you can look for a certain product or generic product type,
looking
in
all departments or a specific department, on all shifts or a
specific
one,
and yada yada yada.

I can't figure out if it's possible to make a query that will accept
all
of
these variables or be able to distinguish if one is null and to
disregard
it.
Should I just create 15 or 20 different queries? My problem is, what
if
someone wants to look at a specific product produced within a time
frame
from
a certain shift and machine? If I can create a query that will do
that
as
well as a generic what items are in the table in this time frame it
would
be
great.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
C

CorporateQAinTX

That great. Very useful. Thanks for the help!

John Spencer said:
You could take a look at Allen Browne's solution. It might give you some
ideas on handling multiple criteria.
http://allenbrowne.com/ser-62.html

You can use a form (and VBA) to build the query string on the fly.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
it isn't unheard of to have a query based on other queries or chain table
builds from other queries. There is a response time impact so depends on
amount of data you are dealing with.
CorporateQAinTX said:
Is it possible to use more than one query at a time?

:

Ouch, a simple query will not work for this as you are over the var
numbers.
Can you do it, yes but your going to have to build the SQL in VB/A to
make
it work. Me personnally I would reduce the need to by all departments or
(note I didn't say And) then break down departments. Can you do it all
with
one query (SQL VBA twist) yes. But presenting all those to the user will
probally confuse them, better to take them down a few levels first.
Better
to have 10 queries then one that no one can figure out how to get the
info
they need. Department, Date, etc.
message SELECT Holdware.TicketNumber, Holdware.DateHeld,
Holdware.DefectDescription,
Holdware.Location, Holdware.ProductType, Holdware.ProductCapacityStyle,
Holdware.PreformCode, Holdware.MachineType, Holdware.MachineNumber,
Holdware.ShiftID, Holdware.QuantityHeld, Holdware.Disposition1,
Holdware.TotalDisposition1, Holdware.Disposition2,
Holdware.TotalDisposition2, Holdware.Disposition3,
Holdware.TotalDisposition3, Holdware.DateCompleted, Holdware.Closed
FROM Holdware
WHERE (((Holdware.DateHeld) Between [Forms]![Create A
Report]![txtStartDate]
And [Forms]![Create A Report]![txtEndDate]) AND
((Holdware.Location)=[Forms]![Create A Report]![Location]) AND
((Holdware.ProductType)=[Forms]![Create A Report]![ProductType]) AND
((Holdware.ProductCapacityStyle)=[Forms]![Create A
Report]![BottleType])
AND
((Holdware.PreformCode)=[Forms]![Create A Report]![PreformType]) AND
((Holdware.MachineType)=[Forms]![Create A Report]![Machine]) AND
((Holdware.MachineNumber)=[Forms]![Create A Report]![MachineNumber])
AND
((Holdware.ShiftID)=[Forms]![Create A Report]![Shift]) AND
((Holdware.Closed)=[Forms]![Create A Report]![TicketStatus]))
ORDER BY Holdware.QuantityHeld DESC;

I'm not very good at SQL. I'm just using the Query Design View, so let
me
know if this is useful. Thanks.

:

Switch to SQL view and post the code. I think you may have some
missing
code as you know from you question, which I also think is causing a
problem.
Get some more eyes on the sql code we may be able to see it.

message So far everyone has been so helpful. Thanks.

Here goes another issue.

I've built a form for entering parameters into a query. The query is
setup
to accept the data entered into the different controls on the form.
In
all
there are 20 possible variables broken up into 7 sections. Each
section
can
give a specific parameter or there is an option to enter all
(haven't
quite
figured out how to set up that in code yet). Most of the choices are
based
in
Option Groups, so there is only one choice that can be chosen.
However,
if
you choose a specific option then a combo box is enabled so you can
be
even
more specific. Or you can leave it blank, which I also think is
causing
a
problem.

Confused yet?

So you can look for a certain product or generic product type,
looking
in
all departments or a specific department, on all shifts or a
specific
one,
and yada yada yada.

I can't figure out if it's possible to make a query that will accept
all
of
these variables or be able to distinguish if one is null and to
disregard
it.
Should I just create 15 or 20 different queries? My problem is, what
if
someone wants to look at a specific product produced within a time
frame
from
a certain shift and machine? If I can create a query that will do
that
as
well as a generic what items are in the table in this time frame it
would
be
great.

Any suggestions?
Click to expand...
Click to expand...
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

Query with no criteria takes forever 6
Passing criteria to queries from forms 2
Simple Criteria Help 4
Selecting criteria in Queries 1
Multiple Queries / Same Criteria 3
Combining queries that don't match 0
Multiple large queries vs. One big/several small queries 5
Criteria 5

Top