Re: Simple to Extreme Criteria Queries

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" <CorporateQAinTX@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:9751EB19-1F82-40E1-B8D0-1C8D7E2B8A83@xxxxxxxxxxxxxxxx
Is it possible to use more than one query at a time?

"Pete D." wrote:

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" <CorporateQAinTX@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:CFEC6E02-BB85-4976-AA91-92A2F4C6549F@xxxxxxxxxxxxxxxx
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." wrote:

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.

"CorporateQAinTX" <CorporateQAinTX@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:A507920F-3504-4D0C-AFA8-47D8E08DA36D@xxxxxxxxxxxxxxxx
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?












.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Dynamic query problem
    ... On Oct 17, 9:41 am, Andy Hull ... If we were to provide a fully featured dynamic query generator we would have ... Provided with already built queries which they can edit ... SQL and see where it is the same as for the other queries and where it ...
    (microsoft.public.access.queries)
  • Re: CONTAINS performance
    ... mark, FTS needs to be very carefully tuned to achieve second response times, ... see SQL Server 2000 BOL title "Full-text Search Recommendations" for more ... When you include the "TOP 100" in your query, you are in fact limiting the ... valid for SQL queries, they often do not apply to FTS queries because the FT ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)