Get value from another table - Dlookup maybe?

[oton]

Hi champs!

I tried to find the best way of doing it but no luck so far. Could anyone help?

I have 2 tables and I need to (automatically) get into one of the tables a very specific value (Quarter) from another table based on a Date Range. Here it is:


Table 1:

ContractID	Client	ContractDate	Quarter
1	Client A	23-Mar-18	?
2	Client B	07-Jun-18	?
3	Client C	16-Aug-18	?

Contract Number (auto number)
Contract End Date
Quarter (automatically from table "Quarter Matrix based on the Contract End Date Field)

Table 2:

Quarter	Start Date	End Date
Q1	01-Jan-18	30-Mar-18
Q2	31-Mar-18	29-Jun-18
Q3	30-Jun-18	28-Sep-18
Q4	29-Sep-18	28-Dec-18

Essentially, I need (either on the actual table 1 or on a query) MS Access to auto populate the Field "Quarter" using the "Contract End Date" from table 1 as reference to lookup from table 2.

Tried in so many ways (IIF, Dlookup, etc) but I cannot get it right.

I have attached the MS file simple sample just in case someone may want to see what that looks like.

any help?

[isladogs]

One way of doing this as a select query is to use both tables with no join (aka Cartesian join) then filter where contract date is between start date and end date
See query qrySelectQtr in the attached database

If you want to populate the field in the Contracts table, use an update query based on the first query

NOTE: I've added 2 more records for info - one for 16/12/2018 (Q4) and another for 29/12/2018
As the last date isn't in your table, the Qtr field is left blank

[June7]

I've never seen quarter periods that didn't begin and end with first and last days of months. This would allow use of DatePart() function to calculate the quarter.

Cartesian relationship query and DLookups() can perform slowly with very large datasets.

Here is the correct DLookup() if you want to test for comparison:

SELECT Contracts.*, DLookUp("Quarter","Quarter Matrix","#" & [ContractDate] & "# BETWEEN [Start Date] AND [End Date]") AS QTR FROM Contracts;


Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

[isladogs]

I've never seen quarter periods that didn't begin and end with first and last days of months. This would allow use of DatePart() function to calculate the quarter.

Strangely this is the third post I've answered in the last fortnight with non-standard quarters.
Another was the teams database by Daisy509

Whoever decided on these dates needs to be hung, drawn & quartered!!!!

[June7]

A bit bloody for me. I'll settle for tarred and feathered and run out of town on rails!

Missed that detail about Daisy509th database.

[isladogs]

The 'quartered' bit seems more appropriate somehow

[oton]

One way of doing this as a select query is to use both tables with no join (aka Cartesian join) then filter where contract date is between start date and end date
See query qrySelectQtr in the attached database

If you want to populate the field in the Contracts table, use an update query based on the first query

NOTE: I've added 2 more records for info - one for 16/12/2018 (Q4) and another for 29/12/2018
As the last date isn't in your table, the Qtr field is left blank

Brilliant !.. Ridder52..
that was exactly what I needed.
Thanks a Lot !!

[oton]

Hello Champs

The reason behind the funny dates behind the Quarter is an internal financial calendar which does not follow the standard calendar... that is y.

Thanks a lot for the help

Strangely this is the third post I've answered in the last fortnight with non-standard quarters.
Another was the teams database by Daisy509

Whoever decided on these dates needs to be hung, drawn & quartered!!!!

[isladogs]

Glad you're happy with it.
What about the final three days of the year?
How will you manage future years? For example Q1 2019?

[oton]

Hi Ridders52

By the end of 2018 the dates for 2019 will be defined and entered on the Quarter Matrix. That will drive all new entries on the contract table.

Thanks again

Glad you're happy with it.
What about the final three days of the year?
How will you manage future years? For example Q1 2019?