Record Count Increases with Join

[JG Scott]

I have a table A with 41,352 unique records. I need to join A to 2
queries, b and c, retaining all records in A and indicating in two
expression columns when A matches b and c, respectively. I created
expressions that say Match b?: Iif(IsNull(.[customer ID]),"","b")
and one like that for c as well. For some reason, though, the join
results in many more records (75,467) than are in A to begin with.
Since ultimately I need to count how many records match b and c, this
is problematic.

I already have set unique records to Yes. This doesn't solve the
problem.

Can someone advise how to resolve this?

Thanks.

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

 

[JG Scott]

Show us the SQL.  Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table A with 41,352 unique records.  I need to join A to 2
queries, b and c, retaining all records in A and indicating in two
expression columns when A matches b and c, respectively.  I created
expressions that say Match b?: Iif(IsNull(.[customer ID]),"","b")
and one like that for c as well.  For some reason, though, the join
results in many more records (75,467) than are in A to begin with.
Since ultimately I need to count how many records match b and c, this
is problematic.

I already have set unique records to Yes.  This doesn't solve the
problem.

Can someone advise how to resolve this?

Thanks.- Hide quoted text -

- Show quoted text -

Here is the SQL:

SELECT Proforma.DEALER_ID, Proforma.PRODUCT_NAME, Proforma.FROM_DATE,
Proforma.TO_DATE, Proforma.EXTENDED_PRICE, Proforma.Contract_Credits,
IIf(IsNull([3001 Scenario 1]!DEALER_ID),"","1") AS Scenario1,
IIf(IsNull([3002 Scenario 2]!DEALER_ID),"","2") AS [Scenario 2]
FROM (Proforma LEFT JOIN [3001 Scenario 1] ON
(Proforma.Contract_Credits = [3001 Scenario 1].Contract_Credits) AND
(Proforma.EXTENDED_PRICE = [3001 Scenario 1].EXTENDED_PRICE) AND
(Proforma.DEALER_ID = [3001 Scenario 1].DEALER_ID)) LEFT JOIN [3002
Scenario 2] ON (Proforma.Contract_Credits = [3002 Scenario
2].Contract_Credits) AND (Proforma.EXTENDED_PRICE = [3002 Scenario
2].EXTENDED_PRICE) AND (Proforma.DEALER_ID = [3002 Scenario
2].DEALER_ID)
GROUP BY Proforma.DEALER_ID, Proforma.PRODUCT_NAME,
Proforma.FROM_DATE, Proforma.TO_DATE, Proforma.EXTENDED_PRICE,
Proforma.Contract_Credits, IIf(IsNull([3001 Scenario 1]!
DEALER_ID),"","1"), IIf(IsNull([3002 Scenario 2]!DEALER_ID),"","2");


I do not have any primary keys or relationships defined.


Thanks.

 

[Jerry Whittle]

Without a primary key or at least a unique index on the three join fields,
dupes can get in. Run the following and see if there are any records returned:

SELECT Proforma.DEALER_ID,
Proforma.EXTENDED_PRICE,
Proforma.Contract_Credits,
Count(Proforma.DEALER_ID) AS TheCount
FROM Proforma
GROUP BY Proforma.DEALER_ID,
Proforma.EXTENDED_PRICE,
Proforma.Contract_Credits
HAVING Count(Proforma.DEALER_ID)>1;

Also you are doing left joins on two different tables. If Proforma has a
record then either 3001 Scenario 1 or 3001 Scenario 2 can return a record.
Therefore for every record in Proforma, there could be more than one record
returned.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table A with 41,352 unique records. I need to join A to 2
queries, b and c, retaining all records in A and indicating in two
expression columns when A matches b and c, respectively. I created
expressions that say Match b?: Iif(IsNull(.[customer ID]),"","b")
and one like that for c as well. For some reason, though, the join
results in many more records (75,467) than are in A to begin with.
Since ultimately I need to count how many records match b and c, this
is problematic.

I already have set unique records to Yes. This doesn't solve the
problem.

Can someone advise how to resolve this?

Thanks.- Hide quoted text -

- Show quoted text -

Here is the SQL:

SELECT Proforma.DEALER_ID, Proforma.PRODUCT_NAME, Proforma.FROM_DATE,
Proforma.TO_DATE, Proforma.EXTENDED_PRICE, Proforma.Contract_Credits,
IIf(IsNull([3001 Scenario 1]!DEALER_ID),"","1") AS Scenario1,
IIf(IsNull([3002 Scenario 2]!DEALER_ID),"","2") AS [Scenario 2]
FROM (Proforma LEFT JOIN [3001 Scenario 1] ON
(Proforma.Contract_Credits = [3001 Scenario 1].Contract_Credits) AND
(Proforma.EXTENDED_PRICE = [3001 Scenario 1].EXTENDED_PRICE) AND
(Proforma.DEALER_ID = [3001 Scenario 1].DEALER_ID)) LEFT JOIN [3002
Scenario 2] ON (Proforma.Contract_Credits = [3002 Scenario
2].Contract_Credits) AND (Proforma.EXTENDED_PRICE = [3002 Scenario
2].EXTENDED_PRICE) AND (Proforma.DEALER_ID = [3002 Scenario
2].DEALER_ID)
GROUP BY Proforma.DEALER_ID, Proforma.PRODUCT_NAME,
Proforma.FROM_DATE, Proforma.TO_DATE, Proforma.EXTENDED_PRICE,
Proforma.Contract_Credits, IIf(IsNull([3001 Scenario 1]!
DEALER_ID),"","1"), IIf(IsNull([3002 Scenario 2]!DEALER_ID),"","2");


I do not have any primary keys or relationships defined.


Thanks.