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

 

[Andrzejek]

declared" - is it TEXT? DATE? NUMBER?

Can you make the SELECT statement I gave you to return just ONE records?
Something like:

[tt]SELECT CustomerNo, SalespersonNo, TransactionDate, InvoiceNo,
InvoiceDate, ProductLine, ItemCode
FROM SalesByPLAndStateDetails
WHERE CustomerNo = [red]1234[/red][/tt]

And if you do get just one records, could you share it here?

>I feel like this should be easier than it is?
Yes, it would be - if you would answer my questions, or be open to create a relationship between two tables by Primary Key - Foreign Key connection. So your tables would look similarly to this:

---- Andy

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

 

[supportsvc]

dhookom,
The FiscalYear table isn't sufficient?

So you're saying a table of ALL dates to match to the TransactionDate should be created with the Fiscal information?
I'm not sure who's going to maintain that

It seems like there's should be a way to look between the PeriodStartDate and the PeriodEndingDate and if a date exists in the TransactionDate to return the associated values re: FiscalYear and FiscalPeriod in that range?

 

[supportsvc]

Andrzjek,

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, FiscalYears
WHERE (((SalesByPLAndStateDetails.InvoiceNo)="0115296"));

it returns 49 times the same record which is equal to the number of records from the FiscalYears table but return NULL values for teh FiscalCalYear and FiscalCalPeriod

Again, I don't know how you can create a PKField in the SalesByPLandState table to then link to the FiscalYears table

Requirement:
1. There are invoice records in the SalesByPLandState table which includes TransactionDate, date of the posted invoice.
2. The FiscalYears table has the information as shown which defines the start and end date of a period and year (Fiscal Year and Period data)
3. Need to know what Fiscal Period and Fiscal Year the TransactionDate falls into

I uploaded the db with the static tables. Perhaps I'm not explaining correctly.

https://files.engineering.com/getfi...40e0-ad30-6d2bd0d7f318&file=FiscalYears.accdb

 

[dhookom]

Supportsvc,
It seems you have many questions about the limited date range solution including "It took over 3.5 hours and it's incorrect. It is saying all the records on Period 10 or 11".

I expect the calendar table would be easy to create and update. I should also be much more efficient in terms of time to return results.

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

 

[supportsvc]

Is there a way to do a Lookup option?

Found this but's it's basic same idea pulling info from one table based on what's in another table

https://stackoverflow.com/questions...cores-from-another-table-based-on-birth-dates

But's it's SQL Db lingo?

 

[supportsvc]

Ok, this is working

SELECT SalesByPLAndStateDetails.CustomerNo, SalesByPLAndStateDetails.SalespersonNo, SalesByPLAndStateDetails.TransactionDate, SalesByPLAndStateDetails.InvoiceNo, SalesByPLAndStateDetails.InvoiceDate, SalesByPLAndStateDetails.ProductLine, SalesByPLAndStateDetails.ItemCode, IIf([TransactionDate]>=[PeriodStartDate] And [TransactionDate]<=[PeriodEndingDate],[FiscalYears].[FiscalYear],Null) AS FiscalCalYear, IIf([TransactionDate]>=[PeriodStartDate] And [TransactionDate]<=[PeriodEndingDate],[FiscalYears].[FiscalPeriod],Null) AS FiscalCalPeriod
FROM SalesByPLAndStateDetails, FiscalYears
WHERE (((IIf([TransactionDate]>=[PeriodStartDate] And [TransactionDate]<=[PeriodEndingDate],[FiscalYears].[FiscalYear],Null)) Is Not Null) AND ((IIf([TransactionDate]>=[PeriodStartDate] And [TransactionDate]<=[PeriodEndingDate],[FiscalYears].[FiscalPeriod],Null)) Is Not Null));

 

[Andrzejek]

It would behoove you to read, understand and implement the functions of Primary Key and Foreign Keys in a Relational database

Just my opinion...

---- Andy

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

 

[supportsvc]

I understand relational databases and primary keys
however it doesn't work here from what I can tell

Thank you for your help!

As noted in my last post, the query shared works!

 

[Andrzejek]

How about Foreign Keys...

---- Andy

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