Angel Angel - 1 month ago 10
SQL Question

Do I need a calendar to join tables in access and report by month

I have to 3 tables that I need to join in Access. So I have created queries to obtain the "Team" field with an inner join [Table A] to [Table C] on "Assoc. NO" and then another one on [Table B] to [Table C] also on "Assoc. NO].

MY ISSUE

I use a query based on [Table C] and [Table A] to obtain the months, but then I am missing months if they did not exist in [Table A] and only [Table B]. Do I need to create a table that only has months and years and create a join to that? I apologize for the long post but want to provide as much info as possible. Thanks in advance for your help.

Table A
|Name | Assoc. NO |month|Year|Product|SaleA|
|John Smith | 1 |Jan |2016|Apple |$10 |
|John Smith | 1 |Jan |2016|Pear |$5 |
|John Smith | 1 |Feb |2016|Apple |$10 |
|George Martin| 2 |Feb |2016|Apple |$10 |
|George Martin| 2 |Feb |2016|Apple |$10 |
|George Martin| 2 |Feb |2016|Pear |$5 |


Table B
|Name | Assoc. NO |month|Year|Service |SaleB|
|John Smith | 1 |Jan |2016|oil change|$25 |
|George Martin| 2 |Jan |2016|oil change|$25 |
|Mark James | 3 |Feb |2016|oil change|$25 |
|Mark James | 3 |Mar |2016|oil change|$25 |
|George Martin| 2 |Mar |2016|oil change|$25 |

Table C
|Team |Name | Assoc. NO |
|Team A |John Smith | 1 |
|Team B |George Martin| 2 |
|Team B |Mark James | 3 |


What I would Like to see is the following:

Query
|Team |Name | Month |Sale |SaleB|SUM(SaleA,SaleB)|
|-------|-------------|--------|-----|-----|----------------|
|Team A |John Smith | Jan | $15 | $25 | $40 |
|Team A |John Smith | Feb | $10 | $0 | $10 |
|Team B |George Martin| Jan | $0 | $25 | $25 |
|Team B |George Martin| Feb | $25 | $0 | $25 |
|Team B |George Martin| Mar | $0 | $25 | $25 |
|Team C |Mark James | Feb | $0 | $25 | $25 |
|Team C |Mark James | Feb | $0 | $25 | $25 |

Answer
SELECT [Table C].Team, [Table C].Name, [Table C].[Assoc No], Month, Sum(T.SaleA) AS TotA, Sum(T.SaleB) AS TotB, [TotA]+[TotB] AS Total
FROM

(select [Assoc no], Month, SaleA, 0 as SaleB from [Table A] 
UNION ALL select [Assoc No], Month, 0, Saleb from [Table B])  AS T

INNER JOIN [Table C] ON T.[Assoc No]= [Table C].[Assoc No]
GROUP BY [Table C].Team, [Table C].Name, Table C].[Assoc No], T.Month;