Need help converting dates to DOW & counting records

[MrMitchell]

Hey all,

I've been lurking around the forum for some time and I'm usually able to find solutions to my roadblocks using the search function, but couldn't find anything, so hopefully you all can help! What I'm trying to do seems fairly straight forward, but can't get the query to work properly for some reason. What I have, essentially, is a table that looks like this:

Region City Date Account_num
NW Portland 6/11/2012 1234
MW Kansas City 6/10/2012 4567

What I'd like to have the query do, is show how many accounts were created by day of week, essentially the field names would look like:

region city Saturday Sunday Monday Tuesday Wednesday Thursday Friday

I've been trying to get there using a variety of different formulas mostly looking like :"Count(iif(DatePart("w"......
but can't seem to get it right. Any help would be greatly appreciated! Thanks in advance for everyone's time!

 

[AccessMSSQL]

You will need a combination of queries to do this. First create a query with your datepart function in there to return the days of the week instead of the day number.

From that query you will need to create a cross tab query with Region and City as rowheadings, Your DayName field in the first query as the columnheading and count(*) as Value field.

I'll post an example shortly.

 

[boblarson]

You will need a combination of queries to do this.

Nope, one query does it.

And the SQL:

SELECT tblAccounts.Region, tblAccounts.City, Sum(IIf(Format([Date],"ddd")="Sun",1,0)) AS Sunday, Sum(IIf(Format([Date],"ddd")="Mon",1,0)) AS Monday, Sum(IIf(Format([Date],"ddd")="Tue",1,0)) AS Tuesday, Sum(IIf(Format([Date],"ddd")="Wed",1,0)) AS Wednesday, Sum(IIf(Format([Date],"ddd")="Thu",1,0)) AS Thursday, Sum(IIf(Format([Date],"ddd")="Fri",1,0)) AS Friday
FROM tblAccounts
GROUP BY tblAccounts.Region, tblAccounts.City;

 

[AccessMSSQL]

In this example it's a production line and downtime instead of region and city:
First query: Query1

SELECT tblLineDownTime.Equipment, tblLineDownTime.Line, Format(Weekday([RunDate]),"ddd") AS RunDay
FROM tblLineDownTime;

2nd query:this is your crosstab query

TRANSFORM Count(Query1.Equipment) AS CountOfEquipment
SELECT Query1.Equipment, Query1.Line
FROM Query1
GROUP BY Query1.Equipment, Query1.Line
PIVOT Query1.RunDay;

The only challenge you will have here is getting the weekday names to show up in order - they will show up in alpha order but not by day of week.

 

[AccessMSSQL]

Bob, your quick! Thanks for the post. I'll have to try it that way next time.

 

[MrMitchell]

Lightning quick thank you both so much!