Access query to loop through date ranges

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
73
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:
AreaDates
33010/5/2019
3309/27/2019
33012/5/2019

<tbody>
</tbody>

Table 2:
AreaStartEnd
3308/1/20199/31/2019
33011/1/201912/31/2019

<tbody>
</tbody>

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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,259
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 14, 2016
Messages
73
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.
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,259
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

one idea:

Code:
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:
Code:
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 ;)
 
Last edited:

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
73
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.

CertResortRAREAStart DateEnd Date
EXCERT273303301/1/20198/31/2019
EXCERT2733033010/1/201910/31/2019

<tbody>
</tbody>

My inventory looks like this:
AreaResortCheck InSizeSumOfUnits
330BLT8/3/201913
330BLT8/3/2019229
330BLT8/10/2019115
330BLT8/10/2019232
330BLT8/17/201917
330BLT8/17/2019240
330BLT8/24/201917
330BLT8/24/2019228
330BLT8/31/201912
330BLT8/31/2019212
330BLT9/7/2019110
330BLT9/7/2019235
330BLT9/14/201912
330BLT9/14/2019224
330BLT9/21/201916
330BLT9/21/2019217
330BLT9/28/201911
330BLT9/28/2019217
330BLT10/5/201911
330BLT10/5/201927
330BLT10/12/201912
330BLT10/12/201925
330BLT10/19/201911
330BLT10/19/201927
330BLT10/26/2019214


TOTAL
324

<tbody>
</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.

AreaResortCheck InSumOfUnitsEXCERT 27
330BLT8/3/20193
330BLT8/3/20193$299
330BLT8/3/201929
330BLT8/3/201929$299
330BLT8/10/201915
330BLT8/10/201915$299
330BLT8/10/201932
330BLT8/10/201932$299
330BLT8/17/20197
330BLT8/17/20197$299
330BLT8/17/201940
330BLT8/17/201940$299
330BLT8/24/20197
330BLT8/24/20197$299
330BLT8/24/201928
330BLT8/24/201928$299
330BLT8/31/20192
330BLT8/31/20192$299
330BLT8/31/201912
330BLT8/31/201912$299
330BLT9/7/201910$299
330BLT9/7/201935$299
330BLT9/14/20192$299
330BLT9/14/201924$299
330BLT9/21/20196$299
330BLT9/21/201917$299
330BLT9/28/20191$299
330BLT9/28/201917$299
330BLT10/5/20191
330BLT10/5/20191$299
330BLT10/5/20197
330BLT10/5/20197$299
330BLT10/12/20192
330BLT10/12/20192$299
330BLT10/12/20195
330BLT10/12/20195$299
330BLT10/19/20191
330BLT10/19/20191$299
330BLT10/19/20197
330BLT10/19/20197$299
330BLT10/26/201914
330BLT10/26/201914$299



536

<tbody>
</tbody>

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

Code:
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.
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,259
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Where is the $299 coming from? I don't see any dollar fields in the first two tables?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Here is my same query with your new tables:

Code:
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           |
----------------------------------------------------------
 
Last edited:

Forum statistics

Threads
1,143,675
Messages
5,720,237
Members
422,271
Latest member
xbaca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top