CODEWITHSUNDEEP
sqlms-access
Jossy
Jossy

Reputation: 989

Why am I getting duplicate query records?

I have a query (qry_prob) with the following results:

+------+-------------+--------------+
| PK_G | ID1_Prob_Ov | ID1_Prob_Sur |
+------+-------------+--------------+
| 1044 | 47.17%      | 72.17%       |
| 1045 | 46.93%      | 53.79%       |
| 1046 | 49.57%      | 50.66%       |
| 1047 | 59.89%      | 66.54%       |
+------+-------------+--------------+

From this I want to create another query that takes the 'Prob' fields and creates a blended % for each record using a reference table (tbl_bldpct) as follows:

+----+------------+---------+
| ID | Bld_Input  | Bld_Pct |
+----+------------+---------+
|  1 | % Prob Ov  | 15.00%  |
|  2 | % Prob Sur | 85.00%  |
+----+------------+---------+

The result should look like this:

+------+--------------+
| PK_G | ID1_prob_bld |
+------+--------------+
| 1044 | 68.42%       |
| 1045 | 52.76%       |
| 1046 | 50.50%       |
| 1047 | 65.54%       |
+------+--------------+

I've used the following SQL: SELECT qry_prob.PK_G, [qry_prob].[ID1_Prob_Ov]*(SELECT Bld_Pct FROM tbl_bldpct WHERE ID = 1)+[qry_prob].[ID1_Prob_Sur]*(SELECT Bld_Pct FROM tbl_bldpct WHERE ID = 2) AS ID1_prob_bld FROM qry_prob, tbl_bldpct;

However, I'm getting duplicates rows as follows:

+------+--------------+
| PK_G | ID1_prob_bld |
+------+--------------+
| 1044 | 68.42%       |
| 1044 | 68.42%       |
| 1045 | 52.76%       |
| 1045 | 52.76%       |
| 1046 | 50.50%       |
| 1046 | 50.50%       |
| 1047 | 65.54%       |
| 1047 | 65.54%       |
+------+--------------+

If I add another row to my reference table I get another row of duplicates so I can see the problem is in the selection of the records from this table but I can't figure out how I fix it...

Upvotes: 2

Views: 119

Answers (4)

John
John

Reputation: 1004

If i am not mistaken

SELECT Table2.PK_G, [Bld_Input]*0.15+[ Bld_Pct]*0.85 AS ID1_prob_bld
FROM qry_prob;

Based on the Bld_Pct 

 SELECT qry_prob.PK_G, qry_prob.Bld_Input* (SELECT Bld_Pct FROM tbl_bldpct  WHERE ID =1) + qry_prob.Bld_Pct* (SELECT Bld_Pct FROM tbl_bldpct WHERE ID =2)
FROM qry_prob;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can join to the tbl_bldpct twice. Unfortunately, MS Access is not very flexible in its JOIN syntax, so you need to use cross joins with filtering in the WHERE clause:

select (qp.ID1_Prob_Ov * bp1.Bld_Pct + qp.ID1_Prob_Sur * bp2.Bld_Pct) as ID1_prob_bld
from qry_prob as qp,
     tbl_bldpct as bp1,
     tbl_bldpct as bp2
where bp1.id = 1 and bp2.id = 2;

You can also pre-aggregate tbl_bldpct:

select (qp.ID1_Prob_oc * bp.Bld_Pct_1 + qp.ID1_Prob * bp.Bld_Pct2) as ID1_prob_bld
from qry_prob as qp, 
     (select max(iif(id = 1, Bld_Pct, null)) as Bld_Pct_1,
             max(iif(id = 1, Bld_Pct, null)) as Bld_Pct_2                 
      from tbl_bldpct
     ) bp;

Upvotes: 1

MicSim
MicSim

Reputation: 26796

It's because you are doing a CROSS JOIN of the 2 tables qry_prob, tbl_bldpct. This happens automatically if you just specify the tables separated by comma in the FROM clause without mentioning a specific JOIN condition:

FROM qry_prob, tbl_bldpct

As the second table is not needed in your SELECTquery, you can just safely omit it and get rid of the CROSS JOIN (which would return the cartesian product of the 2 table's records).

SELECT qry_prob.PK_G, 
   [qry_prob].[ID1_Prob_Ov]*(SELECT Bld_Pct FROM tbl_bldpct WHERE ID = 1)
   +[qry_prob].[ID1_Prob_Sur]*(SELECT Bld_Pct FROM tbl_bldpct WHERE ID = 2) AS ID1_prob_bld
FROM qry_prob

Upvotes: 1

Gustav
Gustav

Reputation: 55816

Try with Distinct:

SELECT DISTINCT
    qry_prob.PK_G, 
    [qry_prob].[ID1_Prob_Ov]*(SELECT Bld_Pct FROM tbl_bldpct WHERE ID = 1)+[qry_prob].[ID1_Prob_Sur]*(SELECT Bld_Pct FROM tbl_bldpct WHERE ID = 2) AS ID1_prob_bld
FROM 
    qry_prob, 
    tbl_bldpct;

Or simply remove the table:

SELECT 
    qry_prob.PK_G, 
        [qry_prob].[ID1_Prob_Ov]*(SELECT Bld_Pct FROM tbl_bldpct WHERE ID = 1) +
        [qry_prob].[ID1_Prob_Sur]*(SELECT Bld_Pct FROM tbl_bldpct WHERE ID = 2) AS ID1_prob_bld
FROM 
    qry_prob;

Upvotes: 1

Related Questions