Fiscal Yearly Progressive numbering (1 Viewer)

[nirmal]

I am trying to get a progressive yearly number on monthly basis for a fiscal year.
I tried the hints given to me on this forum, but there is error in the progressive yearly number, especially for the months of Jan, Feb And March on beginning of new year from 2013 to 2021. Also the same error appears for June 2021. I am attaching a excel sheet where I have depicted the values year wise.
As you will see the error areas have being highlighted Yellow
So Please help me on this

 

[Minty]

When does your fiscal year run from?

When dealing with fiscal periods and fiscal months, I used a calendar table and created the relevant fields (FiscalMonth, FiscalYear etc.) as an easy to use lookup table. You can calculate such things but if you have large datasets the calculations can get very process hungry.

Much easier to join your query to a table with everything you need already in it ready to go.

 

[plog]

I am trying to get a progressive yearly number on monthly basis for a fiscal year.

I cannot parse that. Nor can I discern it from all your attachments. So, I'm just going to tell you what I see wrong with your structure:

1. MonthOfYear table does not need to exist. There are built in date functions that can get you from 1 to January (MonthName()) and January to 1 (CDate() & Month()).

2. You should only store CustomerID in StoreVisited_tbl. CustomerName and Age are both in Customer_tbl, so if you need those pieces of information you obtain them via a query, not by copying the data from Customer_tbl to StoreVisited_tbl.

3. At best, Customer_tbl is improperly named; at worst its not structured properly. I count 5 records with the name ' Yogita Satish Magar'. If this was truly a customer table, then every record would be a unique customer. Why is that person in there 5 times?

4. Action queries. Similar to #2 above--data shouldn't be moved around in a database, it should be referenced. Action queries are a red flag that something is improperly structured and you've tried to put a band-aid on it. I don't know exaactly their purpose but you can generally avoid them with a good structure.

 

[nirmal]

When does your fiscal year run from?

When dealing with fiscal periods and fiscal months, I used a calendar table and created the relevant fields (FiscalMonth, FiscalYear etc.) as an easy to use lookup table. You can calculate such things but if you have large datasets the calculations can get very process hungry.

Much easier to join your query to a table with everything you need already in it ready to go.

My fiscal year starts from 1st April and ends on 31st March next year

 

[arnelgp]

check and test again, compare to your excel file.

 

[nirmal]

check and test again, compare to your excel file.

Sir now the expected results are appearing in the report

 

[nirmal]

check and test again, compare to your excel file.

Sir I am trying to understand the coding, but it seems to be quiet complicated.
Sir , can you please simplify it

The "Private Sub Report_Open(Cancel As Integer)" is affecting other reports of my original database which are not dependent on the "MonthlyReportSearch_frm"

So, suggest some other solution Sir

 

[nirmal]

Sir thank U for your guidance, I could learn some amount of coding
Thanks Once Again