Query to get no duplicated records

[Jose A. Fdez.]

I have two tables tblProducts and tblLoans with the following fields:
tblProducts:
IdProduct
DescriptionProduct
tblLoans:
IdLoan
IdProduct
DateOut
DateIn

How can I get a list of Products without duplications as follows:
IdProduct DescriptionProduct YesIfOut

Where the field "YesIfOut" must be:
"Yes" if, for this IdProduct, there is a record in tblLoans with DateIn
null
"No" if, for this IdProduct, there are no records in tblLoan OR all
records are with DateIn not null.

Is it possible to do that with a query?

Thanks.

 

[Douglas J. Steele]

Try:

SELECT DISTINCT tbl.Products.IdProduct,
tblProducts.DescriptionProduct,
IIf(IsNull(tblLoans.DateIn), "No", "Yes") AS YesIfOut
FROM tblProducts LEFT JOIN tblLoans
ON tblProducts.IdProduct = tblLoans.IdProduct

 

[Jose A. Fdez.]

Try:

SELECT DISTINCT tbl.Products.IdProduct,
tblProducts.DescriptionProduct,
IIf(IsNull(tblLoans.DateIn), "No", "Yes") AS YesIfOut
FROM tblProducts LEFT JOIN tblLoans
ON tblProducts.IdProduct = tblLoans.IdProduct

Hi Douglas,
thank you for your answer.
It's a very good approach but the results are not what I expected. I'll
try to explain it with an example:

IdProduct DescriptionProduct
1 uno
2 dos
3 tres
4 cuatro
5 cinco
6 seis

IdLoan IdProduct DateOut DateIn
100 1 01/01/2006 10/01/2006
101 1 02/02/2006
102 2 03/03/2006 10/03/2006
103 2 04/04/2006 10/04/2006
104 3 04/04/2006 05/04/2006
105 4 05/05/2006

With your query I get this:
IdProduct DescriptionProduct YesIfOut
1 uno No
1 uno Yes
2 dos Yes
3 tres Yes
4 cuatro No
5 cinco No
6 seis No

And my target is to get this:
IdProduct DescriptionProduct YesIfOut
1 uno Yes
2 dos No
3 tres No
4 cuatro Yes
5 cinco No
6 seis No

Regards,
Jose A.

 

[Douglas J. Steele]

Create an interim query qryLoans:

SELECT tblLoans.IdProduct,
Max(Nz([DateIn],#12/30/1899#)) AS ModDate
FROM tblLoans
GROUP BY tblLoans.IdProduct;

Create a second query based on that:

SELECT tblProducts.IdProduct,
tblProducts.DescriptionProduct,
IIf([ModDate]=#12/30/1899#,"Yes","No") AS YesIfOut
FROM tblProducts LEFT JOIN qryLoans
ON tblProducts.IdProduct = qryLoans.IdProduct;

If you're using Access 2000 or newer, you should actually be able to combine
them into a single query:

SELECT tblProducts.IdProduct,
tblProducts.DescriptionProduct,
IIf([ModDate]=DateSerial(1899,12,31),"Yes","No") AS YesIfOut
FROM tblProducts LEFT JOIN
(
SELECT tblLoans.IdProduct,
Max(Nz([DateIn],#12/30/1899#)) AS ModDate
FROM tblLoans
GROUP BY tblLoans.IdProduct
) As Subquery
ON tblProducts.IdProduct = Subquery.IdProduct;

(As an aside, I have no idea why it's necessary to use Max. I would have
expected Min to work, but it didn't)

 

[Jose A. Fdez.]

Hi Douglas,
that's exactly what I wanted!
Thank you very much.

 

[Jose A. Fdez.]

Hi again,
finally I've used the following (it seems clearer to me):

SELECT tblProducts.IdProduct, tblProducts.DescriptionProduct,
IIf([aux]=2,"Yes","No") AS YesIfOut
FROM tblProducts LEFT JOIN (
SELECT tblLoans.IdProduct,
Max(iif(isnull([DateIn]),2,1)) AS aux
FROM tblLoans
GROUP BY tblLoans.IdProduct
) As Subquery ON tblProducts.IdProduct = Subquery.IdProduct;