Filter Query with Date Range

[NewbieAccessor]

Hello!

I have only been using Access for 24 hours and need some help!!

I have a Query that has totalled the number of unique Projects allocated to my group. This is good!

Now, this total, I need to maintain it - but then also break it down into when the Projects were allocated per month.

What I've done is copied the Grand Total column and pasted it 12 times to the right of the total colum and changed the Caption to Jan through Dec.

Now, what I need and where I'm getting stumped is - the date range Expression to put into Access so that I can isolate for each month - what was allocated when.

The Query will be used in 2 ways - one to give an overall group view of the number of projects allocated out for the year and then broken to each month - so that we can assess Resource Allocations and then used again for the individual Project Manager to see what their total is for the year and then each month.

I would think that this should be super-simple but I'm at a loss... The databse already knows when each of the projects has been or is set to Kick-Off but for the life of me, I can't seem to get the query to break it down into each month's data.

Help! Thanks!

[Stewart Ross]

Hi. A problem which often comes up if you don't know a lot about database design is that you can come up with what appears a reasonable approach based on what you would do in a spreadsheet - like creating separate side-by-side fields for monthly totals, or fields for grand totals. You should rarely be storing any calculated field at all, but doing so is not the concern here.

Access, like all relational databases, is not intended to handle repeating field groups within a record. Your side-by-side month date totals are a repeating group. Their inclusion violates the first normal form of database design. Month totals can be calculated when needed from the individual item dates - dates which are only stored once. Presentation of the fields in an Excel-like table with Jan, Feb, Mar etc side by side can be done using a Crosstab query, but the data is not stored in this format at all.

Rather than try to calculate month totals which, unfortunately, are based on flawed designs of your tables, I would strongly advise you to modify your tables now. We have an article on database normalisation and table structures which outlines the basic principles involved. It is essential that you follow such principles or you will never harness the power of a relational database such as Access.

I'm sorry that I can't really advise how you could use your existing totals. In my opinion you will find it much more effective in the medium to long term to revise your design now before it becomes too complex to do so!

Regarding breaking down number of projects into monthly totals, if you have a project date stored for each project you can use functions such as Year and Month on your project date in an Access query to provide you with the year and month for the project. You can then use grouping and totalling or a crosstab in your query to provide you with monthly totals.

There are examples provided in databases such as the Northwind sample which is shipped with Access.

Welcome to Bytes!

-Stewart

[NewbieAccessor]

Hi Stewart!

Thanks for the response! I'm actually just creating queries and then reports from those queries from an existing database that has been set-up by our global is department. I'm just trying to figure out how to pull the data out.

What I already have is a query to the database that pulls out all the projects that our group has worked on. The project data already has the date info that I require. I have then built a report that takes the query and displays it in a nice way that is easier to read than Excel like cells, rows and columns.

The report is already grouping the projects by the coordinator and it is already calculating the timeframe from the Kick-Off to the Completion dates - as entered in the database and being brought into the query and then to the report.

I started to build a new query whereby I could calculate the total projects - which I was able to do in a couple minutes and is correctly giving the same number as the initial query total records.

I was hoping that I could then create a query that would do double-duty - as a total and month breakout that I could then drop into a report for an overview report of the entire group - and then using the same query as a sub-query to the report that would calculate the total per coordinator and their individual projects assigned and completed.

If I need to create multiple queries then I will but I have even tried taking the query that gives me the correct total number of projects and then in the Criteria, I've tried building numerous expressions like: "InitialDat e = between 1/1/10 and 1/31/10" or >=1/1/10 and <=1/31/10... etc. I just cannot even get one filter, sort or criteria expression to work... I'm also referencing a manual I bought and tried everything that I could see in it, even using IF or WHERE to no avail...

It just seems really odd that it would be so difficult to figure out a simple calculation based on a date range!

Help!

[Stewart Ross]

Hi. Date/Time values are not stored as text but as numbers with whole-number components (for the date) and decimal fractional parts (for the time). If you want to use a date range in an expression you need to tell Access that the date literal is a date and not some text. You do this by enclosing your dates with the pound character '#', indicating that the text within is a date literal.

Using one of your examples a possible criterion in your query would be:

between #1/1/10# and #1/31/10#

You could also use calculated fields referring to the year and month as I mentioned. This can be done in many different ways, including the use of date formatting to generate a year and month text string which you can then GROUP BY in your query. In SQL syntax the Format version of doing so could be:

SELECT ..., Format(InitialDate, "YYYY-MM") as YearMonth, ... FROM ...

-Stewart

PS if you want to group your query by month or year you must remove instances of the specific project dates from the query (except where these are inputs to a summarising function such as Year, Month, Format etc). If you retain even one InitialDate field you will be unable to group the query into months as the specific dates concerned will be the highest level at which the query can operate. This also applies to any other field which identifies a specific project - a project ID for instance.