SQL Count Distinct Values For Date

[mirtomi]

Hello everyone,

I have the table 'Transactions' containing all the transactions in an year. The columns of the table are:
Transaction_date
Type
Value
Payer_Id
Status

What I would like to do is a query to group up all the transactions by month, then count the transactions corresponding to each month, Sum up their values and count the tansaction dates (as number of days per monh) for each month.
The problem is that there are numerous transactions with the same date and I need to "Distinct Count" them.
The code I came up with is below, but it gives me an error when saving the query:

SELECT Format([Transaction_date],"MM-YYYY") AS Month
, Count(Transactions.Type) AS [Number of transactions]
, Sum(Transactions.Value) AS [Value]
, TCounts.TCount
FROM Transacions INNER JOIN 
       (SELECT Count(Transaction_Date) as TCount, MonthYear
       FROM (
                SELECT DISTINCT (Transaction_Date)
                , Format([Transaction_Date],"mm-yyyy") as MonthYear
                FROM Transactions
               WHERE (Transactions.Status) = "Complete" and (Transactions.Payer_ID) <> ""
               GROUP BY MonthYear
                )
       ) as TCounts
ON  Format([Transaction_date],"MM-YYYY") INNER JOIN TCounts.MonthYear
WHERE (Transactions.Status) = "Complete" and (Transactions.Payer_ID) <> ""
GROUP BY Format([Transaction_date],"MM-YYYY"), TCounts.TCount;

The problem is that I get the following error message when I try to save the query:

Syntax error (missing operator) in query expression:
'Format([Transactions.Transaction_date],"mm-yyyy") INNER JOIN TCounts.MonthYear'

Please help!
Thank you very much!

 

[ted.martin]

Try this as your bracketing is incorrect

Format([Transactions[COLOR="Red"][B]].[[/B][/COLOR]Transaction_date],"mm-yyyy") INNER JOIN TCounts.MonthYear'

 

[mirtomi]

Ok, I tried it, still gives the same error message:

Syntax error (missing operator) in query expression:
'Format([Transactions].[Transaction_date],"mm-yyyy") INNER JOIN TCounts.MonthYear'

 

[Clut]

This may not be the problem, as I'm not brilliant with SQL, but I did notice that in:

FROM Transacions INNER JOIN

you seem to have spelt the tablename wrong. It looks like it is missing a 't'.

 

[mirtomi]

No, I don't think that's the problem. I must have misspelled the table name when I translated it from my language into English.

However, somebody came with the suggestion that there are 2 INNER JOIN clauses, and the clause should be:

 [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Format([Transactions].[Transaction_date],"MM-YYYY") =TCounts.MonthYear

in the ON Clause.

Once I made the adjustment, it said:

‘Join expression not supported’ and highlighted the:

(Format([Transaction_date],"MM-YYYY")) = TCounts.MonthYear

I’m posting the code again, maybe somebody can figure it out:

 [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]SELECT Format([Transaction_date],"MM-YYYY") AS Month[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier], Count(Transactions.Type) AS [Number of transactions][/FONT][/COLOR]
[COLOR=#465584][FONT=Courier], Sum(Transactions.Value) AS [Value][/FONT][/COLOR]
[COLOR=#465584][FONT=Courier], TCounts.TCount[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]FROM Transactions INNER JOIN [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]       (SELECT Count(Transaction_Date) as TCount, MonthYear[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]       FROM ([/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                SELECT DISTINCT (Transaction_Date)[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                , Format([Transaction_Date],"mm-yyyy") as MonthYear[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                FROM Transactions[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]               WHERE (Transactions.Status) = "Complete" and (Transactions.Payer_ID) <> ""[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]               GROUP BY MonthYear[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                )[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]       ) as TCounts[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]ON  Format([Transaction_date],"MM-YYYY") = TCounts.MonthYear[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]WHERE (Transactions.Status) = "Complete" and (Transactions.Payer_ID) <> ""[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]GROUP BY Format([Transaction_date],"MM-YYYY"), TCounts.TCount;[/FONT][/COLOR]

 

[mirtomi]

No, I don't think that's the problem. I must have misspelled the table name when I translated it from my language into English.

However, somebody came with the suggestion that there are 2 INNER JOIN clauses, and the clause should be:

Format([Transactions].[Transaction_date],"MM-YYYY") =TCounts.MonthYear

in the ON Clause.

Once I made the adjustment, it said:

‘Join expression not supported’ and highlighted the:

(Format([Transaction_date],"MM-YYYY")) = TCounts.MonthYear

I’m posting the code again, maybe somebody can figure it out:

[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]SELECT Format([Transaction_date],"MM-YYYY") AS Month[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier], Count(Transactions.Type) AS [Number of transactions][/FONT][/COLOR]
[COLOR=#465584][FONT=Courier], Sum(Transactions.Value) AS [Value][/FONT][/COLOR]
[COLOR=#465584][FONT=Courier], TCounts.TCount[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]FROM Transactions INNER JOIN [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]       (SELECT Count(Transaction_Date) as TCount, MonthYear[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]       FROM ([/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                SELECT DISTINCT (Transaction_Date)[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                , Format([Transaction_Date],"mm-yyyy") as MonthYear[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                FROM Transactions[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]               WHERE (Transactions.Status) = "Complete" and (Transactions.Payer_ID) <> ""[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]               GROUP BY MonthYear[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                )[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]       ) as TCounts[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]ON  Format([Transaction_date],"MM-YYYY") = TCounts.MonthYear[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]WHERE (Transactions.Status) = "Complete" and (Transactions.Payer_ID) <> ""[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]GROUP BY Format([Transaction_date],"MM-YYYY"), TCounts.TCount;[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]