Access query to loop through date ranges

[MCTampa]

In Table 1, I have an area number and a date.
In Table 2, I have an area number and a range of dates for which that area is restricted.

My issue is that in Table 2, the area can be restricted in multiple, non-consecutive ranges.
For example:

Table 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]330[/TD]
[TD]10/5/2019[/TD]
[/TR]
[TR]
[TD]330[/TD]
[TD]9/27/2019[/TD]
[/TR]
[TR]
[TD]330[/TD]
[TD]12/5/2019[/TD]
[/TR]
</tbody>[/TABLE]

Table 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]330[/TD]
[TD]8/1/2019[/TD]
[TD]9/31/2019[/TD]
[/TR]
[TR]
[TD]330[/TD]
[TD]11/1/2019[/TD]
[TD]12/31/2019[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 would indicate that area 330 is NOT restricted from 10/1/2019 through 10/31/2019.
However when I perform a LEFT JOIN through Area, I am not sure how to set up the query to take the date from Table 1 and evaluate whether it falls between either of the two restriction ranges on Table 2.

Ideally, I would like to indicate that 10/5/2019 is allowed and 9/27 and 12/5 are restricted.

I think this needs to be done with some sort of a loop in VBA, but I'm not sure where to begin.

Thanks for any help,
Mike

 

[Micron]

Seems to me that a subquery is what I'd try before writing a bunch of code. In English:
Show me Table2.Area where Table1.Date is greater than Table2.Start and less than Table2.End (not that I would use the reserved word "Date" for any db object name).

 

[MCTampa]

Seems to me that a subquery is what I'd try before writing a bunch of code. In English:
Show me Table2.Area where Table1.Date is greater than Table2.Start and less than Table2.End (not that I would use the reserved word "Date" for any db object name).

I *think* you're looking at it differently than I am.
I need to retain all of the dates in Table 1 and identify whether or not those dates are restricted or not.

The way I interpret your response is that you're saying "Show me Table 2 areas what are open".
I want to know whether a date in Table 1 is open or closed by evaluating multiple ranges.

Edit - I am reading through the subquery stuff, but I haven't found a solution.

 

[Micron]

Not sure if I am or not. Haven't convinced myself yet that what I wrote is any different from

take the date from Table 1 and evaluate whether it falls between either of the two restriction ranges on Table 2

If you read my English version of the query task, aren't those two statements saying the same thing except that I wrote > and < rather than between? I agree it would show you restricted, but you may have expressed the requirement in two ways that are in conflict. My suggestion was for finding date ranges that by your definition lie within a "restricted" period. If you're looking for the opposite, then I don't know if the right option is to use the NOT operator ("show me areas that Table1.Date is NOT between table2 dates") or to use a query that provides records that are restricted and either a)surmise that if it's not in the result set then it's not restricted or b)use same query in a Find Unmatched Query (if not familiar, it is a query wizard you can use).

 

[xenou]

one idea:

select 
	t1.AreaID, 
	t1.EventDate, 
	(select (AreaID-AreaID-1) from Table10 t2 where t2.DateStart <= t1.EventDate and t2.DateEnd >= t1.EventDate) as IsRestricted 
from Table1 t1

My tables are like yours with some name updates:
Table1 (AreaID, EventDate)
Table10 (AreaID, DateStart, DateEnd)

dates are assumed to be whole dates (no date with time values)

I used (AreaID-AreaID-1) as a simple trick to get -1 as a result (to represent true or yes). All that really matters is whether we get null or any value back (null means no match so not restricted, any result means a match found so restricted).

P.S. I assume by 9/31 you really meant 9/30 - sep 30th as the last day of September.

P.P.S. I also take Micron's approach - to determine if not restricted then just see if it is restricted (and negate the truth value of that result).

So when do things like:

select A.AreaID, A.EventDate, 'Yes' as IsRestricted from
(
	select 
		t1.AreaID, 
		t1.EventDate, 
		(select AreaID from Table10 t2 where t2.DateStart <= t1.EventDate and t2.DateEnd >= t1.EventDate) as IsRestricted
	from Table1 t1
) A
where A.IsRestricted is not null

Union All

select B.AreaID, B.EventDate, 'No' as IsRestricted from
(
	select 
		t1.AreaID, 
		t1.EventDate, 
		(select AreaID from Table10 t2 where t2.DateStart <= t1.EventDate and t2.DateEnd >= t1.EventDate) as IsRestricted
	from Table1 t1
) B
where B.IsRestricted is null

P.P.P.S. There probably is some way to do this with Left Joins. I leave that as an exercise for others

 

[MCTampa]

I'm trying to get it, but I just don't. I'm sorry. Maybe if I show some real data.
Here are my restrictions and they are in table EXCERT27 Area Blocks.

EXCERT27	330	330	1/1/2019	8/31/2019
EXCERT27	330	330	10/1/2019	10/31/2019

<tbody>
[TH="bgcolor: #c0c0c0"] Cert [/TH]
[TH="bgcolor: #c0c0c0"] Resort [/TH]
[TH="bgcolor: #c0c0c0"] RAREA [/TH]
[TH="bgcolor: #c0c0c0"] Start Date [/TH]
[TH="bgcolor: #c0c0c0"] End Date [/TH]

</tbody>

My inventory looks like this:

330	BLT	8/3/2019	1	3
330	BLT	8/3/2019	2	29
330	BLT	8/10/2019	1	15
330	BLT	8/10/2019	2	32
330	BLT	8/17/2019	1	7
330	BLT	8/17/2019	2	40
330	BLT	8/24/2019	1	7
330	BLT	8/24/2019	2	28
330	BLT	8/31/2019	1	2
330	BLT	8/31/2019	2	12
330	BLT	9/7/2019	1	10
330	BLT	9/7/2019	2	35
330	BLT	9/14/2019	1	2
330	BLT	9/14/2019	2	24
330	BLT	9/21/2019	1	6
330	BLT	9/21/2019	2	17
330	BLT	9/28/2019	1	1
330	BLT	9/28/2019	2	17
330	BLT	10/5/2019	1	1
330	BLT	10/5/2019	2	7
330	BLT	10/12/2019	1	2
330	BLT	10/12/2019	2	5
330	BLT	10/19/2019	1	1
330	BLT	10/19/2019	2	7
330	BLT	10/26/2019	2	14
		TOTAL		324

<tbody>
[TH="bgcolor: #c0c0c0"] Area [/TH]
[TH="bgcolor: #c0c0c0"] Resort [/TH]
[TH="bgcolor: #c0c0c0"] Check In [/TH]
[TH="bgcolor: #c0c0c0"] Size [/TH]
[TH="bgcolor: #c0c0c0"] SumOfUnits [/TH]

</tbody>

What I want to do is have a column after sumofunits which tells me whether or not the unit is exposed to EXCERT27.
When I perform my simple join, it inflates to 536 units because it is evaluating each line twice and returning the results independent of each other.
For example 8/3/2019 should just be blank and just appear once, not showing blank and $299 because it is restricted.

330	BLT	8/3/2019	3
330	BLT	8/3/2019	3	$299
330	BLT	8/3/2019	29
330	BLT	8/3/2019	29	$299
330	BLT	8/10/2019	15
330	BLT	8/10/2019	15	$299
330	BLT	8/10/2019	32
330	BLT	8/10/2019	32	$299
330	BLT	8/17/2019	7
330	BLT	8/17/2019	7	$299
330	BLT	8/17/2019	40
330	BLT	8/17/2019	40	$299
330	BLT	8/24/2019	7
330	BLT	8/24/2019	7	$299
330	BLT	8/24/2019	28
330	BLT	8/24/2019	28	$299
330	BLT	8/31/2019	2
330	BLT	8/31/2019	2	$299
330	BLT	8/31/2019	12
330	BLT	8/31/2019	12	$299
330	BLT	9/7/2019	10	$299
330	BLT	9/7/2019	35	$299
330	BLT	9/14/2019	2	$299
330	BLT	9/14/2019	24	$299
330	BLT	9/21/2019	6	$299
330	BLT	9/21/2019	17	$299
330	BLT	9/28/2019	1	$299
330	BLT	9/28/2019	17	$299
330	BLT	10/5/2019	1
330	BLT	10/5/2019	1	$299
330	BLT	10/5/2019	7
330	BLT	10/5/2019	7	$299
330	BLT	10/12/2019	2
330	BLT	10/12/2019	2	$299
330	BLT	10/12/2019	5
330	BLT	10/12/2019	5	$299
330	BLT	10/19/2019	1
330	BLT	10/19/2019	1	$299
330	BLT	10/19/2019	7
330	BLT	10/19/2019	7	$299
330	BLT	10/26/2019	14
330	BLT	10/26/2019	14	$299
			536

<tbody>
[TH="bgcolor: #c0c0c0"] Area [/TH]
[TH="bgcolor: #c0c0c0"] Resort [/TH]
[TH="bgcolor: #c0c0c0"] Check In [/TH]
[TH="bgcolor: #c0c0c0"] SumOfUnits [/TH]
[TH="bgcolor: #c0c0c0"] EXCERT 27 [/TH]

</tbody>

I'm assuming I need to put a WHERE clause in after the FROM, but I don't get how to do that...

SELECT [Inventory with Grid].Area, [Inventory with Grid].Resort, [Inventory with Grid].[Check In], [Inventory with Grid].SumOfUnits, IIf([EXCERT 27 Area Blocks]![RAREA]=[inventory with grid]![Area] And [Inventory with Grid]![Check In]>=[EXCERT 27 Area Blocks]![Start Date] And [Inventory with Grid]![Check In]<=[excert 27 area blocks]![End Date],Null,[inventory with grid]![EXCERT27]) AS [EXCERT 27]

FROM [Inventory with Grid] LEFT JOIN [EXCERT 27 Area Blocks] ON [Inventory with Grid].Area = [EXCERT 27 Area Blocks].RAREA
[B][COLOR=#ff0000]WHERE????[/COLOR][/B]


GROUP BY [Inventory with Grid].Area, [Inventory with Grid].Resort, [Inventory with Grid].[Check In], [Inventory with Grid].SumOfUnits, IIf([EXCERT 27 Area Blocks]![RAREA]=[inventory with grid]![Area] And [Inventory with Grid]![Check In]>=[EXCERT 27 Area Blocks]![Start Date] And [Inventory with Grid]![Check In]<=[excert 27 area blocks]![End Date],Null,[inventory with grid]![EXCERT27])
HAVING ((([Inventory with Grid].Resort)="BLT") AND (([Inventory with Grid].[Check In])<=#10/31/2019#));

I just can't see in my head what you guys are describing.

 

[Micron]

Pretty sure you cannot use WHERE in a Totals query (HAVING takes the place of WHERE). Your left join will also cause all records from 1 table (subject to any criteria) and any records from the other that match the joined fields (also subject to criteria) which may explain the extra records. However, what you have doesn't relate at all to what has been proposed (yours is not a subquery). A subquery retrieves a value from records in a field and passes that value to the outer query as criteria for that field.

If you cannot adapt from the link's I posted or Xenous example then it might be better if you mocked up what you want as a result (use Excel and paste in a post) rather than show what you're getting that is not right. Or you could try an equal join, but based on your original post I don't see that as the solution.

My problem at this point (besides usually struggling with subqueries) is that I have no idea what

whether or not the unit is exposed to EXCERT27

means. I see no field that identifies a "unit". I can't imagine what "exposed" means in that context either. So I'm struggling to envision the goal.

 

[xenou]

Where is the $299 coming from? I don't see any dollar fields in the first two tables?

 

[xenou]

Here is my same query with your new tables:

SELECT 
	IWG.[Area], 
	IWG.Resort, 
	IWG.[Check In], 
	IWG.SumOfUnits, 
	'Yes' AS IsRestricted
FROM 
	[Inventory with Grid] IWG

WHERE
	IWG.Resort = 'BLT'
	AND IWG.[Check In] <= #10/31/2019#
	AND EXISTS (
		SELECT * 
		FROM [EXCERT 27 Area Blocks] ab 
		WHERE ab.RAREA = IWG.[Area] 
		AND IWG.[Check In] >= ab.[Start Date] 
		AND IWG.[Check In] <= ab.[End Date]
	)

UNION ALL

SELECT 
	IWG.[Area], 
	IWG.Resort, 
	IWG.[Check In], 
	IWG.SumOfUnits, 
	'No' AS IsRestricted
FROM 
	[Inventory with Grid] IWG

WHERE
	IWG.Resort = 'BLT'
	AND IWG.[Check In] <= #10/31/2019#
	AND NOT EXISTS (
		SELECT * 
		FROM [EXCERT 27 Area Blocks] ab 
		WHERE ab.RAREA = IWG.[Area] 
		AND IWG.[Check In] >= ab.[Start Date] 
		AND IWG.[Check In] <= ab.[End Date]
	)

Results:
----------------------------------------------------------
| Area | Resort |   Check In | SumOfUnits | IsRestricted |
----------------------------------------------------------
|  330 | BLT    |   8/3/2019 |          3 | Yes          |
|  330 | BLT    |   8/3/2019 |         29 | Yes          |
|  330 | BLT    |  8/10/2019 |         15 | Yes          |
|  330 | BLT    |  8/10/2019 |         32 | Yes          |
|  330 | BLT    |  8/17/2019 |          7 | Yes          |
|  330 | BLT    |  8/17/2019 |         40 | Yes          |
|  330 | BLT    |  8/24/2019 |          7 | Yes          |
|  330 | BLT    |  8/24/2019 |         28 | Yes          |
|  330 | BLT    |  8/31/2019 |          2 | Yes          |
|  330 | BLT    |  8/31/2019 |         12 | Yes          |
|  330 | BLT    |  10/5/2019 |          1 | Yes          |
|  330 | BLT    |  10/5/2019 |          7 | Yes          |
|  330 | BLT    | 10/12/2019 |          2 | Yes          |
|  330 | BLT    | 10/12/2019 |          5 | Yes          |
|  330 | BLT    | 10/19/2019 |          1 | Yes          |
|  330 | BLT    | 10/19/2019 |          7 | Yes          |
|  330 | BLT    | 10/26/2019 |         14 | Yes          |
|  330 | BLT    |   9/7/2019 |         10 | No           |
|  330 | BLT    |   9/7/2019 |         35 | No           |
|  330 | BLT    |  9/14/2019 |          2 | No           |
|  330 | BLT    |  9/14/2019 |         24 | No           |
|  330 | BLT    |  9/21/2019 |          6 | No           |
|  330 | BLT    |  9/21/2019 |         17 | No           |
|  330 | BLT    |  9/28/2019 |          1 | No           |
|  330 | BLT    |  9/28/2019 |         17 | No           |
----------------------------------------------------------