How to categorize Quarters based on Start Date and End Date

[Mahendra1000]

Hello Friends,

Am struck with a problem in my project. Kindly help me out how to sort it out.

I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.

The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year

For Example,

If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).

2nd Example: We need to consider the End Date for classifying the quarters, Month and Year

If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).



The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.

Could any one send me the sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.

i will see the logic and will sort it out.

Please help me out

[June7]

Quarter, month, year should not be saved into table. They should be calculated in query when needed.

[Mahendra1000]

Hello June7,

Thanks for the Reply.

Could you kindly send me a sample demo with just 3 examples please.

[June7]

Demo of what? A query with calculations? Don't you already know how to build query with calculated fields?

[Mahendra1000]

A demo of sample sheet with 3 examples with Start Date, End Date, Quarter, Month and Year fields

To be genuine am an novice to MS Access and I work in another domain of database. The person who did it is in a long vacation and I have been assigned to this changes.

I don't know how to build queries with calculated fields. Please assist me

[June7]

Search Access Help and web. Any introductory book on Access will have info. Lots of guidance out there.

Bing: Access query calculated field

http://ms-access-tips.blogspot.com/2...n-queries.html

Use functions to extract date parts.

Month([End Date])

Year([End Date])

DatePart("q", [End Date])

[Mahendra1000]

Hello June7,

Thanks for the suggestions. I worked on the month part and it worked.. I used the below formula

MonthName(Month([EndDate]])

For year

Year([End Date]) is not working. Could you let me know the exact formala for finding out the year and quarter

[June7]

That is correct expression for extracting year. What does 'not working' mean - error message, wrong results, nothing happens?

Use DatePart function for the quarter as already suggested.

http://www.techonthenet.com/access/functions/index.php

http://www.quepublishing.com/article...43872&seqNum=3

[Mahendra1000]

It displaying the values in the year and quarter column as

#Name?

[June7]

It is not finding the field named [End Date]. Is there actually a space in the name? You don't show a space in the month expression. Make sure spelling is correct.