Date Range within Date?

[supportsvc]

Hello,
I am trying to figure how to find dates in TransactionDate that falls into the PeriodEndingDate

So company does not use the calendar year for their fiscal year.

TransactionDate contains dates when the invoice transaction posted.
Each FiscalPeriod for each year doesn't necessary end on the last day of the month.

How do you set two new fields to look at the TransactionDate and if it falls into the PeriodEndingDate show
1. the FiscalYear
2. the FiscalPeriod
As shown in the Fiscal table?

So from the TransactionDate to determine the Fiscal information if the dates fall into the PeriodEndingDate

These are their Fiscal Periods
FiscalYear FiscalPeriod PeriodEndingDate
2020 03 6/30/2019
2020 04 7/28/2019
2020 05 8/25/2019
2020 06 9/29/2019
2020 07 10/27/2019
2020 08 11/24/2019
2020 09 12/29/2019
2020 10 1/26/2020
2020 11 2/23/2020
2020 12 3/29/2020
2021 01 4/26/2020
2021 02 5/24/2020
2021 03 6/28/2020
2021 04 7/26/2020
2021 05 8/23/2020
2021 06 9/27/2020
2021 07 10/25/2020
2021 08 11/22/2020
2021 09 12/27/2020
2021 10 1/24/2021
2021 11 2/21/2021
2021 12 3/28/2021

 

[SkipVought]

Hi,

SELECT y.TransactionDate, f2.FiscalYear, f2.FiscalPeriod
FROM [YourTable] y, 
 FISCAL f1, FISCAL f2
WHERE (f1.FiscalPeriod = f2.FiscalPeriod-1 AND f1.FiscalYear = f2.FiscalYear
   OR f1.FiscalPeriod = f2.FiscalPeriod+11 AND f1.FiscalYear = f2.FiscalYear-1)
  AND y.TransactionDate > f1.PeriodEndingDate 
  AND y.TransactionDate <= f2.PeriodEndingDate

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[supportsvc]

Hello SkipVought
and thank you

Not sure why it's asking about the 'FISCAL'?

Warning
The Microsoft Access datbase engine cannot find the input table or query 'FISCAL'. Make sure it exists and that its name is spelled correctly.

So the TransactionDate is coming from 'SalesByPLandStateDetails' table and the Fiscal data is coming from GL_FiscalYearDetail table having the following fields:
FiscalYear
FiscalPeriod
PeriodEndingDate

in WIP:

SELECT y.TransactionDate, f2.FiscalYear AS Expr1, f2.FiscalPeriod AS Expr2
FROM SalesByPLandStateDetails AS y, FISCAL AS f1, FISCAL AS f2
WHERE (((y.TransactionDate)>[f1].[PeriodEndingDate] And (y.TransactionDate)<=[f2].[PeriodEndingDate]) AND (([f1].[FiscalPeriod])=[f2].[FiscalPeriod]-1) AND (([f1].[FiscalYear])=[f2].[FiscalYear])) OR (((y.TransactionDate)>[f1].[PeriodEndingDate] And (y.TransactionDate)<=[f2].[PeriodEndingDate]) AND (([f1].[FiscalPeriod])=[f2].[FiscalPeriod]+11) AND (([f1].[FiscalYear])=[f2].[FiscalYear]-1));

 

[SkipVought]

You also need to change table FISCAL to GL_FiscalYearDetail.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[supportsvc]

Ah, makes sense now

Is there a way to make this run faster?
It's really slow to process

 

[supportsvc]

Is there a way to make this faster?

It took over 3.5 hours and it's incorrect. It is saying all the records on Period 10 or 11

The GL_FiscalYearDetails has 255 records
The SalesByPLAndStateDetails table has 22625 records and growing daily when there are posted invoices/transactions

I created another table FiscalYearDetails from teh GL_FiscalYearDetails table and added the PeriodStartDate to perhaps use a date range instead

FiscalYear FiscalPeriod PeriodStartDate PeriodEndingDate
2019 01 3/30/2018 4/29/2018
2019 02 4/30/2018 5/27/2018
2019 03 5/28/2018 7/1/2018
2019 04 7/2/2018 7/29/2018
2019 05 7/30/2018 8/26/2018
2019 06 8/27/2018 9/30/2018
2019 07 10/1/2018 10/28/2018
2019 08 10/29/2018 11/25/2018
2019 09 11/24/2018 12/30/2018
2019 10 12/31/2018 1/27/2019
2019 11 1/28/2019 2/24/2019
2019 12 2/25/2019 3/31/2019
2020 01 4/1/2019 4/28/2019
2020 02 4/29/2019 5/26/2019
2020 03 5/27/2019 6/30/2019
2020 04 7/1/2019 7/28/2019
2020 05 7/29/2019 8/25/2019
2020 06 8/26/2019 9/29/2019
2020 07 9/30/2019 10/27/2019
2020 08 10/26/2019 11/24/2019
2020 09 11/25/2019 12/29/2019
2020 10 12/30/2019 1/26/2020
2020 11 1/27/2020 2/23/2020
2020 12 2/24/2020 3/29/2020
2021 01 3/30/2020 4/26/2020
2021 02 4/27/2020 5/24/2020
2021 03 5/25/2020 6/28/2020
2021 04 6/27/2020 7/26/2020
2021 05 7/25/2020 8/23/2020
2021 06 8/24/2020 9/27/2020
2021 07 9/28/2020 10/25/2020
2021 08 10/24/2020 11/22/2020
2021 09 11/23/2020 12/27/2020
2021 10 12/28/2020 1/24/2021
2021 11 1/25/2021 2/21/2021
2021 12 2/22/2021 3/28/2021
2022 01 3/29/2021 4/25/2021
2022 02 4/26/2021 5/23/2021
2022 03 5/24/2021 6/27/2021
2022 04 6/28/2021 7/25/2021
2022 05 7/26/2021 8/22/2021
2022 06 8/23/2021 9/26/2021
2022 07 9/27/2021 10/24/2021
2022 08 10/25/2021 11/21/2021
2022 09 11/22/2021 12/26/2021
2022 10 12/27/2021 1/23/2022
2022 11 1/24/2022 2/20/2022
2022 12 2/21/2022 3/27/2022
2023 01 3/28/2022 4/24/2022


From that I tried an Update query and adding FiscalYear and FiscalPeriod fields into the SalesByPLAndStateDetaiils table

UPDATE SalesByPLAndStateDetails, FiscalYears SET SalesByPLAndStateDetails.Fiscalyear = IIf([TransactionDate] Between [PeriodStartDate] And [PeriodEndingDate],[FiscalYears].[FiscalYear]), SalesByPLAndStateDetails.FiscalPeriod = IIf([TransactionDate] Between [PeriodStartDate] And [PeriodEndingDate],[FiscalYears].[FiscalPeriod]);

However it did not update

I set it to view the data and it repeats and only has 2020 and 10 updating?

SELECT SalesByPLAndStateDetails.CustomerNo, SalesByPLAndStateDetails.SalespersonNo, SalesByPLAndStateDetails.TransactionDate, SalesByPLAndStateDetails.InvoiceNo, SalesByPLAndStateDetails.InvoiceDate, SalesByPLAndStateDetails.ProductLine, SalesByPLAndStateDetails.ItemCode, IIf([TransactionDate] Between [PeriodStartDate] And [PeriodEndingDate],[FiscalYears].[FiscalYear]) AS FiscalCalYear, IIf([TransactionDate] Between [PeriodStartDate] And [PeriodEndingDate],[FiscalYears].[FiscalPeriod]) AS FiscalCalPeriod
FROM SalesByPLAndStateDetails, FiscalYears;

What can be changed so it reads the PeriodStartDate and PeriodEndingDate from the TransactionDate and display the FiscalPeriod and FiscalYear from the FiscalYearDetails table?

 

[Andrzejek]

If your [tt]FiscalYearDetails[/tt] table is a 'look-up' table, why not introduce a [blue]PK[/blue] field and than use it as a Foreign Key field in any other table(s) where you need to reference this information...

[pre]
table: FiscalYearDetails
[blue]F_ID[/blue] FiscalYear FiscalPeriod PeriodStartDate PeriodEndingDate
[blue]1[/blue] 2019 1 3/30/2018 4/29/2018
[blue]2[/blue] 2019 2 4/30/2018 5/27/2018
[blue]3[/blue] 2019 3 5/28/2018 7/1/2018
[blue]4[/blue] 2019 4 7/2/2018 7/29/2018
[blue]5[/blue] 2019 5 7/30/2018 8/26/2018
[blue]...
[/blue]
[blue]44[/blue] 2022 8 10/25/2021 11/21/2021
[blue]45[/blue] 2022 9 11/22/2021 12/26/2021
[blue]46[/blue] 2022 10 12/27/2021 1/23/2022
[blue]47[/blue] 2022 11 1/24/2022 2/20/2022
[blue]48[/blue] 2022 12 2/21/2022 3/27/2022
[blue]49[/blue] 2023 1 3/28/2022 4/24/2022
[/pre]
... or I am missing something here...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I typically create a long date table with the date field as the primary key. Add columns for fiscal year, fiscal month, holiday name, etc. You will find lots of examples and how to easily create and populate.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[supportsvc]

Hello Andrzjek and dhookom,

Andrzjek,
I can add the ID field into the FiscalYearDetails but then how do you use it against the other table with TransactionDates?


dhookom,
the FiscalYearDetails is that table??

 

[Andrzejek]

I would assume your TransactionDates table has a (date?) field of the transaction that you want to use to reference the record from FiscalYearDetails table.

So, if you have a record in TransactionDates table:

[pre]
ID SomeField TransDate OtherField [blue]F_ID_FK[/blue]
12 ABCD [green]5/5/2018[/green] XYZ [blue]2[/blue]
[/pre]

Your records with 5/5/2018 would reference FiscalYearDetails table's F_ID's [blue]2[/blue] because 5/5/2018 falls between 4/30/2018 and 5/27/2018

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[supportsvc]

The SalesByPLandStateDetails has the field name TransactionDate

I am sorry, I am not following how the F_ID_FK would work as it does not exist in SalesByPLandStateDetails?

 

[Andrzejek]

I have to guess what tables and fields you have, so what I said before is my best guess.

So, if the TransactionDate is a field in SalesByPLandStateDetails table, I would add a new field to this table (F_ID_FK ?) which you can populate getting the data from FiscalYearDetails.F_ID

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[supportsvc]

Sorry, I'm still not following
I can add the F_ID_FK field in both tables however how in the world would they link up??

 

[Andrzejek]

No.
You have a table called [tt]FiscalYearDetails[/tt] with fields: [tt]FiscalYear, FiscalPeriod, PeriodStartDate,[/tt] and [tt]PeriodEndingDate[/tt]. I would add a Primary Key field (F_ID or whatever you want to call it) to [tt]FiscalYearDetails[/tt] table.

Also, you have a table [tt]SalesByPLandStateDetails[/tt] where you have a field [tt]TransactionDate[/tt] - that's the table where I would add a Foreign Key field that would reference a PK from [tt]FiscalYearDetails[/tt] table.

This way all records from [tt]SalesByPLandStateDetails[/tt] table will have corresponding information from [tt]FiscalYearDetails[/tt] table when you connect the two by PK-FK

Isn't that your requirement? Have corresponding [tt]FiscalYear, FiscalPeriod, PeriodStartDate,[/tt] and [tt]PeriodEndingDate[/tt] (all of these fields or just one or some of them) for each record in [tt]SalesByPLandStateDetails[/tt] table? So you will have a One-to-many relationship

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[supportsvc]

Hello,
I'm sorry, I don't understand HOW?

I would add a Foreign Key field that would reference a PK from FiscalYearDetails table.

This way all records from SalesByPLandStateDetails table will have corresponding information from FiscalYearDetails table when you connect the two by PK-FK

Looking to produce the corresponding FiscalYear and FiscalPeriod based on the PeriodStartDate and PeriodEndingDate in FiscalYearDetails from the TransactionDate from SalesByPLAndStateDetails

I was attempting to do the IIf to lookup the date range from one to the other but this isn't working

SELECT SalesByPLAndStateDetails.CustomerNo, SalesByPLAndStateDetails.SalespersonNo, SalesByPLAndStateDetails.TransactionDate, SalesByPLAndStateDetails.InvoiceNo, SalesByPLAndStateDetails.InvoiceDate, SalesByPLAndStateDetails.ProductLine, SalesByPLAndStateDetails.ItemCode, IIf([TransactionDate] Between [PeriodStartDate] And [PeriodEndingDate],[FiscalYears].[FiscalYear]) AS FiscalCalYear, IIf([TransactionDate] Between [PeriodStartDate] And [PeriodEndingDate],[FiscalYears].[FiscalPeriod]) AS FiscalCalPeriod
FROM SalesByPLAndStateDetails, FiscalYears;

 

[Andrzejek]

Not tested....

[pre]
SELECT
CustomerNo,
SalespersonNo,
TransactionDate,
InvoiceNo,
InvoiceDate,
ProductLine,
ItemCode,
([blue]Select FiscalYear From FiscalYearDetails
Where FiscalYear.PeriodStartDate >= TransactionDate
And FiscalYear.PeriodEndingDate <= TransactionDate[/blue]) AS FiscalCalYear,
([green]Select FiscalPeriod From FiscalYearDetails
Where FiscalYear.PeriodStartDate >= TransactionDate
And FiscalYear.PeriodEndingDate <= TransactionDate[/green]) AS FiscalCalPeriod
FROM SalesByPLAndStateDetails
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[supportsvc]

Thank you

it is producing NULLs

SELECT SalesByPLAndStateDetails.CustomerNo, SalesByPLAndStateDetails.SalespersonNo, SalesByPLAndStateDetails.TransactionDate, SalesByPLAndStateDetails.InvoiceNo, SalesByPLAndStateDetails.InvoiceDate, SalesByPLAndStateDetails.ProductLine, SalesByPLAndStateDetails.ItemCode, (Select FiscalYear From FiscalYears
  Where FiscalYears.PeriodStartDate  >= TransactionDate 
    And FiscalYears.PeriodEndingDate <= TransactionDate) AS FiscalCalYear, (Select FiscalPeriod From FiscalYears
  Where FiscalYears.PeriodStartDate  >= TransactionDate 
    And FiscalYears.PeriodEndingDate <= TransactionDate) AS FiscalCalPeriod
FROM SalesByPLAndStateDetails;

 

[Andrzejek]

How about this...?
Create SELECT statement that returns just one record from [tt]SalesByPLAndStateDetails[/tt] table, something like:
[tt]
SELECT CustomerNo, SalespersonNo, TransactionDate, InvoiceNo,
InvoiceDate, ProductLine, ItemCode
FROM SalesByPLAndStateDetails
WHERE [red]PKField = a_value[/red]
[/tt]
How is [tt]TransactionDate[/tt] field declared in your table?
How are [tt]PeriodStartDate[/tt] and [tt]PeriodEndingDate[/tt] fields declared?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[supportsvc]

There is no PKField

Not sure what do you be "declared"?

I feel like this should be easier than it is?
Not sure why the if statement isn't producing any results?

IIf([TransactionDate] Between [PeriodStartDate] And [PeriodEndingDate],[FiscalYears].[FiscalYear]) AS FiscalCalYear, IIf([TransactionDate] Between [PeriodStartDate] And [PeriodEndingDate],[FiscalYears].[FiscalPeriod]) AS FiscalCalPeriod
FROM SalesByPLAndStateDetails, FiscalYears;

NOTE: sorry, table name is "FiscalYears" not "FiscalYearsDetails

 

[dhookom]

Again, I recommend a calendar lookup table with all dates. It's fairly easy to create this (with appropriate other columns) in Excel and then import it into your database. Make the date field in this calendar table the primary key. Then just link the calendar date field to your TransactionDate field.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016