Help with Simple Sort/Group Query

[Guest]

I have a database that tracks quality issues. One table that has the following fields:

RootCauseID RootCauseDescrip Date TotalRootCause

01-01 Wrong Material 2/1/2002 $1,05
02-25 Wrong Part Num 10/10/2003 $250
03-43 Bad Adhesive 8/08/2004 $165

I want to write a query that will give me the top 8 Root Causes for each year and month
based on the $ amount of TotalRootCause. It should calcualte monthly and yearly totals

I know this should be simple but I can't figure it out

Thanks for any help

Tim

 

[Michel Walsh]

Hi,


SELECT a.RootCauseDescrip, Month(a.[Date]), Year(a.[Date]),
a.TotalRootCause
FROM myTable as a LEFT JOIN myTable As b
ON a.RootCauseDescrip=b.RootCauseDescrip
AND Month(a.[Date] ) = Month(b.[Date] )
AND Year(a.[Date] = Year(b.[Date]_
AND a.TotalRootCause < b.TotalRootCase
GROUP BY a.RootCauseDescrip, Month(a.[Date]) , Year(a.[Date]),
a.TotalRootCause
HAVING COUNT(*) < 8

ORDER BY a.RootCauseDescrip, Month(a.[Date]) , Year(a.[Date]),
a.TotalRootCause DESC


and


SELECT a.RootCauseDescrip,Year(a.[Date]), a.TotalRootCause
FROM myTable as a LEFT JOIN myTable As b
ON a.RootCauseDescrip=b.RootCauseDescrip
AND Year(a.[Date] = Year(b.[Date]_
AND a.TotalRootCause < b.TotalRootCase
GROUP BY a.RootCauseDescrip, Year(a.[Date]), a.TotalRootCause
HAVING COUNT(*) < 8

ORDER BY a.RootCauseDescrip, Year(a.[Date]), a.TotalRootCause DESC



should do (I didn't tested it), the first one by month, the last one by
year.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michael, first, thanks so much for your help. I really appreciate you taking the time to write that out.
I hate to bug you again. But I am getting a synatx error. I am very novice with SQL and I can't find the mistake.
My table name is RootCauseExternal. I substituted it for myTable in the query you wrote. The syntax error
is occuring on the line AND Month(a.[Date]) = Month(b.[D*te])
The error is where the asterisk is. I copied my revision below.

Thanks so much for the help.


SELECT a.RootCauseDescrip, Month(a.[Date]), Year(a.[Date]),
a.TotalRootCause
FROM RootCauseExternal as a LEFT JOIN RootCauseExternal As b
ON a.RootCauseDescrip=b.RootCauseDescrip
AND Month(a.[Date]) = Month(b.[Date])
AND Year(a.[Date] = Year(b.[Date]_
AND a.TotalRootCause < b.TotalRootCase
GROUP BY a.RootCauseDescrip, Month(a.[Date]) , Year(a.[Date]),
a.TotalRootCause
HAVING COUNT(*) < 8


----- Michel Walsh wrote: -----

Hi,


SELECT a.RootCauseDescrip, Month(a.[Date]), Year(a.[Date]),
a.TotalRootCause
FROM myTable as a LEFT JOIN myTable As b
ON a.RootCauseDescrip=b.RootCauseDescrip
AND Month(a.[Date] ) = Month(b.[Date] )
AND Year(a.[Date] = Year(b.[Date]_
AND a.TotalRootCause < b.TotalRootCase
GROUP BY a.RootCauseDescrip, Month(a.[Date]) , Year(a.[Date]),
a.TotalRootCause
HAVING COUNT(*) < 8

ORDER BY a.RootCauseDescrip, Month(a.[Date]) , Year(a.[Date]),
a.TotalRootCause DESC


and


SELECT a.RootCauseDescrip,Year(a.[Date]), a.TotalRootCause
FROM myTable as a LEFT JOIN myTable As b
ON a.RootCauseDescrip=b.RootCauseDescrip
AND Year(a.[Date] = Year(b.[Date]_
AND a.TotalRootCause < b.TotalRootCase
GROUP BY a.RootCauseDescrip, Year(a.[Date]), a.TotalRootCause
HAVING COUNT(*) < 8

ORDER BY a.RootCauseDescrip, Year(a.[Date]), a.TotalRootCause DESC



should do (I didn't tested it), the first one by month, the last one by
year.




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


It should be [Date], not [D*te]. Also,another typo I did, it should be
Year(b.[Date]) not Year(b.[Date]_ , both queries.



Hoping it may help,
Vanderghast, Access MVP

Michael, first, thanks so much for your help. I really appreciate you

taking the time to write that out.

I hate to bug you again. But I am getting a synatx error. I am very novice

with SQL and I can't find the mistake.

My table name is RootCauseExternal. I substituted it for myTable in the

query you wrote. The syntax error

is occuring on the line AND Month(a.[Date]) = Month(b.[D*te])
The error is where the asterisk is. I copied my revision below.

Thanks so much for the help.


SELECT a.RootCauseDescrip, Month(a.[Date]), Year(a.[Date]),
a.TotalRootCause
FROM RootCauseExternal as a LEFT JOIN RootCauseExternal As b
ON a.RootCauseDescrip=b.RootCauseDescrip
AND Month(a.[Date]) = Month(b.[Date])
AND Year(a.[Date] = Year(b.[Date]_
AND a.TotalRootCause < b.TotalRootCase
GROUP BY a.RootCauseDescrip, Month(a.[Date]) , Year(a.[Date]),
a.TotalRootCause
HAVING COUNT(*) < 8


----- Michel Walsh wrote: -----

Hi,


SELECT a.RootCauseDescrip, Month(a.[Date]), Year(a.[Date]),
a.TotalRootCause
FROM myTable as a LEFT JOIN myTable As b
ON a.RootCauseDescrip=b.RootCauseDescrip
AND Month(a.[Date] ) = Month(b.[Date] )
AND Year(a.[Date] = Year(b.[Date]_
AND a.TotalRootCause < b.TotalRootCase
GROUP BY a.RootCauseDescrip, Month(a.[Date]) , Year(a.[Date]),
a.TotalRootCause
HAVING COUNT(*) < 8

ORDER BY a.RootCauseDescrip, Month(a.[Date]) , Year(a.[Date]),
a.TotalRootCause DESC


and


SELECT a.RootCauseDescrip,Year(a.[Date]), a.TotalRootCause
FROM myTable as a LEFT JOIN myTable As b
ON a.RootCauseDescrip=b.RootCauseDescrip
AND Year(a.[Date] = Year(b.[Date]_
AND a.TotalRootCause < b.TotalRootCase
GROUP BY a.RootCauseDescrip, Year(a.[Date]), a.TotalRootCause
HAVING COUNT(*) < 8

ORDER BY a.RootCauseDescrip, Year(a.[Date]), a.TotalRootCause DESC



should do (I didn't tested it), the first one by month, the last one by
year.




Hoping it may help,
Vanderghast, Access MVP

I have a database that tracks quality issues. One table that has

the
following fields:

$1,050
02-25 Wrong Part Num 10/10/2003 $2500
03-43 Bad Adhesive 8/08/2004 $1650

for each
year and month

based on the $ amount of TotalRootCause. It should calcualte

monthly and
yearly totals.