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.
JG Scott said:
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.
JG Scott said:
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.
