Need help writing query too complex for my skill level

[Christine Pearc]

I just can’t seem to get the hang of writing queries hope someone can help me figure out what to put where in the query design grid. I feel pretty dumb not being able to figure it out, especially since my boss wants a report asap .

What I need to do is to figure out how many CARs (corrective action requests) for each process are pending, how many have been pending for over 7 working days, and how many have been issued for action, and how many have action dates that are overdue.

TABLE FIELDS:
ProcessID
DateOpened
Status: Values are 1-Requested, 2-Issued, 3-Rejected, 4-Incident, 5-Closed
ReviewDate
TargetDate
ClosedDate

CRITERIA FOR EACH COLUMN:
Below is the English for what I want and my feeble attempt to use Access terminology to get the required results. I’m certain that, not only is the coding wrong, but I really don’t understand how it should look in the query design grid.

Column1 - ProcessID: No criteria required – just list the processes and info for columns 2-4.

Column2 - # CARs Pending Review - The CAR has been open for less than 7 working days and the ReviewDate is blank:
StatusID =1 and DateDiff("d",[DateOpened],[DispositionDate] <7
working days

Column3 - # CARs Review Overdue - The CAR has been open more than 7 working days and the ReviewDate is blank (null):
StatusID=1 and DateDiff("d",[DateOpened],[DispositionDate]>7
working days

Column4 - # CARs Issued for Action – The CAR has been issued for action and the target date hasn’t been passed:
StatusID=2 and TargetDate<Date()

Column 5 - # CARs Action Overdue: The CAR has been issued and the target date has come and gone:
StatusID=2 and Date() > TargetDate

--------------
Thanks in advance,
Christine

 

[Len Boorman]

I think your basic problem is that you are trying to do everything in one query.

I doubt that this can be done (but someone will no doubt put me right)

Solution

Do a single query to satisfy each requirement
Create a small report for each requirement
Put all the individual reports onto another report

You now have 1 report with everything on it

Len B

 

[calmwind]

Try this:

tblCAR
ProcessID, AutoNumber
DateOpened, Date/Time
Status, Number
ReviewDate, Date/Time
TargetDate, Date/Time
ClosedDate, Date/Time

tblStatus
StatusID, Number
StatusDesc, Text

qryCAR
SELECT tblCAR.ProcessID, tblCAR.DateOpened, tblCAR.Status, tblCAR.ReviewDate, tblCAR.TargetDate, tblCAR.ClosedDate, DateDiff("d",[DateOpened],[DispositionDate]) AS DateDiffOpened, DateDiff("d",[DispositionDate],[TargetDate]) AS DateDiffTarget, (IIf([Status]=1,(IIf([ReviewDate] Is Null,(IIf([DateDiffOpened]<7,"Pending Review","")),"")),"")) AS PendingReview, (IIf([Status]=1,(IIf([ReviewDate] Is Null,(IIf([DateDiffOpened]>7,"Review Overdue","")),"")),"")) AS ReviewOverdue, (IIf([Status]=2,(IIf([DateDiffTarget]>0,"Issued for Action","")),"")) AS IssuedforAction, (IIf([Status]=2,(IIf([DateDiffTarget]<0,"Action Overdue","")),"")) AS ActionOverdue
FROM tblCAR;

Hope that helps.

 

[Christine Pearc]

Thank you for that, CalmWind. I like your code better than the thought of having to construct a whole much of separate queries!

Could I trouble you again? I'd like the output so that only the number of records for each critiera are displayed, like this:

#CARs #CARs #CARs #CARs
Pending Review Issued for Action
ProcessID Review Overdue Action Overdue
--------- -------- -------- ---------- --------
HR 5 1 2 1
Assembly 3 1
Test 9 0 3 1
...etc.

If you could help one last time with this I'll study what you've done and hopefully be able to construct similar queries for other stuff I've got to report on.

Cheers,
Christine

 

[calmwind]

Try this:

tblCAR
ProcessID, AutoNumber
ProcessName, Text <-----new
DateOpened, Date/Time
Status, Number
ReviewDate, Date/Time
TargetDate, Date/Time
ClosedDate, Date/Time

I had to add ProcessName to tblCAR so you could group by it in the summary query


tblStatus
StatusID, Number
StatusDesc, Text

qryCAR <-----changed SQL
SELECT tblCAR.ProcessID, tblCAR.ProcessName, tblCAR.DateOpened, tblCAR.Status, tblCAR.ReviewDate, tblCAR.TargetDate, tblCAR.ClosedDate, DateDiff("d",[DateOpened],[DispositionDate]) AS DateDiffOpened, DateDiff("d",[DispositionDate],[TargetDate]) AS DateDiffTarget, (IIf([Status]=1,(IIf([ReviewDate] Is Null,(IIf([DateDiffOpened]<7,1,0)),0)),0)) AS PendingReview, (IIf([Status]=1,(IIf([ReviewDate] Is Null,(IIf([DateDiffOpened]>7,1,0)),0)),0)) AS ReviewOverdue, (IIf([Status]=2,(IIf([DateDiffTarget]>0,1,0)),0)) AS IssuedforAction, (IIf([Status]=2,(IIf([DateDiffTarget]<0,1,0)),0)) AS ActionOverdue
FROM tblCAR;

Since you want to add the the results, I changed the last part of the IIf statements from :
,"Pending Review","")),"")),"")) to ,1,0)),0)),0))
and
,"Issued for Action","")),"")) to ,1,0)),0))
and did the same to the other two iif fields to give you numeric values.


To get summary results, you need to make a summary query based off qryCAR to get the results you are asking for:

qryCARSum <-----new
SELECT qryCAR.ProcessName, Sum(qryCAR.PendingReview) AS SumOfPendingReview, Sum(qryCAR.ReviewOverdue) AS SumOfReviewOverdue, Sum(qryCAR.IssuedforAction) AS SumOfIssuedforAction, Sum(qryCAR.ActionOverdue) AS SumOfActionOverdue
FROM qryCAR
GROUP BY qryCAR.ProcessName;


Hope that helps.

 

[Christine Pearc]

Dear CalmWind,

Many thanks again - I wouldn't have thought of using two queries. I've been playing around with your revised code for the past few hours but can't get it to produce any datail: all the sums = 0.

I've attached a copy of the dB in the hopes you can look at it - I hope this isn't too cheeky of me . If you can't look at it I'll certainly understand, but if you could it would save me from further insanity trying to figure this out.

Christine

 

[calmwind]

I'm using Access 2000 and consequently can't view your attachment.

Is qryCAR working as expected? (Read step 11 first)

If it is, then try creating qryCARSum through the query wizard.

On 2000 here are the directions:
1) click "Create query by using wizard"
2) select Query: qryCAR
3) move over "ProcessName", "PendingReview", "ReviewOverdue", "IssuedforAction", "ActionOverdue"
4) click Next
5) select "Summary"
6) click "Summary Options"
7) choose the Sum check box for each of the four fields shown
8) click OK
9) click Next
10) rename query to qryCARSum

*****11) It'll ask for the DispositionDate. You must type some date in, otherwise it will give all zeros....(could that be the problem with the other query?)


If DispositionDate is always equal to today's date, then in the SQL for each of the two instances in qryCAR, change
[DispositionDate]
to
Now()
and it won't keep asking for the DispositionDate

Hope that helps.

 

[calmwind]

I was able to get a 2003 version of Access to check out your database.

qryCAR

1) DateDiffOpened: DateDiff("d",[DateOpened],[ReviewDate])

should be

DateDiffOpened: DateDiff("d",[DateOpened],Now())

since [ReviewDate] may be blank and Now() will help calculate for today's date

2) DateDiffTarget: DateDiff("d",[ReviewDate],TargetDate])

should be

DateDiffTarget: DateDiff("d",Now(),[TargetDate])

since you want to compare the TargetDate to today


Hope that helps.

 

[Christine Pearc]

Dear CalmWind,

Sorry for the delay since you last wrote. Your last two posts, and especially the one for Now() worked like a charm! Thank you so very much. When you see well executed/written code, it makes novices like me realise that, not only do you need to know Access code and syntax stuff, but having the ability to actually think about what you're actually trying to do in the first place (logic!) is more than half the battle! Thanks again, Christine