Full Version: Query for Quarters and Monthly
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
kc_beckwith
Is there a way that I can setup a quarterly and monthly query based on field, such as department and a date?

Here's my problem..

I have a one to many relationship with my one table that has id, fname, lname, lanid, and department for fields. My many table has dateid, id, lanid, date, and month. I have departments, such as Accounting and Financial mgt that need to be queried quarterly based on the department, date, and month and other departments, such as logistics queried monthly based on the department, date, and month. What would be the most effective way of extracting the data?
HiTechCoach
Welcome to Utter Access!

Yes, it should be possible to do.

Check out:
Functions for calculating and for displaying Date/Time values in Access
kc_beckwith
Thank you. I looked the website and cannot find anything relating to querying for quarters I want to use it to set up quarterly reports for certain departments.
emanday
Try this. Might be what you need.

"http://support.microsoft.com/search/default.aspx?catalog=LCID%3D2057&ast=1%2C2%2C3%2C8%2C9&spid=2509&mode=a&cat=true&kt=BOOLEAN&title=false&mdt=&pwt=False&comm=1&query=quarter+OR+qrt&srch=sup&x=7&y=12"
jmcwk
Use the query with you date and format it for month,quarter etc. If you need help with that let me know

Basically you need to use your date field in your query that is the recordsource for your query and format that field accordingly.
Month: Format([yourdatefieldhere],"mmmm / yyyy")
Quarter: Format([yourdatefieldhere],"q/yyyy")

I also noticed you identified some of the fields in your table as Date,Month these are reserved words in Access and should not be used as field names.




Edited by: jmcwk on Sat May 6 19:07:46 EDT 2006.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.