I thought it was a simple report

[dmullins]

I have a report that I can't get the query to build correctly, I have had some help on already. I am not a programmer, but understand quite a bit with the databases.
I need a query that will give me 3 columns that display total count for a month of list of 8 items and two additonal that are in dollars. I also need a column that provides year to date total count and total dollars for the 2 items.

Here are the tables:

ExamErrorTracking is the data

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - Currency - use dismissed date
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is forRefunds, civil penalty date )
Status

This is LegalStatus table that contains the list of items in LegaDisp
These are the item names I need to count and total (also column 1).

Filed (noticeofhearing for date)
Dismissed (dismisseddate for date)
Pending (noticeofhearing for date)
Revocation (dismisseddate for date)
Suspension (dismisseddate for date)
Censure (dismisseddate for date)
Probation (dismisseddate for date)
Civil Penalties (dismisseddate for date)
Refunds (dismisseddate for date)
Cease & Desist (dismissedDate for date)

Here is what I have so far, and it is close:

Date function is not working, if I remove the date function YTD total is not right and the monthly of course is not right because of the date function not working.

Dismissed should use dismisseddate, it is using noticeof hearing

SELECT LegalStatus.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
Sum(XX.CivilPenalty) AS SumOfCivilPenalty, (SELECT Count([XX].LegalDisp) FROM
ExamErrorTracking AS [XX] WHERE [LegalStatus].LegalDisp = [XX].LegalDisp
AND ([XX].NoticeofHearing) Between DateSerial(Year(Date()),1,1) and
Date()-Day(Date()) ) AS [Year to date]
FROM LegalStatus LEFT JOIN ExamErrorTracking AS XX ON LegalStatus.LegalDisp
= XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY LegalStatus.LegalDisp;

Thanks to anyone that can take it on. Please.

 

[dmullins]

Had to edit some things

 

[boblarson]

An Outer join (LEFT JOIN) will not return the correct records if you have criteria. Use a sub query to select the record criteria and then use the Outer join.

 

[dmullins]

Thanks Bob, but I don't know how to do that. I cam from a FOXPRO world and that was 10 years ago, just learning this.

I will be in training in the next motnh and hope to learn it then.

 

[dmullins]

An Outer join (LEFT JOIN) will not return the correct records if you have criteria. Use a sub query to select the record criteria and then use the Outer join.

Thanks Bob, but I don't know how to do that. I cam from a FOXPRO world and that was 10 years ago, just learning this.

I will be in training in the next motnh and hope to learn it then.