Conditional Query

[tig]

I've got two tables. I need to see all the records in table1 and some
fields from table2 based on conditions. My primary join field (field1)
could give me duplicate records in table2. So what I need is, if
table2 has two records matching table1 on field1, only give me the
table2 record where field2 = X. Don't show me the other one. If only
one table2 record matches on field1, then give me that one.

I'm a little green on SQL. So any help would be greatly appreciated.
Here's my code so far, I'm just not sure how to continue.

SELECT Table1.Field1, Table1.Dt, Table1.Field3, Table2.Field1,
Table2.field2
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table1.Field1
WHERE (((Table1.Field2)="Yes") AND ((Table1.Dt)=Date()));

Thanks in advance.

 

[tig]

I would use a bunch of queries (other that know subqueries would use them).
Totals query on Table2 to find all Field1 that have duplicates. Try these
queries.

tig_1 ---
SELECT Table2.Field1, Count(Table2.Field1) AS CountOfField1
FROM Table2
GROUP BY Table2.Field1
HAVING (((Count(Table2.Field1))>1));

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field1,
Table2.Field2
FROM Table1 LEFT JOIN (Table2 LEFT JOIN tig_1 ON Table2.Field1 =
tig_1.Field1) ON Table1.Field1 = Table2.Field1
WHERE (((Table2.Field1)=[tig_1].[Field1]) AND ((Table2.Field2)="X") AND
((tig_1.CountOfField1)=2)) OR (((Table2.Field1)=[tig_1].[Field1]))
ORDER BY Table2.Field1;

I've got two tables. I need to see all the records in table1 and some
fields from table2 based on conditions. My primary join field (field1)
could give me duplicate records in table2. So what I need is, if
table2 has two records matching table1 on field1, only give me the
table2 record where field2 = X. Don't show me the other one. If only
one table2 record matches on field1, then give me that one.

I'm a little green on SQL. So any help would be greatly appreciated.
Here's my code so far, I'm just not sure how to continue.

SELECT Table1.Field1, Table1.Dt, Table1.Field3, Table2.Field1,
Table2.field2
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table1.Field1
WHERE (((Table1.Field2)="Yes") AND ((Table1.Dt)=Date()));

Thanks in advance.

Thanks for the post Karl. That was really close to working for me.
Here's what I got to work (my code has my actual object names):

SELECT Brokers.ABANbr AS rec_ABA, Format([NetAmt],"Standard") AS Cash,
Brokers.BrokerAcct AS rec_acct, Brokers.Addl_Info AS rec_info,
RW.CUSIP, RW.OrigFace, RW.Units, calc_par([RW]![OrigFace],[RW]![Units])
AS par, RW.Side, RW.PstDt, RW.BrkrFINS, RW.MBS, Brokers.BrkrFINS,
Brokers.MBS

FROM RW LEFT JOIN (Brokers LEFT JOIN Broker_Dups ON Brokers.BrkrFINS =
Broker_Dups.BrkrFINS) ON RW.BrkrFINS = Brokers.BrkrFINS

WHERE (((RW.Side)="S") AND ((RW.PstDt)=Date()) AND ((RW.MBS)=True) AND
((Brokers.BrkrFINS)=[Broker_Dups].[BrkrFINs]) AND ((Brokers.MBS)=True)
AND ((Broker_Dups.CountOfField1)=2))

OR (((RW.Side)="S") AND ((RW.PstDt)=Date()) AND ((RW.MBS)=True) AND
((Brokers.BrkrFINS)=[RW].[BrkrFINs]) AND ((Brokers.MBS)<>True) AND
((Broker_Dups.BrkrFINS) Is Null));


Thank you again for taking the time to post.