Overdue Dates in Qry

[samonwalkabout]

I have a qry that gives me the due dates of projects. Row heading by department. Colums displaying date in Months. The cells show the number of project for a department in a month. Is it possible to get the colums to group the dates into (not due, 1-30 days overdue, 31-90 days over due, 91-180 days overdue, 181-365 days over due.

heres the SQL.

TRANSFORM Count([2002_Preventative_Actions_Table].Auto_Number) AS CountOfAuto_Number
SELECT [2002_Preventative_Actions_Table].Dept_Resp_ID, Count([2002_Preventative_Actions_Table].Auto_Number) AS [Total Of Auto_Number]
FROM 2002_Preventative_Actions_Table
GROUP BY [2002_Preventative_Actions_Table].Dept_Resp_ID
PIVOT Format([Due Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Im guessing its something to do with the PIVOT setting??

Thanks

 

[simongallop]

I am guessing that it might be easier to take a step back. How is the original data stored that feeds the query that you have shown? If you have a table with a record per project then it would be easier to carry out the test at that level in assigning each record to the correct column and then summing that query.

ie SELECT Proj_ID, Proj_Dep, Iif((Proj_date>(date()-90) AND Proj_Date<date(),1,0) AS <90Days, ...etc.

 

[samonwalkabout]

Yes i have one record per project. the autonumber is the project ID and the Dept_resp_ID = the department.

But im still unsure of what you mean, should i not be using a cross tab qry for this? Do i need IF statments as criteia in a select query?

Thanks

 

[simongallop]

The way that I have suggested is for you to flag each record with a 1 or 0 depending upon which date range they fall into. You should then have a query showing the following data:

Proj_ID, Dept, Proj_Date, Less<30,31-90, 91-180, 181-365
1............Admin...15-Mar-03..........0........1...........0..............0
2............IT.........31-DEC-02..........0........0...........0..............1
etc.

With this data, you can then run a second query where you select Dept and the time periods, making the query a totals query, grouping on Dept and summing the time periods. You will then have the number of projects overdue and how many.

HTH

 

[samonwalkabout]

I cant get the qry to flag the data with a 0/1 value. i Presume it this part of your statement (bolded)

SELECT Proj_ID, Proj_Dep, Iif((Proj_date>(date()-90) AND Proj_Date<date(),1,0) AS <90Days, ...etc.

But i cant seem to get it to take, there no error it just wont let me save??


Heres a slim version

 

[simongallop]

Instead of being a linked table, run a make table query on the data and create a table with data in it. Obviously I am not linked to your network so reading the linktable file is proving to be a bit difficult!

 

[samonwalkabout]

Sorry

Sorry hows this

 

[simongallop]

Hope this is what you were after!!

 

[samonwalkabout]

Harry your a genius!

That's exactly what I was trying to do! I didn't even know you could make a select query function this way, I was just exporting it and using IF statements in excel! This is a much better way of getting it done.

Thanks very much for your help!

 

[mark curtis]

Nice queries Harry