Query multiplies number of fields 1

[DLSE]

I have three tables: A, B, and C. Tables A and C are each outer linked to table B. I also have two queries. Query 1 is based on table A. Query 2 is based on table C. I now wish to make an additional query (query 3) that combines queries 1 and 2. However, when I attempt this, the number of fields in query 1 is multiplied by the number of fields in query 2. For example, if Q1 has 7 fields & Q2 has 8 fields, Q3 will result in 56 fields. Any help in figuring out this problem will be appreciated.
Thanks,
Matt

 

[mpastore]

Matt,

Can you post the SQL in this forum? My gut feeling is that you are not joining q1 and q2 on a common field and are getitng a cross-product.

Mike Pastore

Hats off to (Roy) Harper

 

[DLSE]

Here is the current SQL for Query 3:

SELECT [1].[Month Number], [1].[Month Name], [2].[CountOfProblem Code], [2].[Problem Description]
FROM 2 RIGHT JOIN 1 ON [2].Month = [1].[Month Number];

Thanks for the help,
Matt

 

[mpastore]

Check [2].Month and [1].[Month Number] from the q1 and q2, is the month number the same in each row?



Mike Pastore

Hats off to (Roy) Harper

 

[DLSE]

No, the month number is not the same in each row.
I am trying to list "Codes" according to month. Currently, I only have data for April and only 2 of 3 "codes". Therefore, in Q1, 53 records exist. 42 of these are data for April, and 11 are for the remaining months with no data. However, Q1 does not include the third "code", which is not used as of April. I then made Q2, which returns all three codes, whether or not they are used. In Q2 there are 43 records. 42 of these are data for april, and 1 is for the remaining code, which has no corresponding month. So basically, I'm trying to include all twelve months and all three codes in one query, whether or not they are actually included in the data yet. Sorry if this is a bit confusing, but I'm fairly new to Access.
Thanks,
Matt

 

[mpastore]

Ok, try to follow my logic here and make your own adjustments to fit your structures:


Assume a table called tbCode with following data (these are your codes):

code
A
B
C

Assume a table called tbMonth with following data (these are your months):

month_no month_name
1 Jan
2 Feb
3 Mar
4 Apr
5 May
6 Jun
7 Jul
8 Aug
9 Sep
10 Oct
11 Nov
12 Dec


Assume a table called tbCodeMonth with following data (these are your codes used for Apr):

cd_date cd_code
04/02/2003 A
04/05/2003 A
04/04/2003 B

The output I come up with is this:

month_name code CountOfcd_code
Jan A 0
Jan B 0
Jan C 0
Feb A 0
Feb B 0
Feb C 0
Mar A 0
Mar B 0
Mar C 0
Apr A 2
Apr B 1
Apr C 0
May A 0
May B 0
May C 0
Jun A 0
Jun B 0
Jun C 0
Jul A 0
Jul B 0
Jul C 0
Aug A 0
Aug B 0
Aug C 0
Sep A 0
Sep B 0
Sep C 0
Oct A 0
Oct B 0
Oct C 0
Nov A 0
Nov B 0
Nov C 0
Dec A 0
Dec B 0
Dec C 0

If this look OK, use these queries:

1) Create a query named qCodesMonths, paste following in:

SELECT tbCode.code, tbMonth.month_no, tbMonth.month_name
FROM tbCode, tbMonth;

2) Create a query named qCodeMonth_used, paste following in:

SELECT Month([cd_date]) AS [month], tbCodeMonth.cd_code
FROM tbCodeMonth;

3) Create a query named qCodeMonth_total, paste following in:

SELECT qCodesMonths.month_name, qCodesMonths.code, Count(qCodeMonth_used.cd_code) AS CountOfcd_code
FROM qCodesMonths LEFT JOIN qCodeMonth_used ON (qCodesMonths.code = qCodeMonth_used.cd_code) AND (qCodesMonths.month_no = qCodeMonth_used.month)
GROUP BY qCodesMonths.month_name, qCodesMonths.code, qCodesMonths.month_no
ORDER BY qCodesMonths.month_no;


The last query lists the results.






Mike Pastore

Hats off to (Roy) Harper

 

[DLSE]

Thank you VERY much. Your post solved all of my problems that I had spent days on in 10 minutes. It works great and was very helpful. Thanks for your time.
Matt