Angel Angel - 1 year ago 57
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].


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:

|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 |

Queries as follows:
Query A

SELECT DISTINCTROW [Table A].[Name], [Table A].Month, Sum([Table A].SaleA)
AS SumOfSaleA, [Table A].[Assoc No], [Table C].Team
FROM [Table C] INNER JOIN [Table A] ON [Table C].[Assoc No] = [Table A].
[Assoc No]
GROUP BY [Table A].[Name], [Table A].Month, [Table A].[Assoc No], [Table

Query B

SELECT [Table B].[Name], [Table B].Month, Sum([Table B].[SaleB]) AS
[SumOfSaleB], [Table B].[Assoc No], [Table C].Team
FROM [Table C] INNER JOIN [Table B] ON [Table C].[Assoc No] = [Table B].
[Assoc No]
GROUP BY [Table B].[Name], [Table B].Month, [Table B].[Assoc No], [Table

Query C

SELECT [Table C].Team, [Table C].[Name], [Table A].Month, [Table C].[Assoc
FROM [Table C] INNER JOIN [Table A] ON [Table C].[Assoc No] = [Table A].
[Assoc No]
GROUP BY [Table C].Team, [Table C].[Name], [Table A].Month, [Table C].[Assoc

Answer Source
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

(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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download