SQL Query based on dates that fall within +/- one day of each other

[orangeman2003]

Hi,

I have a query I need help with. It's actually something I want to learn how to do so it's more for practice than anything. I downloaded the NHL schedule in csv and imported it into ACCESS. What I want to do is have a query where I can say 'show me records for the following teams where each team has a home game on a Thu, Fri, Sat, and where that game is within one calendar day plus or minus the home game of the other team'.

So, here would be an example

Toronto Maple Leafs home game on Fri Nov 6 and Montreal Canadiens also have a home game on Sat Nov 7.

The challenging part here is how to represent dates, set it to be within +/- one day of each other

Thanks,
Evan

I don't have code yet because this is well beyond my ability so people who respond just use general sql terms like 'use DATEDIFF' or something like that.

[orangeman2003]

so I was able to get everything minus the 'fall within +/- 3 days of one another'

SELECT [2015_2016_NHL_Schedule].Date, [2015_2016_NHL_Schedule].Time, [2015_2016_NHL_Schedule].Home, [2015_2016_NHL_Schedule].Away, Weekday.Expr1 AS [Day]
FROM 2015_2016_NHL_Schedule INNER JOIN Weekday ON [2015_2016_NHL_Schedule].Date = Weekday.Date
WHERE ((([2015_2016_NHL_Schedule].Home)="Toronto Maple Leafs" Or ([2015_2016_NHL_Schedule].Home)="Montreal Canadiens" Or ([2015_2016_NHL_Schedule].Home)="Ottawa Senators") AND ((Weekday.Expr1)="Thursday" Or (Weekday.Expr1)="Friday" Or (Weekday.Expr1)="Sat" Or (Weekday.Expr1)="Sun") AND ((Month([2015_2016_NHL_Schedule].[Date]))<>"10"));

[ItsMe]

I do not understand what this is doing.
Weekday.Expr1 AS [Day]

I would probably use an alias to define the Weekday but would use the Weekday function.
Weekday([2015_2016_NHL_Schedule].[Date]) AS [Day]

The Weekday() function returns an integer. You can use the value within your Where Criteria. Maybe apply the Criteria via some VBA.

Also, Date is a reserved word. If you have columns in tables or controls or any other object with reserved words as names. you will have problems. You might not have a problem the first day, but a problem will, eventually arise.

[orangeman2003]

my naming is not great, what it is is a function that translates the integer to day of week based on a m/dd/yyyy value. So if a date is recognized to be a sunday it would return 1 and then I added another function to translate that value to string day of week. I'm all set except for one thing which may or may not be possible and if it is it might be possible via filtering and might not need a function.

So far in my query I have made criteria focus on 3 teams, montreal, toronto, and ottawa. When I query on those three teams, remaining home games on thurs, fri, sat, or sun I get 29 records. This is 99% of what I want to do. The remaining 1% is that I want to be able to now say show me instances where 2 of the 3 teams how home games that fall +/- within 2 days of one another.

Example:

Montreal plays on Thursday Nov 5
Toronto plays on Sat Nov 7
Ottawa plays on Tues Nov 10

In this example, 2 out of the 3 teams have a game that falls within 2 days of each other within a thu, fri, sat, and sun. I think the way to do this is using calculated duration but I have no idea how to do that

[ItsMe]

is a function that translates the integer to day of week based on a m/dd/yyyy value

There is no need for a custom function since the built-in Weekday() function will work within a query.

The remaining 1% is that I want to be able to now say show me instances where 2 of the 3 teams how home games that fall +/- within 2 days of one another.

I am not sure I am following, perfectly. You are going to need to grab one date and consider what two days forward are and two day backward are. The criteria would look something like.

WHERE [2015_2016_NHL_Schedule].[Date] Between SomeVariable -3 And SomeVariable +3

Perhaps you can use a Main Form with a subform and apply the WHERE criteria with the BETWEEN operator to the subform's RecordSource. you can assign a date from the main form to SomeVariable.