CODEWITHSUNDEEP
sqlms-accessms-access-2013
BlueRhapsody
BlueRhapsody

Reputation: 93

MS Access SQL: Grouping By Month

I am trying to create an SQL query which returns the frequency of each month appearing in my table (in a percentage form).

The SQL i have written to start this is the following:

SELECT Format(Sum(IIf(Sale_PickupDateTime="*/01/*",1,0))/Count(Sale_PickupDateTime), "Percent") AS January
FROM [Sales Table];

I plan to continue onwards for each month.

My issue is that when I try to run this code, I get the following error:

"Data Type Mismatch in Criteria Expression."

I am fairly certain this happens because sale_pickupdatetime is a general date format because I have used this code elsewhere (Sale_reason is a text field):

SELECT Format(Sum(IIf(Sale_Reason="Business",1,0))/Count(Sale_Reason), "Percent") AS Business
FROM [Sales Table];

and it has worked perfectly.

Upvotes: 0

Views: 41

Answers (2)

Gustav
Gustav

Reputation: 55806

You would need something like:

Select 
    Format(Sale_PickupDateTime, "yyyymm") As Period,
    Format(Count(*)/DCount("*", "[Sales Table]"), "Percent") As MonthShare
From
    [Sales Table];
Group By
    Format(Sale_PickupDateTime, "yyyymm")

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

Afaik Access supports a MONTH function:

SELECT
   Format(Sum(IIf(MONTH(Sale_PickupDateTime)=1,1,0))/Count(Sale_PickupDateTime)
         , "Percent") AS January
FROM [Sales Table];

Upvotes: 1

Related Questions