CODEWITHSUNDEEP
sqlms-access
Fitzy
Fitzy

Reputation: 113

MS Access output duplicate rows on joined tables

I have been pulling my hair out on this.

I have an access database. It has 3 tables imported. One table is a complete list of Business Service types, say A, B, C, D, in the first column.
The 2 other tables represent the months of the Year, say "Jan Bus All" and "Feb Bus All". These two have in Column A the Business Service Type and Column B the count for that month that the service type has been used.

I join the 2 month tables to the service type table, placing the join on column A, the Business Service Type. Each month may have different service types used. Example, Jan may have counts for A and C. Feb may have A and D.

The problem is, I am hoping to get disticnt rows in the output correspinding the the Service type list, however I am getting multiple rows for the Service type, with alternating counts for each month.

Am I missing something?

Here is the SQL code used in Access.

 SELECT DISTINCT [Business Service Type List].[Bus Service] AS Business_Service,   
[Jan Bus All].Jan AS Jan, [Feb Bus All].Feb AS Feb  
    FROM [Feb Bus All]   
RIGHT JOIN ([Jan Bus All] RIGHT JOIN [Business Service Type List]   
ON [Jan Bus All].Business_Service = [Business Service Type List].[Bus Service])  
ON [Feb Bus All].Business_Service = [Business Service Type List].[Bus Service]   
GROUP BY [Business Service Type List].[Bus Service], [Jan Bus All].Jan, [Feb Bus All].Feb;

Upvotes: 1

Views: 68

Answers (1)

Don Jewett
Don Jewett

Reputation: 1957

Your version works fine for me and so does this one below. 

SELECT DISTINCT [Business Service Type List].[Bus Service] AS Business_Service, 
   [Jan Bus All].Jan AS Jan, [Feb Bus All].Feb AS Feb
FROM [Feb Bus All] RIGHT JOIN ([Jan Bus All] 
RIGHT JOIN [Business Service Type List] 
   ON [Jan Bus All].Business_Service = [Business Service Type List].[Bus Service]) 
   ON [Feb Bus All].Business_Service = [Business Service Type List].[Bus Service]
WHERE [Jan Bus All].Jan Is Not Null OR [Feb Bus All].Feb Is Not Null;

I assume this means you have duplicate values in one of your tables. In other words in your Feb table, a Service exists more than once or the same for your Jan table.

But this is a really unconventional design. The data you are describing is a classic many-to-many which can be much better designed like so:

Table 1: Services

ServiceId
+-----------+
A
B
C
D
E

Table 2: Months

| MonthId  | MonthName
+----------+-----------+
| 1        | Jan
| 2        | Feb

Table 3: ServiceMonths

| ServiceId    | MonthId    | TotalNumber
+--------------+------------+-------------+
| A            | 2          | 8
| A            | 1          | 5
| B            | 1          | 15
| D            | 2          | 32
| E            | 2          | 36
| E            | 1          | 25

With relationships and keys like this:

enter image description here

This can be queried as a CrossTab:

TRANSFORM Sum(TotalNumber) AS SumOfTotalNumber
SELECT ServiceId
FROM ServiceMonths
GROUP BY ServiceId
PIVOT MonthId;

With the following results:

| ServiceId    | 1     | 2
+--------------+-------+-----+
| A            | 5     | 8
| B            | 15    | 
| D            |       | 32
| E            | 25    | 36

Upvotes: 1

Related Questions