Re: probably not as complex as I'm thinking...
From: rgrantz (rgrantz_at_hotmail.com)
Date: 03/09/05
- Next message: Duane Hookom: "Re: Titles on InputBox within Query"
- Previous message: MGFoster: "Re: How to insert a row without testing for existence"
- In reply to: MGFoster: "Re: probably not as complex as I'm thinking..."
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 8 Mar 2005 23:09:29 -0800
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?
"MGFoster" <me@privacy.com> wrote in message
news:ulOVd.577$cN6.449@newsread1.news.pas.earthlink.net...
> -----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-----
>
>
> rgrantz wrote:
>> 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
>>
>>
>>
>>
>>
>>
>> "MGFoster" <me@privacy.com> wrote in message
>> news:f2vVd.9920$MY6.9337@newsread1.news.pas.earthlink.net...
>>
>>>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
>>>>
>>>>
>>>>
>>>>"MGFoster" <me@privacy.com> wrote in message
>>>>news:gkpVd.3569$L17.2096@newsread3.news.pas.earthlink.net...
>>>>
>>>>
>>>>>-----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.
>>>>>>
>>>>>>
>>>>>>
>>>>>>"MGFoster" <me@privacy.com> wrote in message
>>>>>>news:SC3Vd.8523$MY6.1486@newsread1.news.pas.earthlink.net...
>>>>>>
>>>>>>
>>>>>>
>>>>>>>-----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
- Next message: Duane Hookom: "Re: Titles on InputBox within Query"
- Previous message: MGFoster: "Re: How to insert a row without testing for existence"
- In reply to: MGFoster: "Re: probably not as complex as I'm thinking..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
