Return data with multi-date criteria in different tables

[Matricus]

Hey fellow data guys.



A specific issue is bothering me a longer time and I was not able to solve it on my own.

I have two tables (tbl1 and tbl2) and want to create a new one.

tbl1 contains the numeric field "aID" and the short date "DateFull". tbl1 contains different data whenever the query is run as it is an import, but its structure remains.
Except for "cID" the other fields are not unique.

cID	eID	aID	DateFull
1	2345	10789	20.11.2018
2	2346	10534	20.11.2018
3	2345	10789	03.04.2018

tbl2 contains additional information for the "aID" value of tbl1.

ID	aID	DateStart	DateEnd	Role
1	10789	01.01.2018	31.05.2018	Role 1
2	10789	01.06.2018		Role 2
3	10534	01.01.2018		Role 1

ID is the only unique field.

I want to create a new table with cID, DateFull of tbl1 and ID, Role of tbl2.

As you can see above, tbl2 contains duplicate aID values.
Another tricky thing is, that DateEnd can be empty. Empty equals current date (date when query runs).

To get the fitting entry I need to check if tbl1.DateFull is between tbl2.DateStart and tbl2.DateEnd and then return the data I need.

The final table should look like this:

cID	DateFull	ID	Role
1	20.11.2018	2	Role 2
3	03.04.2018	1	Role 1
2	20.11.2018	3	Role 1

In the past I did work around it by manually editing the raw data in the database to work around the DateStart/DateEnd problem and simply changing archived data.

Hope you guys can help.

Cheers

[isladogs]

Make a query with both tables and link using the common field aID.
Add all fields you want to the query.
For the DateFull field set filter criteria between DateStart and Nz(DateEnd, Date).
The Nz part should handle null values for DateEnd

[kevinjoseph]

That's Use only one select query in SQL.

[Matricus]

Hmm... correctly applied?

SELECT tbl1.cID, tbl1.DateFull, tbl2.ID, tbl2.Role
FROM tbl1 
INNER JOIN tbl2 ON tbl1.aID = tbl2.aID
WHERE (((tbl1.DateFull)>=[DateStart] And (tbl1.DateFull)<=Nz([DateEnd],Date())));

[isladogs]

Should normally be a comma not a semicolon in the Nz function but that depends on language settings.
Your code sample shows a comma but your screenshot has a semicolon.

Another way of writing it is

Between [DateStart] And Nz([DateEnd],Date())

but yours should also be fine

[Matricus]

Well yeah, company system... Windows is US and primary language setting is UK-English; most Office apps (not all though) require me to use a semicolon for formulas but SQL view for example is comma.
Whatever, thanks.

[isladogs]

Does that mean it is now solved?