Date counting

[Thompyt]

I am starting with the following:



TTL: ([Change Request].[CR_no] Between ([CMB.Date_ID]<=[Start_Date]) And ([Date_Closed]<=[CMB>Stop_Date]))

SELECT [CMB Dates].CMB, Sum(([CR_no] Between ([Date_ID]<=[Start_Date]) And ([Date_Closed]<=[Stop_Date]))) AS TTL, [Change Request].CR_No, [Change Request].Sub_No
FROM [CMB Dates], [Change Request]
GROUP BY [CMB Dates].CMB, [Change Request].CR_No, [Change Request].Sub_No
HAVING ((([CMB Dates].CMB)<>"") AND (([Change Request].CR_No)<>0) AND (([Change Request].Sub_No)<>0));

What I am trying to do is set up a count where:

Table CMB sets the date range of that particular voting board's influence
Query Change request sets the range where the CR is identified and where its finalized vote occurs.

I want to count the CR_No that fall in between the Start-Stop range for ech CMB.

There should be several CR_No's that will fall into several of the CMB date ranges and I want those to be counted in each range set.

Where am I going wrong? If I count I get a duplicate number for all the CMB's, if I sum I get a 0 for all the CMB's

[CJ_London]

your code does not seem to make sense

although the syntax is wrong, your code seems to be looking to return records where CRNo is between 0 and 0 or -1 and 0 or 0 and -1 (depending on the values) - is this correct? Please clarify so we can get the syntax correct first time

[ItsMe]

Maybe you need more parenthesis'.

Between (([CMB.Date_ID]<=[Start_Date])) And ([Date_Closed]<=[CMB>Stop_Date]))

[Thompyt]

AJAX,
[Change Request].CR_No)<>0 means exclude any CR_No's that = 0
[Change Request].Sub_No)<>0 means exclude any Sub_No's that = 0

I gather the syntax is wrong as the code doesn't work the way I am intending.

CMB Date Table
CMB..........Start_Date........Stop_Date.
CMB-01......01/11/2014......6/11/2014
CMB-02......07/11/2014......18/11/2014.........and so on up to CMB-17

Change Request table.
Date_ID = the date when there was a need for a change request
Date_Closed = When the Change Request was completed

Now I am looking for the number of CR's where it fits within the CMB-01, CMB-02...... Each CR has a init and stop date. I am trying to bracket those CR's that fit between the Start/Stop date noted on the CMB Table

SO I am looking for a count of CR_NOs where the start date is greater than or equal to the Date_ID and where the stop date is less than or equal to the Date_Closed.

[orange]

I suggest you tell us in simple English what you are trying to do. And provide an example to show clearly the input and the acceptable result.

When you create a quasi Access expression with syntax errors it certainly doesn't tell/show/describe to readers WHAT you are trying to do. It shows us HOW you have attempted to do something. We have to understand the something, before offering advice/options as to HOW it might be achieved.

Good luck.

Between 2 dates in Access is:

....BETWEEN Date1 and Date2 (and includes the 2 endpoints)

OOops: I see you have posted while I was typing.

You may get some help reviewing this article by PBaldy.

[Thompyt]

Orange,
SO I am looking for a count of CR_NOs where the start date is greater than or equal to the Date_ID and where the stop date is less than or equal to the Date_Closed.

I gave the tables above.
Count the # of CR_No's that fit in the dates of the CMB So CMB is between 1 November 2014 and 6 November 2014. CR_No's submitted for that time range have to fit within that range. So a Date_ID = 22 December 2014 would not fit, but one with 4 November 2014 would. A Date completed = 2 November would fit. What I want essentially is to count the CR's and the CR's that overlap the CMB date ranges. The overlapping would be counted in each start stop date range.

I understand Between Date1 and Date2.

My intention is to find:
Date1 = Anything Date_ID => Start_Date
Date2 = Anything Closed_Date <= Stop_Date

Example Timeline

Start_Date........................................ .................................................. ................................................St op_Date
...............<...................Any CR_No's that fit between here as ranged by Date_ID and Closed_Date.............>
............................<...................An y CR_No's that fit between here as ranged by Date_ID and Closed_Date.............>

[orange]

What , if anything, is the relationship between these tables
[CMB Dates], [Change Request]?

[Thompyt]

Orange,
There isn't a relationship.

[orange]

Ok. Can you show your table layouts to show Which tables contain which fields

I'm confused with your
My intention is to find:
Date1 = Anything Date_ID => Start_Date
Date2 = Anything Closed_Date <= Stop_Date

and no reference to table.field

[Thompyt]

CMB Dates Table
CMB
Start_Date
Stop_Date

CHange Request table
CR_No
Sub_No
Date_ID
Date_Closed

CRCount: Count([CR_No] Between ([Date_ID]<=[Start_Date]) And ([Date_Closed]<=[Stop_Date]))
or
CRCount: Count([CR_No] Between ([Change Request].[Date_ID]<=[CMB Dates].[Start_Date]) And ([Change Request].[Date_Closed]<=[CMB Dates].[Stop_Date]))

Come out the same: 337 for all CMBs

Where would I make an InnerJoin?

[Thompyt]

Table: Change Request

Properties
AlternateBackShade: 100 AlternateBackThemeColorInde -1
AlternateBackTint: 100 BackShade: 100
BackTint: 100 DatasheetFontHeight: 11
DatasheetFontItalic: False DatasheetFontName: Calibri
DatasheetFontUnderline: False DatasheetFontWeight: Normal
DatasheetForeColor: 0 DatasheetForeColor12: 0
DatasheetForeThemeColorInd 0 DatasheetGridlinesThemeColo -1
DateCreated: 3/3/2015 2:47:09 PM DefaultView: 2
DisplayViewsOnSharePointSite 1 Filter: ([Change Request].[Sponsor] In
("SOSI/ATEC","TRIAD"))
FilterOnLoad: False GUID: {guid {D66040A4-208D-4A35-
9B1B-AC4EAAEA4AF2}}
HideNewField: True LastUpdated: 5/17/2015 10:25:40 AM
NameMap: Long binary data OrderBy: [Change Request].[CR_No]
OrderByOn: True OrderByOnLoad: True
Orientation: Left-to-Right PublishToWeb: 1
RecordCount: 713 TabularCharSet: 0
TabularFamily: 34 ThemeFontIndex: -1
TotalsRow: True Updatable: True
Columns
CR_No Decimal 16
AggregateType: 2
AllowZeroLength: False
AppendOnly: False
Attributes: Variable Length
CollatingOrder: 18
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1035
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Text Box
GUID: {guid {A35D3A6F-79FE-4D44-8042-EEEF698EBBA5}}
OrdinalPosition: 2
Required: False
ResultType: 0
SourceField: CR_No
SourceTable: Change Request
TextAlign: General
Sub_No Single 4
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1140
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Text Box
GUID: {guid {E716E34A-1F81-479D-B3AC-4052E0B958F1}}
OrdinalPosition: 3
Required: False
ResultType: 0
SourceField: Sub_No
SourceTable: Change Request
TextAlign: General
Date_ID Date/Time 8
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default ColumnWidth: 1320
CurrencyLCID: 0
DataUpdatable: False
DefaultValue: =Date()
Format: Medium Date
GUID: {guid {0E5899B4-63F8-4E6B-A9A1-2183C98E385C}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 6
Required: False
ResultType: 0
ShowDatePicker: For dates
SourceField: Date_ID
SourceTable: Change Request
TextAlign: General
Date_Closed Date/Time 8
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Variable Length
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 2250
CurrencyLCID: 0
DataUpdatable: False
DefaultValue: Date()
Format: Short Date
GUID: {guid {AC39FA10-0470-4DBE-A8DC-9EE743B1C77D}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 36
Required: False
ResultType: 0
ShowDatePicker: For dates
SourceField: Date_Closed
SourceTable: Change Request
TextAlign: General



Table: CMB Dates
Properties
AlternateBackShade: 95 AlternateBackThemeColorInde 1
AlternateBackTint: 100 BackShade: 100
BackTint: 100 DatasheetForeThemeColorInd 0
DatasheetGridlinesThemeColo 3 DateCreated: 5/18/2015 5:49:55 PM
DefaultView: 2 DisplayViewsOnSharePointSite 1
FilterOnLoad: False GUID: {guid {26F56FB3-3E10-4508-
B1A6-B90B1108E843}}
HideNewField: False LastUpdated: 5/18/2015 6:23:13 PM
NameMap: Long binary data OrderByOn: False
OrderByOnLoad: True Orientation: Left-to-Right
PublishToWeb: 1 RecordCount: 7
ThemeFontIndex: 1 TotalsRow: False
Updatable: True

Columns
Name Type Size
CMB_ID Long Integer 4
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size, Auto-Increment
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
CurrencyLCID: 0
DataUpdatable: False
GUID: {guid {13483F86-4336-40AE-81C0-A05F4AB3C6A5}}
OrdinalPosition: 0
Required: False
ResultType: 0
SourceField: CMB_ID
SourceTable: CMB Dates
TextAlign: General
CMB Text 255
AggregateType: -1
AllowZeroLength: True
AppendOnly: False
Attributes: Variable Length
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
CurrencyLCID: 0
DataUpdatable: False
DisplayControl: Text Box
GUID: {guid {8870011F-0C5C-4631-8A6F-5E72E6D2B871}}
IMEMode: 0
IMESentenceMode: 3 OrdinalPosition: 1
Required: False
ResultType: 0
SourceField: CMB
SourceTable: CMB Dates
TextAlign: General
UnicodeCompression: True
Start_Date Date/Time 8
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
CurrencyLCID: 0
DataUpdatable: False
GUID: {guid {1DF2A972-5225-49C2-BFFB-12D265ADD87C}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 2
Required: False
ResultType: 0
ShowDatePicker: For dates
SourceField: Start_Date
SourceTable: CMB Dates
TextAlign: General
Stop_Date Date/Time 8
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
CurrencyLCID: 0
DataUpdatable: False
GUID: {guid {F79E3460-5F5B-4DAE-A3C3-FD77238CB777}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 3
Required: False
ResultType: 0
ShowDatePicker: For dates
SourceField: Stop_Date
SourceTable: CMB Dates
TextAlign: General

Query: Test

Properties
DateCreated: 5/18/2015 6:30:20 PM DefaultView: 2
DOL: Long binary data FilterOnLoad: False
GUID: {guid {5E1C6E50-D003-44CC- LastUpdated: 5/18/2015 6:39:40 PM
86C0-83AC7B55294E}}
MaxRecords: 0 ODBCTimeout: 60
OrderByOn: False OrderByOnLoad: True
Orientation: Left-to-Right PublishToWeb: 1
RecordLocks: No Locks RecordsAffected: 0
RecordsetType: Dynaset ReturnsRecords: True
TotalsRow: False Type: 0
Updatable: True
SQL
SELECT [CMB Dates].CMB, Count([CR_No] Between ([Date_ID]<=[Start_Date]) And
([Date_Closed]<=[Stop_Date])) AS CRCount
FROM [CMB Dates], [Change Request]
WHERE ((([Change Request].CR_No)<>0) AND (([Change Request].Sub_No)<>0))
GROUP BY [CMB Dates].CMB
ORDER BY [CMB Dates].CMB;

Columns
Name Type Size
CMB Text 255
AggregateType: -1
AllowZeroLength: True
AppendOnly: False
Attributes: Variable Length
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
CurrencyLCID: 0
DataUpdatable: False
DisplayControl: Text Box
GUID: {guid {2E78B62E-6FC9-4256-9CB9-26547EDF46FF}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 0
Required: False
ResultType: 0
SourceField: CMB
SourceTable: CMB Dates
TextAlign: General
UnicodeCompression: True
CRCount Long Integer 4
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size

[orange]

There's no INNER JOIN if there are no relationships.
I made up 2 tables and some fictitious dates since I had none.
I reread your posts several times and concocted some sql
based on

count  of CR_NOs where the start date is greater than or equal to the Date_ID  and
 where the stop date is less than or equal to the Date_Closed.

My tables are:
CMB

Start_date	Stop_date	CMBID
01/11/2015	06/11/2015	1
23/11/2015	26/11/2015	2
24/11/2015	26/11/2015	3
11/11/2015	14/11/2015	4

CR

Date_ID	Date_Closed	CR_NO	Sub_NO	id
22/12/2015	28/12/2015	100	1	1
02/11/2015	07/11/2015	100	2	2
23/11/2015	27/11/2015	200	10	3
08/11/2015	15/11/2015	300	2	4

My Query

SELECT Count(CR.CR_NO) AS Count_CR
    ,CMB.Start_Date
    ,CMB.Stop_Date
    ,CMB.CMBID
    ,CR.Date_ID
    ,CR.Date_Closed
FROM CMB
    ,CR
WHERE ((CMB.Start_Date) >= ([CR].[Date_ID]))
    AND (CMB.Stop_Date) <= ([CR].[Date_Closed])
GROUP BY CMB.Start_Date
    ,CMB.Stop_Date
    ,CMB.CMBID
    ,CR.Date_ID
    ,CR.Date_Closed

My result from the query

Count_CR	Start_Date	Stop_Date	CMBID	Date_ID	Date_Closed
1	11/11/2015	14/11/2015	4	08/11/2015	15/11/2015
1	23/11/2015	26/11/2015	2	23/11/2015	27/11/2015
1	24/11/2015	26/11/2015	3	23/11/2015	27/11/2015

Good luck.

[Thompyt]

Thanks Orange,
I see what you did by putting the dates range requirement in the criteria. I was trying to put it into 1, and it never crossed my mind to do it in 2. I didn't need the dates, so I removed them from the query for simplicities sake.

SELECT [CMB Dates].CMB, Count([Change Request].CR_NO) AS CR_Count
FROM [CMB Dates], [Change Request]
WHERE ((([CMB Dates].Start_Date)>=([Change Request].[Date_ID])) AND (([CMB Dates].Stop_Date)<=([Change Request].[Date_Closed])))
GROUP BY [CMB Dates].CMB;

[orange]

Good stuff, glad you have it resolved.