Help with data analysis options Dcount - Crosstab query (newbie)

[baronqueefington]

Good evening,

I have a table [Control Table] with the fields [Date signed] and [outcome] date signed is formatted as dd/mm/yyyy and the outcome field is a drop down with the options granted, not granted ect

I am looking for a way to present the data using specific date ranges.

I have found 2 possible avenues;

Dcount in a select query:

w/c 01/04/2014 GRANTED: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Outcome]='Granted'")

w/c 01/04/2014 Not GRANTED: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Outcome]='Not Granted' And [Reason not granted]='Assessed'")

w/c 01/04/2014 Discharged: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Reason not granted]='Discharged'")

ECT

But I would need to create the multiple queries 52 times each for the different count value per week




my 2nd option

I have looked at crosstab query, but I cant find a way for it to list the specific dates I need it to query e.g from

01/04/2014 - 06/04/2014
07/04/2014 - 13/04/2014
14/04/2014 - 20/04/2014

ECT

Does anyone have any tips on Data analysis? I have been able to perform the task previously in excel using If statements but we are now moving to access and I somewhat stumped.

Thanks.

[June7]

Your date range is weekly? You can create a field in query that calculates the Year and Week Number and use that field in a grouping query/report or CROSSTAB.

YrWk: Year([datefield] & Format(DatePart("ww",[datefield]), "00")

Can be a complication where the week crosses other periods - monthly, yearly. Do you want December 28 and January 1 data to roll into same week?