If a certain date = Monday?, how to do this 3

[NFLDUser]

I have a table with each row having a date field. I am trying to return the total number of rows from each day of the week.

Mon Tues Wed Thurs Fri Sat Sun
44 45 3 45 23 2 34

I'd like to have something like the above to work with.

Any ideas????

 

[NFLDUser]

OK...I can feel how close we are.

It's now asking for a Parameter Value for M.visit_date.

Here is my query.

<CODE>
Select WeekDay(M.[visit_date]) As [Week Day],
Count(*) As [CountOfWeekDay],

(Count(*) /

(SELECT Count(*)
FROM
(SELECT DISTINCT X.[visit_date]
FROM Breakdown AS X
WHERE Weekday(X.[visit_date])=WeekDay(M.[visit_date])
) As J)) As [AverageValue]

From Breakdown As M

Group By WeekDay(M.[visit_date])
</CODE>

 

[Golom]

That indicates that the field [Visit_Date] does not exist in the BreakDown table. Check the field names in the table.

Is it for example [VisitDate]? [Visit Date]? ...

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[NFLDUser]

I do have a field called 'visit_date'.

Could it have something to do with using M
WHERE Weekday(X.[visit_date])=WeekDay(M.[visit_date])

before it is declared.
From Breakdown As M

?

 

[lespaul]

no, that's the correct structure for a correlated sub-query.

 

[NFLDUser]

Hmm,
well I simplified the DB and now I have the table named 'Breakdown' with 1 field, 'visit_date' set as Short Date with just a few random dates in there.

I still get it looking for M.visit_date as a parameter.

 

[Golom]

OK. The problem was that the reference is actually embedded two-levels deep so the SQL parser can't figure it out. Some modifications are in order

Select WeekDay(M.[Visit_Date]) As [Week Day],
       Count(*) As [CountOfWeekDay],


(Count(*) /

    (Select Number

     From

        (Select WD, Count(*)  As [Number]

        From

           (SELECT DISTINCT cdate(int(X.[Visit_Date])) As [Sale Date], 
                            WeekDay(X.[Visit_Date]) As WD  
            FROM BreakDown AS X) As Q

        Group By WD ) As J

      Where J.WD=WeekDay(M.[Visit_Date])                 )  As [AverageValue]

From BreakDown As M

Group By WeekDay(M.[Visit_Date])

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[NFLDUser]

THANK YOU!

I am SQL'd out now.

Thank you again!!!!!!!

 

[NFLDUser]

With this query, I now have the ave value per transaction for every day of the week.

Now I'm looking for totals.
So I'm looking for the average total for Monday-Sunday.

So first I'd need to determine what the average total is for each Monday for example. Then divide that by the number of Mondays that there were transactions.

Ideas on this??