Count over multiple ranges query

[TashiraRonin]

I need to break down data such that it performs a count of one field but based on a date field

I have a table with Fields

ID (this is the primary key)
Assessment_Assigned_Number
Assessment_Type
Date_of_Application
Funding_Type
Etc


I need a report which says
Etc
Date range Assessment type 1 Assessment type2 Assessment type 3
1/7/03 to31/6/04 1 2 4
1/7/04 to31/6/05 5 8 12
1/7/05 to31/6/06 2 2 5
1/7/06 to31/6/07 3 5 2
1/7/07 to31/6/08 2 9 8


And I am not sure how I get from the field to the table

What queries, I am assuming more than one, will I need to create to get from table data to out put

 

[dhookom]

I would start by creating a table of date ranges with FromDate, ToDate, and RangeName fields. You could then use this table in a crosstab query where the FromDate and ToDate are the Row Headings, AssessmentType is the Column Heading, and Count of AssessmentType is the value.

Duane
Hook'D on Access
MS Access MVP

 

[TashiraRonin]

Ok I created a new Table "FinancialYearDateRanges" and then created a query "YearCrosstabSource" with the Fields from that new table and the table above "ApplicationTitleandKey", so that I would have a single query with all necessary colums to make my crosstab.
However I am not sure how to get either the crosstab or the crosstab query source to use the DateRanges to look at the Date_of_application Field and to do it such that it uses the unique id for each so it does not count each assessment more than once.
So in the query source so that I can generate the crosstab I have two tables with Fields

Table called [FinancialYearDateRanges]
ID (this is the primary key)
ToDate
FromDate
RangeName ( eg 98-99 Fincial Year)

and

Table Called [ApplicationTitleandKey]
ID (this is the primary key)
Assessment_Assigned_Number
Assessment_Type (text)
Date_of_Application (date)
Funding_Type (text)
I have made sure none of these use look up

When I tried it unjoined it gave me identical data in all each column. I could not see any sense in joining the ID from the tables together, or at least not what kinf of join would make it work
Soooo it is the process to get the date field to count by the Range I do not understand.

Is this a case where the crosstab is contructed in SQL rather than through the wizard? If so please advise as I am not sure I understand the syntax to achieve that outcome.

THANK YOU

 

[dhookom]

You need to place both tables in your crosstab and set the criteria under the Date_Of_Application to
Between ToDate AND FromDate

This assumes your date ranges don't overlap or you may double-count.


Duane
Hook'D on Access
MS Access MVP

 

[TashiraRonin]

Ok I have

TRANSFORM Count([Application Title and Key].[Assessment Type]) AS [CountOfAssessment Type]
SELECT FinancialYearDateRanges.FromDate, FinancialYearDateRanges.ToDate
FROM FinancialYearDateRanges, [Application Title and Key]
WHERE ((([Application Title and Key].[Application Eligibility Date]) Between "ToDate" And "FromDate"))
GROUP BY FinancialYearDateRanges.FromDate, FinancialYearDateRanges.ToDate
PIVOT [Application Title and Key].[Assessment Type];

but it gives me "Data mismatch in expression"

all date fields are date with no input masks
all other fields are text with no look ups

Again thank you for the handholding, this is making more and more sense as you help.

 

[lespaul]

well, if nothing else the quotes should be removed, I can't tell from looking if there's anything else that should be changed...sorry...

TRANSFORM Count([Application Title and Key].[Assessment Type]) AS [CountOfAssessment Type]
SELECT FinancialYearDateRanges.FromDate, FinancialYearDateRanges.ToDate
FROM FinancialYearDateRanges, [Application Title and Key]
WHERE ((([Application Title and Key].[Application Eligibility Date]) Between ToDate And FromDate))
GROUP BY FinancialYearDateRanges.FromDate, FinancialYearDateRanges.ToDate
PIVOT [Application Title and Key].[Assessment Type];

 

[dhookom]

Take a look at your criteria. Your criteria values are text, not fields. Use []s to dilimit your field names.

TRANSFORM Count([Application Title and Key].[Assessment Type]) AS [CountOfAssessment Type]
SELECT FinancialYearDateRanges.FromDate, FinancialYearDateRanges.ToDate
FROM FinancialYearDateRanges, [Application Title and Key]
WHERE ((([Application Title and Key].[Application Eligibility Date]) Between [ToDate] And [FromDate]))
GROUP BY FinancialYearDateRanges.FromDate, FinancialYearDateRanges.ToDate
PIVOT [Application Title and Key].[Assessment Type];

Duane
Hook'D on Access
MS Access MVP

 

[TashiraRonin]

Thank you SOOOOOOOOOOOOOOOOOO much I even understand what you said and can now go an create the other 6 of these I need !

 

[TashiraRonin]

Is there a way to make this calculate percentages not counts based on total for each row

 

[dhookom]

Calculate the percentages in your form or report.

Duane
Hook'D on Access
MS Access MVP

 

[TashiraRonin]

Ahh Ok

I was looking for a query way to do this. I am assuming your post means there is no way to do it.

As report formats etc change but if I have the data in a query I can recreate it in multiple "styles" to suit the whim of the executive and committee

 

[dhookom]

It's possible but I would do it in the form or report. You could try create a query that counts all records between the Min FromDate and the Max ToDate. Then include it in the above query and set your Value to:

Count([Application Title and Key].[Assessment Type])/Min(CountOfAllField) AS [CountOfAssessment Type]

Duane
Hook'D on Access
MS Access MVP

 

[TashiraRonin]

Ok
ended up with
TRANSFORM Format(nz(Count([Application Title and Key].[Assessment Type])/[TotalCountOfAssessment Type],0),"#,##0.0%") AS Expr1
SELECT FinancialYearDateRanges.FromDate, FinancialYearDateRanges.ToDate, Count([Application Title and Key].[Assessment Type]) AS [TotalCountOfAssessment Type]
FROM FinancialYearDateRanges, [Application Title and Key]
WHERE ((([Application Title and Key].[Application Eligibility Date]) Between [ToDate] And [FromDate]))
GROUP BY FinancialYearDateRanges.FromDate, FinancialYearDateRanges.ToDate
PIVOT [Application Title and Key].[Assessment Type];


which does it!!!

Thank you for your help