Help with a Union Query (I Think) needed

[kentwoodjean]

Hoping someone can assist me. I am trying combine 3 separate queries into one (Union Query) and I am having difficulty. I have shown the SQL view of the 3 initial queries, along with the final query where I tried to join them. The final joining as the look I want, but does not give me accurate totals based on the way they are linked. As a final result I would like totals grouped together as follows:

Resp Main RespCat CountOfRecdDt CountofClosedDt Countof Pending(countofContract*) Avg Age

Receipts

SELECT tblLastUpdated.[Recd Dt], Count(tblLastUpdated.[Recd Dt]) AS [CountOfRecd Dt], tblLastUpdated.[Resp Cd]
FROM tblLastUpdated
GROUP BY tblLastUpdated.[Recd Dt], tblLastUpdated.[Resp Cd]
HAVING (((tblLastUpdated.[Recd Dt]) Like "*2004*"));

Closes

SELECT tblLastUpdated.[Closed Dt], Count(tblLastUpdated.[Closed Dt]) AS [CountOfClosed Dt], tblLastUpdated.[Resp Cd]
FROM tblLastUpdated
GROUP BY tblLastUpdated.[Closed Dt], tblLastUpdated.[Resp Cd]
HAVING (((tblLastUpdated.[Closed Dt]) Like "*2004*"));

Pending

SELECT Count(tblOpenCases.[Contract #]) AS [CountOfContract #], Avg((Date()-[Last St Date])) AS AvgAge, RespCdeLst.RespID, RespCdeLst.RespCat, RespCdeLst.RespMain
FROM tblOpenCases INNER JOIN RespCdeLst ON tblOpenCases.[Resp Cd] = RespCdeLst.RespCde
GROUP BY RespCdeLst.RespID, RespCdeLst.RespCat, RespCdeLst.RespMain;


Final Query grouping the Previous 3 queries

SELECT [Countof Closed DateRespCde].[CountOfClosed Dt], CountofRecdDate1.[CountOfRecd Dt], CountofRecdDate1.[Recd Dt], PendingCases.[CountOfContract #], PendingCases.AvgAge, PendingCases.[Resp Cd], PendingCases.RespID, PendingCases.RespDef, PendingCases.RespCat, PendingCases.RespMain
FROM (CountofRecdDate1 INNER JOIN PendingCases ON CountofRecdDate1.[Resp Cd] = PendingCases.[Resp Cd]) INNER JOIN [Countof Closed DateRespCde] ON PendingCases.[Resp Cd] = [Countof Closed DateRespCde].[Resp Cd]
WHERE (((CountofRecdDate1.[Recd Dt])=[Weekending Date (05/03/04)]) AND (([Countof Closed DateRespCde].[Closed Dt])=[Recd Dt]))
ORDER BY PendingCases.RespCat;

 

[dhookom]

How can you suggest the final query is the grouping of the previous three when the final query doesn't contain "receipts", "closes", or "Pending"?

Receipts and Closes have similar fields that you could join in a query but Pending doesn't.

Is [Recd Dt] a text field or date field? If it is a date field then don't use string comparisons. Use date functions for comparing dates
Year(tblLastUpdated.[Recd Dt]) =2004

What is this [red]Weekending Date (05/03/04)[/red]?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kentwoodjean]

The weekending Date was a mistake. It will be replaced with a from and to date. The only field that all 3 have in common is the RespCde. The 1st query tells me how many receipts for one of several RespCdes based on received date. the 2nd Query tells me the # of closes by RespCde based on closed date (criteria is RecvdDte). The 3rd gives me real time # of pending items under each RespCde. The open cases table that makes up the pending query also contains recvdDte information. Does that make a difference?

If I have to keep them separate, I may just do a sub report in my final product.

 

[dhookom]

I still have un-resolved issues from my first reply.
Why don't you start from the beginning by explaining your table structure(s) and what you want as a final result?


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kentwoodjean]

Thanks for the response. By the way, congrats on being Tipster of the Week. Hopeing you can tip me with this additional information.

Recd Dt is a date field rather than text and as indicated before, weekending was not the correct criteria entry. Knowing what the SQL is for my existing 3 queries, let me then give you the table structure from which they are made.

Both the receipts and closes query are made from the same table, tblLastUpdated, with the following fields:

Last St Cd Text
Last St Date Date/Time
First Name Text
Last Name Text
Contract # Text
Group # Text
Recd Dt Date/Time
RecdDtAlpha Text
Desk Cd Text
Case Cd Text
Resp Cd Text
Closed Dt Date/Time

The 3rd query "Pending" is made from 2 tables. They are: tblOpen Cases. tblRespCdeLst

Last St Cd Text RespCde Text
Last St Date Date/Time RespID Text
First Name Text RespCat Text
Last Name Text RespMain Text
Contract # Text RespDef Text
Group # Text RespDefID Text
Recd Dt Date/Time
RecdDtAlpha Text
Desk Cd Text
Case Cd Text
Resp Cd Text
Closed Dt Date/Time

My receipts query gave me a count of contract numbers based on resp Cd for a received date(s).
My closes query gives me a count of contract numbers based on resp Cd for a closed date(s)
My pending query gave me a count of contracts for each RespCd that are currently open along with avg days age. The closed date field is not populated. Following is what I want my end result to be:

Recvd Closed Avg Days Pending
RespMain (Facility)
RespID(BL) RespDef(Bcard) # # # #
RespID(BO) RespDef(POS) # # # #

RespMain (Professional)
RespID(BA) RespDef(Local) # # # #
RespID(BO) RespDef(POS) # # # #
RespID(BL) RespDef(Bcard) # # # #

Hope this gives you a bit of a better idea.