Question Criteria in Query

[Sinoe]

A couple of days ago I posted a question about how one would go about designing a query to show if an occurrence was repeated within six month of the initial determination of the first allegation. I got some help but I think my question was not clear. I am attempting again to get some help this time hoping that the query included here can be improved to produce the outcome that I am looking for, which is to show all the allegations that were repeated within six months of the initial determination. Here is the query with out the repeats ( I cannot figure out how to do that):


SELECT DISTINCT SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_MAARC_CC_AP_RPT_ID, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_VICTIM_ID, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.AGENCY_DISP_DATE, Count(SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_ALLEG_CODE) AS CountOfSSIS_ALLEG_CODE, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_ALLEG_CODE, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_DISP_CODE, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_DISP_DESC, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_ALLEG_DESC
FROM SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV
WHERE (((SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_ASSESS_WG_START_DATE)>=#7/1/2015# And (SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_ASSESS_WG_START_DATE)<=#6/30/2016#) AND ((SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_ASSESS_WG_END_DATE)<=#12/31/2016#))
GROUP BY SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_AARC_CC_AP_RPT_ID, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_VICTIM_ID, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.AGENCY_DISP_DATE, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_ALLEG_CODE, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_DISP_CODE, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_DISP_DESC, SSAPVIEWS_COUNTYCEPREPORTDISPOSITIONV.SSIS_ALLEG_DESC;
Looking forward to your brilliant responses.

 

[MarkK]

Good naming of tables and fields really matters

Working with code is hard enough that you want to make sure the code you write, and therefore the objects you name, are distinct and uncluttered in their presentation and readability.

Some resources...

• Google "writing clean code"
• Google "Camel Case."
• Don't use ALLCAPS, underscores, or spaces in any identifier you create.
• Google "how to alias table names in jet sql"

I would normally help with your code, but for me to begin to understand what that SQL does, I would have to copy it into a text editor and change all the table names and field names so that it was minimally readable, and I don't have time for that, but I hope you do find this response valuable going forward.

Best Regards,
Mark

 

[MarkK]

Here's a related tweet...
https://twitter.com/CodeWisdom/status/913463638408777729

 

[isladogs]

Here's a related tweet...
https://twitter.com/CodeWisdom/status/913463638408777729

Thanks for posting that.
Some of the quotes made me laugh out loud....

 

[Sinoe]

Thanks you, MarkK. Appreciate your thoughtfulness.

 

[MarkK]

Here's your SQL with a naming style I'd recommend, and since there is only one table, there is no need to qualify the table name at every field...

SELECT DISTINCT RowID, VictimID, DispDate, Count(AllegationTypeID) AS CountAllegType, AllegationTypeID, DispensationID, DispDescription, AllegDescription
FROM tCountyDisposition
WHERE StartDate >= #7/1/2015#
And StartDate <= #6/30/2016#
And EndDate) <= #12/31/2016#
GROUP BY RowID, VictimID, DispDate, AllegationTypeID, DispensaionID, DispDescription, AllegDescriptioin;

One thing I see that I'm not sure makes sense is that you are using both DISTINCT and GROUP BY in the same SQL. The GROUP BY clause tries to lump rows together, and when they are lumped, then your Count(AllegationTypeID) AS CountAllegType function counts the rows in each lump. But the DISTINCT keyword has the effect of only selecting a single row per lump. I doubt you want to use both DISTINCT and GROUP BY in the same SQL. Not sure if this is the problem you are encountering though...
hth
Mark