SQL FORMULA FOR ACCESS

[johnkylex]

I track data on problems with certain hardware. These problems are categorized by repair codes. I track the type of hardware someone has, the date, and the repair codes. What I would like to do is to take the last full week of data. Take the top ten repair codes used to track problems, and then plot the history of those ten repair codes and their date.

This way I will get to see my top ten problems for the previous week and see the past history trend for those repair codes.

Thanks,
John
johnkylex@yahoo.com

 

[mdmilner]

Three SQL Queries. Last Gives you a List of all valid entries for those specific top 10 trouble codes. First Query is a Crosstab, the second gives you the TOP X entries, and the third Joins the original table to add additional information. Please pardon the field names as I pulled this quickly out of a test database for a completely unrelated type of function.

Summarize the information by counting the entries within the time frame

TRANSFORM Count(tbl1.fld1) AS CountOffld1
SELECT tbl1.fld2, Count(tbl1.fld1) AS [Total Of fld1]
FROM tbl1
WHERE (((tblD1.Datefld4)>=Now()-7))
GROUP BY tbl1.fld2
PIVOT tbl1.fld3;

Give me the top 10 entries

SELECT TOP 10 qry1.[Total Of fld1], qry1.fld2
FROM qry1
ORDER BY qry1.[Total Of fld1] DESC;

Join the top 10 list to the original table on the field counted and list all results.

SELECT tbl1.Number, tbl1.Datefld4
FROM tbl1 RIGHT JOIN qry2 ON tbl1.fld2 = qry2.fld2;

Mike