Why's my query doing this?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Let me preface by saying I am a relative novice at Access and I'm having
trouble with some Queries I created.

My database contains a lists of defects that are reported for our product.
The defects are grouped into categories (manufacturing issue, labelling,
etc). I want to create a query that will total the # of defects in each
category during a user specified time period. I created a form which included
a calendar for user input of start and end dates. The form then returns those
dates to Query#1 which then correctly shows all the defects for the specified
time period. Query#2 is then supposed to calculate the # of defects per
category using the results from Query#1. Query#2 works but it always asks for
the Start and End dates and I'm not sure why it is asking for this as I
thought it would simply use the results from Query#1 and not require reentry
of the dates.

Query#1 SQL:
SELECT OOS.OOSType, OOS.Date
FROM OOS
WHERE (((OOS.Date) Between [Forms]![frmDateFilter]![StartDate] And
[Forms]![frmDateFilter]![EndDate]));

Query #2 SQL:
SELECT NewQry.OOSType, Count(*) AS OOSTypeCount
FROM NewQry
GROUP BY NewQry.OOSType;

What have I done wrong?
TIA
 
"asks for the Start and End dates" you don't have anything in your sql that
would ask for "Start" and "End" dates. It might ask for
Forms!frmDateFilter!StartDate if the form was closed.

Also, you have "Query#1" but your Query#2 uses "NewQry". This might all be
picky but we all deserve some accuracy in questions. We shouldn't have to
work to make guesses.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why not simplify like this:

PARAMETERS [Forms]![frmDateFilter]![StartDate] DateTime,
[Forms]![frmDateFilter]![EndDate] DateTime;
SELECT NewQry.OOSType, Count(*) AS OOSTypeCount
FROM OOS
WHERE [Date] Between [Forms]![frmDateFilter]![StartDate] And
[Forms]![frmDateFilter]![EndDate]
GROUP BY OOSType

BTW, [Date] is a lousy column name. It begs the question what date:
sales date, manufacture date, reject date, inspection date, ... ? Also,
Date() is a built-in VBA function and can sometimes cause confusion when
trying to debug a query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRDWGxIechKqOuFEgEQIMzwCgvYbr+kb3IHRgbjL84JL31LO7n8EAnjLy
rdm4W5IN60hPEdCkgPrduXNE
=eIxz
-----END PGP SIGNATURE-----
 
Back
Top