win, draw, loss? 1

[snakemaster]

Hi,

This is the situation:
I have a table 'results' with the following field names:
'ID', 'Date', 'F', 'A'
F and A stand for the number of goals for or against the home team. On every day there can be 1 or more matches played.
From this table, I created two queries:
'Games lost per day'
'Games won per day'
'Games drawn per day'
But how can I 'join' those tables in Access. What query do I use. To give an example... I have the following tables:
'Games lost per day'
august 1, 1
'Games drawn per day'
august 1, 1
'Games won per day'
august 1, 1
august 2, 1
What I want to get is a table that would look like this:
'Results per day (day, won, drawn, lost)'
august 1, 1, 1, 1
august 2, 1, NULL, NULL (or 0 would even be better)

How can I get this to work? The full outer join syntax that I'm used to working with, doesn't seem to work in Access...

Thanks in advance!

Bart

 

[Norris68]

SELECT [Date], SUM(IIF([F]>[A]),1,0)) As Won, SUM(IIF([F]=[A]),1,0)) As Drawn, SUM(IIF([F]<[A]),1,0)) As Lost FROM Results GROUP BY [Date]

 

[snakemaster]

Wow... It's embarrassing how EASY that turned out to be...
Thanks! All I had to do was to add an extra '(' after the 'IIF' statements.

 

[Norris68]

Oops! Sorry it was actually an extra ')' in each of the IIFs.
SELECT [Date], SUM(IIF([F]>[A],1,0)) As Won, SUM(IIF([F]=[A],1,0)) As Drawn, SUM(IIF([F]<[A],1,0)) As Lost FROM Results GROUP BY [Date]