Particular Months Data

[kattzndagz]

Hi
I have a Deficiency Report Register database.

I need to run a query which will end up looking like this

This Month
No. No. No.
Type Raised Rework Closed

Corrective Actions 0 0 0
Non-Conformances 9 9 0
Process Improvements 0 0 0

I have the SQL (see below) from the query I have the data in but i can't seperate it into the above information - no matter what i do. I can't seem to get the zero values and all of the info in the one query. When i have separate queries i can't get that info into one report. I have tried a cross tab too but it wouldn't seem to work either.


SELECT [DR Register Tbl].Type, [DR Register Tbl].[Date Raised], [DR Register Tbl].[Rework Required], [DR Register Tbl].[Close-Out Date]
FROM [DR Register Tbl]
WHERE ((([DR Register Tbl].Type) Is Not Null));


I also have this query..
SELECT [DR Register Tbl].Type, Count([DR Register Tbl].[Date Raised]) AS [CountOfDate Raised], Count([All Open DR's].[DR No]) AS [CountOfDR No], Count([All Closed DR's].[Close-Out Date]) AS [CountOfClosed Out]
FROM ([DR Register Tbl] LEFT JOIN [All Closed DR's] ON [DR Register Tbl].[DR No] = [All Closed DR's].[DR No]) LEFT JOIN [All Open DR's] ON [DR Register Tbl].[DR No] = [All Open DR's].[DR No]
GROUP BY [DR Register Tbl].Type
HAVING ((([DR Register Tbl].Type)<>"nil"));

which looks like this
Overall
Type Raised Open Closed
Corrective Actions 22 3 19
Non-Conformance 118 51 67
Process Improvement 49 12 37

which works fine but overall i need the two lots of data to form one table for our monthly report. i.e side by side


I hope someone can help me

 

[PHV]

Which field(s) holds which date(s) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kattzndagz]

The main table i am using has a number of fields and only the following are where i get my data from

DR No Autonumber
Type Lookup 3 types previously mentioned
Date Raised Date
Close-out date Date
Rework yes/no

So, I need to get a report which counts how many of each type, per month and overall.

I have modified my database to include just basic info and I have the following queries based on the above table.

ALL CLOSED QRY
SELECT [DR Register Tbl].[DR No], [DR Register Tbl].Type, [DR Register Tbl].[Date Raised], [DR Register Tbl].[Rework Required], [DR Register Tbl].[Close-Out Date]
FROM [DR Register Tbl]
WHERE ((([DR Register Tbl].[Close-Out Date]) Is Not Null));

ALL OPEN QRY
SELECT [DR Register Tbl].[DR No], [DR Register Tbl].Type, [DR Register Tbl].[Date Raised], [DR Register Tbl].[Rework Required], [DR Register Tbl].[Close-Out Date]
FROM [DR Register Tbl]
WHERE ((([DR Register Tbl].[Close-Out Date]) Is Null));

OVERALL QRY
SELECT [DR Register Tbl].Type, Count([DR Register Tbl].[Date Raised]) AS [CountOfDate Raised], Count([DR Register Tbl].[Close-Out Date]) AS [CountOfClose-Out Date], Count(AllOpenDRQry.[DR No]) AS [CountOfDR No]
FROM [DR Register Tbl] LEFT JOIN AllOpenDRQry ON [DR Register Tbl].[DR No] = AllOpenDRQry.[DR No]
GROUP BY [DR Register Tbl].Type;

Since I have the Overall Qry now, I just need to get the current month query which needs to show, Number Raised, Number with Rework and Number Closed for the month for each Type.

Then I would like to combine the Overall Qry with the Current Month Qry in one report.

I have tried to make this as clear as possible so I hope it is understandable.

 

[PHV]

Which date ([Date Raised] or [Close-out date ]) you want to test against the current month ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kattzndagz]

Hi PH
I need to test against both dates as I need to know how many raised and how many closed for the month for each type - corrective actions, non conformance and process improvement.