Countiif between dates in crosstab query (1 Viewer)

Cassim

New member
Local time
Today, 13:24
Joined
Nov 4, 2013
Messages
8
Hi Guys,

I am new to MS Access and I have a tiny problem :rolleyes:

I have genetated a table using a crosstab query and it gives the information as below

12/10 12/11 12/12 13/01
Part 1 1 2
Part 2 4 4 4
Part 3 5

So basically part, qty used, year/month used - it works fine up to this point.

Now I want to either add more columns to the crosstab query to count number of times Part 1 was used in last 12 months & 24-13 months

Or

Create another query to do this.

But I want the count function to start counting from current month to last 12 months and from month 13 to month 24 (in another column).

The problem I am having is that this query will run every month and I don't want to update the column headers (in query property sheet) instead I want query to pick this up automatically.

I use the design view to generate queries so much appreciate if you could advise me of a solution in that "View".

Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:24
Joined
Feb 19, 2002
Messages
42,970
If you use the query wizard to build the crosstab, it automatically includes a totals column.

The criteria for the query should be based on a date range.
WHERE somedate Between Forms!frmcriteria!txtFromDate and Forms!frmcriteria!txtThruDate

The form can populate the from and through dates automatically based on the current date but it should allow overrides so you can run the report for any date range, not just the most recent.

You can change the column names in the query so they will always be the same. That makes the report easier to create. Then you can have code in the report section for the column headers to change the captions based on the date range of the criteria form.

I don't have time to write the code for the function for you but the query would look like
Select StandardizeName(MyDate) As CalcDate, someotherfield, someotherfield, ...

Then pivot on CalcDate rather than MyDate. Your function should take the date in MyDate and using the date range specified on the form convert mydate to Month01 - Month12 depending on where MyDate falls in the specified range.

Then in the report do the opposite so you convert Month01 to the first year/month of the date range etc.
 

Users who are viewing this thread

Top Bottom