probably not as complex as I'm thinking...

[rgrantz]

I've been sitting here making queries, rewports, and subreports for about 2
hours, and thought maybe there was a very simple answer to what I need to
do:

I have a table (ItemDetail) with UnitID (unique identifier). In this table
is also DateProduced (date unit was manufactured)

I have a second table (QCData) which has UnitID as relational tie to
ItemDetail (many to one relationship; there are many entries in QCData
relating to one entry in ItemDetail). In QCData is also Defect, Qty (for
number of that defect found), and Solution (what was done to repair defect).
There can be many defects per UnitID. Each defect has a Qty AND Solution
UNLESS the Defect is the number standing for "No Defects."

Ex. in ItemDetail table:

UnitID DateProduced
1234 2/1/05
3456 2/2/05


ex in QCData table:
UnitID Defect Qty Solution
1234 crack 3 Reseal
1234 discoloration 1 Repaint
1234 wrong part 1 Remake

So, UnitID 1234 has a total of 4 defects that did NOT result in a remake,
and 1 instance of a Remake

There are four main focal points of reporting:

Total UnitID's produced
Total Instances of Solution = 1 (complete remake)
Total Instances of Solution = 2 (3/4 remake)
Total Instances of Defects (Sum of Qty in QCData) whose solutions were NOT 1
or 2

I need to run a report that, in any given date range shows how many UnitID's
TOTAL were produced per day (DateProduced is a date range parameter from
reporting form), how many TOTAL defects were found that day (not per UnitID)
whose Solutions were NOT 1 or 2, how many instances of Solution = 1, and how
many instances of Solution = 2.

My Grouping is by DateProduced

ex of Report (one day):

----1/2/05----
Total Units: 25 (Count of all UnitID's with DateProduced = 1/2/05)
Total Complete Remakes (Count of all QCData records with Solution = 1,
and DateProduced for all ItemID's is 1/2/05): 4
Total 3/4/ Remakes (same as above, only Solution = 2): 3
Total defects (Sum of the Qty field in QCData where that Defect did NOT
have a Solution of 1 or 3): 38

I have tried making 3 separate queries to get all the appropriate counts,
but then when they are put together in one query, I only get the number of
records where ALL their criteria match, so I'm probably not thinking about
the query construction right.

Can anyone help me get the query structure together so I can do this?

Thanks for any help

 

[MGFoster]

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

I believe your criteria is really the Date range of the report. The
other "criteria" can be designated in the SELECT clause. Here's a try:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT I.DateProduced,
Count(*) As TotalUnits,
Sum(IIf(D.Solution = 1, D.Qty) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty) As OtherDefects
FROM ItemDetail As I INNER JOIN QCData As D
ON I.UnitID = D.UnitID
WHERE I.DateProduced Between [Begin Date] And [End Date]
GROUP BY I.DateProduced

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiTGQYechKqOuFEgEQLZ0wCdEJEf8kd41GVJAyTttqf9LHpvqx0AniBV
YqchtcI00RMgLeRilOv3k6Yt
=WuqQ
-----END PGP SIGNATURE-----

 

[rgrantz]

I get a syntax error, missing operator when trying to put your suggestion in
SQL view and save the query.

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

I believe your criteria is really the Date range of the report. The
other "criteria" can be designated in the SELECT clause. Here's a try:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT I.DateProduced,
Count(*) As TotalUnits,
Sum(IIf(D.Solution = 1, D.Qty) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty) As OtherDefects
FROM ItemDetail As I INNER JOIN QCData As D
ON I.UnitID = D.UnitID
WHERE I.DateProduced Between [Begin Date] And [End Date]
GROUP BY I.DateProduced

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiTGQYechKqOuFEgEQLZ0wCdEJEf8kd41GVJAyTttqf9LHpvqx0AniBV
YqchtcI00RMgLeRilOv3k6Yt
=WuqQ
-----END PGP SIGNATURE-----

I've been sitting here making queries, rewports, and subreports for about 2
hours, and thought maybe there was a very simple answer to what I need to
do:

I have a table (ItemDetail) with UnitID (unique identifier). In this table
is also DateProduced (date unit was manufactured)

I have a second table (QCData) which has UnitID as relational tie to
ItemDetail (many to one relationship; there are many entries in QCData
relating to one entry in ItemDetail). In QCData is also Defect, Qty (for
number of that defect found), and Solution (what was done to repair defect).
There can be many defects per UnitID. Each defect has a Qty AND Solution
UNLESS the Defect is the number standing for "No Defects."

Ex. in ItemDetail table:

UnitID DateProduced
1234 2/1/05
3456 2/2/05


ex in QCData table:
UnitID Defect Qty Solution
1234 crack 3 Reseal
1234 discoloration 1 Repaint
1234 wrong part 1 Remake

So, UnitID 1234 has a total of 4 defects that did NOT result in a remake,
and 1 instance of a Remake

There are four main focal points of reporting:

Total UnitID's produced
Total Instances of Solution = 1 (complete remake)
Total Instances of Solution = 2 (3/4 remake)
Total Instances of Defects (Sum of Qty in QCData) whose solutions were NOT 1
or 2

I need to run a report that, in any given date range shows how many UnitID's
TOTAL were produced per day (DateProduced is a date range parameter from
reporting form), how many TOTAL defects were found that day (not per UnitID)
whose Solutions were NOT 1 or 2, how many instances of Solution = 1, and how
many instances of Solution = 2.

My Grouping is by DateProduced

ex of Report (one day):

----1/2/05----
Total Units: 25 (Count of all UnitID's with DateProduced = 1/2/05)
Total Complete Remakes (Count of all QCData records with Solution = 1,
and DateProduced for all ItemID's is 1/2/05): 4
Total 3/4/ Remakes (same as above, only Solution = 2): 3
Total defects (Sum of the Qty field in QCData where that Defect did NOT
have a Solution of 1 or 3): 38

I have tried making 3 separate queries to get all the appropriate counts,
but then when they are put together in one query, I only get the number of
records where ALL their criteria match, so I'm probably not thinking about
the query construction right.

 

[MGFoster]

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

I forgot to put the ending parenthesis on the Sum() function. Sorry.
Here is the correction:

Sum(IIf(D.Solution = 1, D.Qty)) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty)) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty)) As OtherDefects


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiYhgIechKqOuFEgEQKnqACePxWvyEMbXq339QZm2qgN6rp6Jk8AoMQ7
/n9wl1f6QmHBlxER/xZ0iNx2
=bbMl
-----END PGP SIGNATURE-----

I get a syntax error, missing operator when trying to put your suggestion in
SQL view and save the query.

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

I believe your criteria is really the Date range of the report. The
other "criteria" can be designated in the SELECT clause. Here's a try:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT I.DateProduced,
Count(*) As TotalUnits,
Sum(IIf(D.Solution = 1, D.Qty) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty) As OtherDefects
FROM ItemDetail As I INNER JOIN QCData As D
ON I.UnitID = D.UnitID
WHERE I.DateProduced Between [Begin Date] And [End Date]
GROUP BY I.DateProduced

 

[MGFoster]

Please show the querys that work. Perhaps I can work something out.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Hi again.

I appreciate your help with this, please don't give up on me:>).

The results returned a HUGE number in each column, like thousands more than
actually exist in the tables. I deleted all the fields except the
DateProduced (the one with GroupBy and the one with the criteria) and the
TotalUnits to see if I was somehow summing the Qty fields wrong, but still
got thousands and thousands more per day than were actually in the table.

I wanted to make sure I was clear about the difference between Qty and the
number of instances of the 1 and 2. I need to show:

- Total Units Made per Day in date range (Count of unique UnitD in
ItemDetail table per day)
- Total Instances of Remakes (Count of Solution = 1 from QCData, NOT Sum of
Qty. Qty is the number of each defect type found on that item which may
have RESULTED in Solution being 1)
- Total Instances of 3/4 Remakes (Count of Solution = 2 from QCData, NOT Sum
of Qty. See above)
- Total Defects whose Solutions were not 1 or 2 (this one IS a Sum of Qty,
summing the Qty for all defects whose Solutions were NOT 1 or 2)

And, the Date grouping seems to already be handled from your example.

Does this complicate things enough so as to be impossible? I have no
problem with making multiple queries to get to this point, but when I try to
combine the ones I made (3 separate queries, which work), I don't get all
the data because in the combination ALL the criteria across all 3 queries
are used by Access, so I don't see any dates where there was no Solution =
1, for instance, even though there were defects, just not ones that resulted
in a Solution of 1. In my combined query, I only see dates and counts where
there were defects AND Solutions of 1 AND Solutions of 2.

If I'm not being very clear, I can put this db somewhere for download

Thanks again, I appreciate your efforts

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

I forgot to put the ending parenthesis on the Sum() function. Sorry.
Here is the correction:

Sum(IIf(D.Solution = 1, D.Qty)) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty)) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty)) As OtherDefects


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiYhgIechKqOuFEgEQKnqACePxWvyEMbXq339QZm2qgN6rp6Jk8AoMQ7
/n9wl1f6QmHBlxER/xZ0iNx2
=bbMl
-----END PGP SIGNATURE-----

I get a syntax error, missing operator when trying to put your suggestion
in
SQL view and save the query.





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

I believe your criteria is really the Date range of the report. The
other "criteria" can be designated in the SELECT clause. Here's a try:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT I.DateProduced,
Count(*) As TotalUnits,
Sum(IIf(D.Solution = 1, D.Qty) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty) As OtherDefects

FROM ItemDetail As I INNER JOIN QCData As D

ON I.UnitID = D.UnitID
WHERE I.DateProduced Between [Begin Date] And [End Date]
GROUP BY I.DateProduced

 

[rgrantz]

Hi again.

I appreciate your help with this, please don't give up on me:>).

The results returned a HUGE number in each column, like thousands more than
actually exist in the tables. I deleted all the fields except the
DateProduced (the one with GroupBy and the one with the criteria) and the
TotalUnits to see if I was somehow summing the Qty fields wrong, but still
got thousands and thousands more per day than were actually in the table.

I wanted to make sure I was clear about the difference between Qty and the
number of instances of the 1 and 2. I need to show:

- Total Units Made per Day in date range (Count of unique UnitD in
ItemDetail table per day)
- Total Instances of Remakes (Count of Solution = 1 from QCData, NOT Sum of
Qty. Qty is the number of each defect type found on that item which may
have RESULTED in Solution being 1)
- Total Instances of 3/4 Remakes (Count of Solution = 2 from QCData, NOT Sum
of Qty. See above)
- Total Defects whose Solutions were not 1 or 2 (this one IS a Sum of Qty,
summing the Qty for all defects whose Solutions were NOT 1 or 2)

And, the Date grouping seems to already be handled from your example.

Does this complicate things enough so as to be impossible? I have no
problem with making multiple queries to get to this point, but when I try to
combine the ones I made (3 separate queries, which work), I don't get all
the data because in the combination ALL the criteria across all 3 queries
are used by Access, so I don't see any dates where there was no Solution =
1, for instance, even though there were defects, just not ones that resulted
in a Solution of 1. In my combined query, I only see dates and counts where
there were defects AND Solutions of 1 AND Solutions of 2.

If I'm not being very clear, I can put this db somewhere for download

Thanks again, I appreciate your efforts

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

I forgot to put the ending parenthesis on the Sum() function. Sorry.
Here is the correction:

Sum(IIf(D.Solution = 1, D.Qty)) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty)) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty)) As OtherDefects


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiYhgIechKqOuFEgEQKnqACePxWvyEMbXq339QZm2qgN6rp6Jk8AoMQ7
/n9wl1f6QmHBlxER/xZ0iNx2
=bbMl
-----END PGP SIGNATURE-----

I get a syntax error, missing operator when trying to put your suggestion
in
SQL view and save the query.

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

I believe your criteria is really the Date range of the report. The
other "criteria" can be designated in the SELECT clause. Here's a try:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT I.DateProduced,
Count(*) As TotalUnits,
Sum(IIf(D.Solution = 1, D.Qty) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty) As OtherDefects
FROM ItemDetail As I INNER JOIN QCData As D
ON I.UnitID = D.UnitID
WHERE I.DateProduced Between [Begin Date] And [End Date]
GROUP BY I.DateProduced

 

[rgrantz]

OK, I have 4 queries, the SQL view of which I have below, with brief
explanations. Please understand that I use the Query Design Grid to make my
queries, as I'm not so fluent with SQL; what is below is just what I copied
out of the SQL view:


qryItemTotal: This gives me the total count of Items produced per day in
the date range:

SELECT DISTINCT Count(ItemDetail.UnitID) AS CountOfUnitID,
ItemDetail.DateProduced
FROM ItemDetail
WHERE (((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryRemakes: This gives me the COUNT of "Solution = 1" records per day in
the date range from the form

SELECT Count(ItemDetail.UnitID) AS CountOfUnitID, ItemDetail.DateProduced
FROM ItemDetail LEFT JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((QCData.Solution)=1) AND ((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryPartialRemake: This gives me COUNT of "Solution = 2" records per day in
the date range of the form

SELECT Count(QCData.UnitID) AS CountOfUnitID, ItemDetail.DateProduced
FROM ItemDetail LEFT JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((QCData.SolutionNum)=2) AND ((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryTotalErrors: This gives me a SUM of the Quantity of all errors found
whose solutions was NOT 1, 2, or 10

SELECT Sum(QCData.Qty) AS SumOfQty, ItemDetail.DateProduced
FROM ItemDetail INNER JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]) AND ((QCData.Solution)<>10 And
(QCData.Solution)<>1 And (QCData.Solution)<>2))
GROUP BY ItemDetail.DateProduced;


Now, each of these queries obviously can have a different number of records,
with different dates. So, when I attempt to combine them in a separate
query, I don't get all records returned.

For instance, if there are no Solution = 1 on 1/5/05, I won't see 1/5/05 in
the combined query, even though there ARE other errors or records from the
other 2 queries. I see:

Date CountOfUnitID Remake 3/4Remake
OtherErrors
1/3/05 43 2
1 65
1/11/05 55 1
3 45

When what I NEED to see is:

Date CountOfUnitID Remake 3/4Remake
OtherErrors
1/3/05 43 2
1 65
1/4/05 32 0
2 56
1/5/05 70 0
1 34
1/6/05 39 0
0 60

etc.

In the combined query, whenever one of the recordsource queries has a null
set for a particular day (no rows), that date won't show up in the combined
query either. I need to somehow have "0" show up, but it won't in my 4
queries, because some dates in each query may have no rows for a particular
date in the date range (ie. not reurning a 0, but having a null set, or no
rows period).

So how can I get to combine this data to make a recordsource for my report
that will show "0's" when there is no data for that particular count for
that particular date (and I'm grouping by Date in the report, by the way,
which is why I need to have the DateProduced included in some or one or all
of these queries).


Thanks again, I appreciate the consideration and input

Please show the querys that work. Perhaps I can work something out.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Hi again.

I appreciate your help with this, please don't give up on me:>).

The results returned a HUGE number in each column, like thousands more than
actually exist in the tables. I deleted all the fields except the
DateProduced (the one with GroupBy and the one with the criteria) and the
TotalUnits to see if I was somehow summing the Qty fields wrong, but still
got thousands and thousands more per day than were actually in the table.

I wanted to make sure I was clear about the difference between Qty and the
number of instances of the 1 and 2. I need to show:

- Total Units Made per Day in date range (Count of unique UnitD in
ItemDetail table per day)
- Total Instances of Remakes (Count of Solution = 1 from QCData, NOT Sum of
Qty. Qty is the number of each defect type found on that item which may
have RESULTED in Solution being 1)
- Total Instances of 3/4 Remakes (Count of Solution = 2 from QCData, NOT Sum
of Qty. See above)
- Total Defects whose Solutions were not 1 or 2 (this one IS a Sum of Qty,
summing the Qty for all defects whose Solutions were NOT 1 or 2)

And, the Date grouping seems to already be handled from your example.

Does this complicate things enough so as to be impossible? I have no
problem with making multiple queries to get to this point, but when I try to
combine the ones I made (3 separate queries, which work), I don't get all
the data because in the combination ALL the criteria across all 3 queries
are used by Access, so I don't see any dates where there was no Solution =
1, for instance, even though there were defects, just not ones that resulted
in a Solution of 1. In my combined query, I only see dates and counts where
there were defects AND Solutions of 1 AND Solutions of 2.

If I'm not being very clear, I can put this db somewhere for download

Thanks again, I appreciate your efforts

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

I forgot to put the ending parenthesis on the Sum() function. Sorry.
Here is the correction:

Sum(IIf(D.Solution = 1, D.Qty)) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty)) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty)) As OtherDefects


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiYhgIechKqOuFEgEQKnqACePxWvyEMbXq339QZm2qgN6rp6Jk8AoMQ7
/n9wl1f6QmHBlxER/xZ0iNx2
=bbMl
-----END PGP SIGNATURE-----

rgrantz wrote:

I get a syntax error, missing operator when trying to put your suggestion
in
SQL view and save the query.





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

I believe your criteria is really the Date range of the report. The
other "criteria" can be designated in the SELECT clause. Here's a try:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT I.DateProduced,
Count(*) As TotalUnits,
Sum(IIf(D.Solution = 1, D.Qty) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty) As OtherDefects

FROM ItemDetail As I INNER JOIN QCData As D

ON I.UnitID = D.UnitID
WHERE I.DateProduced Between [Begin Date] And [End Date]
GROUP BY I.DateProduced

 

[MGFoster]

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

Let's try the following. I'm using an undocumented feature of JET SQL -

the square brackets around a derived table in the FROM clause. Inside
those square brackets there cannot be any other square brackets, like
around [Form]. Also, sometimes Access chokes when this query is viewed
in the Design view. It won't run if you view it in design, so save the
query while the SQL view is showing (if it works ;-) ).

The "1 As Units" is a trick to get 1 for each unique UnitID. Then the
Sum(C.Units) adds up all those 1s from the derived table to get the
count of individual units in the date range.

I don't know if this will work, but give it a try.

SELECT D.DateProduced
Sum(C.Units) As TotalUnits ,
Count(IIf(Q.Solution=1,D.UnitID)) AS Solution1,
Count(IIf(Q.Solution=2,D.UnitID)) AS Solution2,
Sum(IIf(Q.Solution Not In (1,2,10),Q.Qty)) AS OtherSolutions

FROM (ItemDetail As D LEFT JOIN QCData As Q
ON D.UnitID = D.UnitID)
INNER JOIN

[ SELECT UnitID, 1 As Units
FROM ItemDetail
WHERE DateProduced Between
Forms!frmInspectionReportDates!StartDate
And Forms!frmInspectionReportDates!EndDate
GROUP BY UnitID, ]. As C
ON D.UnitID = C.UnitID

WHERE D.DateProduced Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]

GROUP BY D.DateProduced

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


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

iQA/AwUBQiex2YechKqOuFEgEQIyXwCg+G/R0XyqVssHr/1b6bHVg+s7Cl8AoL33
mNUtKliLpQr+LDUODsOMwCle
=OpW6
-----END PGP SIGNATURE-----

OK, I have 4 queries, the SQL view of which I have below, with brief
explanations. Please understand that I use the Query Design Grid to make my
queries, as I'm not so fluent with SQL; what is below is just what I copied
out of the SQL view:


qryItemTotal: This gives me the total count of Items produced per day in
the date range:

SELECT DISTINCT Count(ItemDetail.UnitID) AS CountOfUnitID,
ItemDetail.DateProduced
FROM ItemDetail
WHERE (((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryRemakes: This gives me the COUNT of "Solution = 1" records per day in
the date range from the form

SELECT Count(ItemDetail.UnitID) AS CountOfUnitID, ItemDetail.DateProduced
FROM ItemDetail LEFT JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((QCData.Solution)=1) AND ((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryPartialRemake: This gives me COUNT of "Solution = 2" records per day in
the date range of the form

SELECT Count(QCData.UnitID) AS CountOfUnitID, ItemDetail.DateProduced
FROM ItemDetail LEFT JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((QCData.SolutionNum)=2) AND ((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryTotalErrors: This gives me a SUM of the Quantity of all errors found
whose solutions was NOT 1, 2, or 10

SELECT Sum(QCData.Qty) AS SumOfQty, ItemDetail.DateProduced
FROM ItemDetail INNER JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]) AND ((QCData.Solution)<>10 And
(QCData.Solution)<>1 And (QCData.Solution)<>2))
GROUP BY ItemDetail.DateProduced;


Now, each of these queries obviously can have a different number of records,
with different dates. So, when I attempt to combine them in a separate
query, I don't get all records returned.

For instance, if there are no Solution = 1 on 1/5/05, I won't see 1/5/05 in
the combined query, even though there ARE other errors or records from the
other 2 queries. I see:

Date CountOfUnitID Remake 3/4Remake
OtherErrors
1/3/05 43 2
1 65
1/11/05 55 1
3 45

When what I NEED to see is:

Date CountOfUnitID Remake 3/4Remake
OtherErrors
1/3/05 43 2
1 65
1/4/05 32 0
2 56
1/5/05 70 0
1 34
1/6/05 39 0
0 60

etc.

In the combined query, whenever one of the recordsource queries has a null
set for a particular day (no rows), that date won't show up in the combined
query either. I need to somehow have "0" show up, but it won't in my 4
queries, because some dates in each query may have no rows for a particular
date in the date range (ie. not reurning a 0, but having a null set, or no
rows period).

So how can I get to combine this data to make a recordsource for my report
that will show "0's" when there is no data for that particular count for
that particular date (and I'm grouping by Date in the report, by the way,
which is why I need to have the DateProduced included in some or one or all
of these queries).


Thanks again, I appreciate the consideration and input

Please show the querys that work. Perhaps I can work something out.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


than

the

still

table.

the

of

Sum

Qty,

try to

combine the ones I made (3 separate queries, which work), I don't get

all

the data because in the combination ALL the criteria across all 3

queries

are used by Access, so I don't see any dates where there was no Solution

=

1, for instance, even though there were defects, just not ones that

resulted

in a Solution of 1. In my combined query, I only see dates and counts

where

there were defects AND Solutions of 1 AND Solutions of 2.

If I'm not being very clear, I can put this db somewhere for download

Thanks again, I appreciate your efforts





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

I forgot to put the ending parenthesis on the Sum() function. Sorry.
Here is the correction:

Sum(IIf(D.Solution = 1, D.Qty)) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty)) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty)) As OtherDefects


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiYhgIechKqOuFEgEQKnqACePxWvyEMbXq339QZm2qgN6rp6Jk8AoMQ7
/n9wl1f6QmHBlxER/xZ0iNx2
=bbMl
-----END PGP SIGNATURE-----

rgrantz wrote:


I get a syntax error, missing operator when trying to put your

suggestion

in
SQL view and save the query.






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

I believe your criteria is really the Date range of the report. The
other "criteria" can be designated in the SELECT clause. Here's a

try:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT I.DateProduced,
Count(*) As TotalUnits,
Sum(IIf(D.Solution = 1, D.Qty) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty) As OtherDefects

FROM ItemDetail As I INNER JOIN QCData As D


ON I.UnitID = D.UnitID
WHERE I.DateProduced Between [Begin Date] And [End Date]
GROUP BY I.DateProduced

 

[rgrantz]

No, sorry, I get a syntax error in Group By clause here, and I tried not
using the commas, etc., still didn't work.

Thanks for trying.


Does anyone have any other ideas?

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

Let's try the following. I'm using an undocumented feature of JET SQL -

the square brackets around a derived table in the FROM clause. Inside
those square brackets there cannot be any other square brackets, like
around [Form]. Also, sometimes Access chokes when this query is viewed
in the Design view. It won't run if you view it in design, so save the
query while the SQL view is showing (if it works ;-) ).

The "1 As Units" is a trick to get 1 for each unique UnitID. Then the
Sum(C.Units) adds up all those 1s from the derived table to get the
count of individual units in the date range.

I don't know if this will work, but give it a try.

SELECT D.DateProduced
Sum(C.Units) As TotalUnits ,
Count(IIf(Q.Solution=1,D.UnitID)) AS Solution1,
Count(IIf(Q.Solution=2,D.UnitID)) AS Solution2,
Sum(IIf(Q.Solution Not In (1,2,10),Q.Qty)) AS OtherSolutions

FROM (ItemDetail As D LEFT JOIN QCData As Q
ON D.UnitID = D.UnitID)
INNER JOIN

[ SELECT UnitID, 1 As Units
FROM ItemDetail
WHERE DateProduced Between
Forms!frmInspectionReportDates!StartDate
And Forms!frmInspectionReportDates!EndDate
GROUP BY UnitID, ]. As C
ON D.UnitID = C.UnitID

WHERE D.DateProduced Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]

GROUP BY D.DateProduced

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


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

iQA/AwUBQiex2YechKqOuFEgEQIyXwCg+G/R0XyqVssHr/1b6bHVg+s7Cl8AoL33
mNUtKliLpQr+LDUODsOMwCle
=OpW6
-----END PGP SIGNATURE-----

OK, I have 4 queries, the SQL view of which I have below, with brief
explanations. Please understand that I use the Query Design Grid to make
my
queries, as I'm not so fluent with SQL; what is below is just what I
copied
out of the SQL view:


qryItemTotal: This gives me the total count of Items produced per day in
the date range:

SELECT DISTINCT Count(ItemDetail.UnitID) AS CountOfUnitID,
ItemDetail.DateProduced
FROM ItemDetail
WHERE (((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryRemakes: This gives me the COUNT of "Solution = 1" records per day in
the date range from the form

SELECT Count(ItemDetail.UnitID) AS CountOfUnitID, ItemDetail.DateProduced
FROM ItemDetail LEFT JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((QCData.Solution)=1) AND ((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryPartialRemake: This gives me COUNT of "Solution = 2" records per day
in
the date range of the form

SELECT Count(QCData.UnitID) AS CountOfUnitID, ItemDetail.DateProduced
FROM ItemDetail LEFT JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((QCData.SolutionNum)=2) AND ((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]))
GROUP BY ItemDetail.DateProduced;



qryTotalErrors: This gives me a SUM of the Quantity of all errors found
whose solutions was NOT 1, 2, or 10

SELECT Sum(QCData.Qty) AS SumOfQty, ItemDetail.DateProduced
FROM ItemDetail INNER JOIN QCData ON ItemDetail.UnitID = QCData.UnitID
WHERE (((ItemDetail.DateProduced) Between
[Forms]![frmInspectionReportDates]![StartDate] And
[Forms]![frmInspectionReportDates]![EndDate]) AND ((QCData.Solution)<>10
And
(QCData.Solution)<>1 And (QCData.Solution)<>2))
GROUP BY ItemDetail.DateProduced;


Now, each of these queries obviously can have a different number of
records,
with different dates. So, when I attempt to combine them in a separate
query, I don't get all records returned.

For instance, if there are no Solution = 1 on 1/5/05, I won't see 1/5/05
in
the combined query, even though there ARE other errors or records from
the
other 2 queries. I see:

Date CountOfUnitID Remake 3/4Remake
OtherErrors
1/3/05 43 2
1 65
1/11/05 55 1
3 45

When what I NEED to see is:

Date CountOfUnitID Remake 3/4Remake
OtherErrors
1/3/05 43 2
1 65
1/4/05 32 0
2 56
1/5/05 70 0
1 34
1/6/05 39 0
0 60

etc.

In the combined query, whenever one of the recordsource queries has a
null
set for a particular day (no rows), that date won't show up in the
combined
query either. I need to somehow have "0" show up, but it won't in my 4
queries, because some dates in each query may have no rows for a
particular
date in the date range (ie. not reurning a 0, but having a null set, or
no
rows period).

So how can I get to combine this data to make a recordsource for my
report
that will show "0's" when there is no data for that particular count for
that particular date (and I'm grouping by Date in the report, by the way,
which is why I need to have the DateProduced included in some or one or
all
of these queries).


Thanks again, I appreciate the consideration and input

Please show the querys that work. Perhaps I can work something out.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


rgrantz wrote:

Hi again.

I appreciate your help with this, please don't give up on me:>).

The results returned a HUGE number in each column, like thousands more
than

actually exist in the tables. I deleted all the fields except the
DateProduced (the one with GroupBy and the one with the criteria) and
the

TotalUnits to see if I was somehow summing the Qty fields wrong, but
still

got thousands and thousands more per day than were actually in the
table.

I wanted to make sure I was clear about the difference between Qty and
the

number of instances of the 1 and 2. I need to show:

- Total Units Made per Day in date range (Count of unique UnitD in
ItemDetail table per day)
- Total Instances of Remakes (Count of Solution = 1 from QCData, NOT Sum
of

Qty. Qty is the number of each defect type found on that item which may
have RESULTED in Solution being 1)
- Total Instances of 3/4 Remakes (Count of Solution = 2 from QCData, NOT
Sum

of Qty. See above)
- Total Defects whose Solutions were not 1 or 2 (this one IS a Sum of
Qty,

summing the Qty for all defects whose Solutions were NOT 1 or 2)

And, the Date grouping seems to already be handled from your example.

Does this complicate things enough so as to be impossible? I have no
problem with making multiple queries to get to this point, but when I

try to

combine the ones I made (3 separate queries, which work), I don't get
all

the data because in the combination ALL the criteria across all 3
queries

are used by Access, so I don't see any dates where there was no Solution
=

1, for instance, even though there were defects, just not ones that
resulted

in a Solution of 1. In my combined query, I only see dates and counts
where

there were defects AND Solutions of 1 AND Solutions of 2.

If I'm not being very clear, I can put this db somewhere for download

Thanks again, I appreciate your efforts





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

I forgot to put the ending parenthesis on the Sum() function. Sorry.
Here is the correction:

Sum(IIf(D.Solution = 1, D.Qty)) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty)) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty)) As OtherDefects


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiYhgIechKqOuFEgEQKnqACePxWvyEMbXq339QZm2qgN6rp6Jk8AoMQ7
/n9wl1f6QmHBlxER/xZ0iNx2
=bbMl
-----END PGP SIGNATURE-----

rgrantz wrote:


I get a syntax error, missing operator when trying to put your
suggestion

in
SQL view and save the query.






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

I believe your criteria is really the Date range of the report. The
other "criteria" can be designated in the SELECT clause. Here's a
try:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT I.DateProduced,
Count(*) As TotalUnits,
Sum(IIf(D.Solution = 1, D.Qty) As Remakes,
Sum(IIf(D.Solution = 2, D.Qty) As [3/4 Remakes],
Sum(IIf(D.Solution Not In (1,2), D.Qty) As OtherDefects

FROM ItemDetail As I INNER JOIN QCData As D


ON I.UnitID = D.UnitID
WHERE I.DateProduced Between [Begin Date] And [End Date]
GROUP BY I.DateProduced