Eliminate duplicate records

[vincentt via AccessMonster.com]

Hi all,

I have FORM link to a QUERY, which I could choose the criteria for searching,
however, hinder by 1 field. The query code as follow:

SELECT DISTINCT studentinfo.sid, studentinfo.slname, order.orderid, wat.
pcfprint, receipt.paid
FROM studentinfo INNER JOIN ([order] INNER JOIN (watusa INNER JOIN receipt ON
wat.orderid=receipt.orderid) ON (order.orderid=wat.orderid) AND (order.
orderid=receipt.orderid)) ON studentinfo.sid=order.sid;

Since in the table of RECEIPT, it might have several RECEIPT NO for 1 ORDER,
some of them may have PAID or UNPAID.

If I specify the SEARCH PAID or UNPAID, it would not have problem, however,
If I perform the search without concerning being PAID or UNPAID, it would
comes out 2 records, with same ORDERID, but 1 is PAID and other UNPAID.

For me, it is a DUPLICATE record, since I only need to SEARCH other Criteria,
e.g. wat.pcfprint = false without concerning PAID or UNPAID.

Could anyone help, how to eliminate the duplicate, instead only show either
PAID or UNPAID?

Thanks in advance!!!!!

 

[vincentt via AccessMonster.com]

Additional Info, the RECEIPT.PAID field is TRUE or FALSE field, so I could
not use MAX or MIN for it.

--
I am so new to Access... questions questions and questions...

Thanks always!

Message posted via AccessMonster.com

 

[John Spencer]

You can use FIRST or LAST or MAX or MIN. True/False fields are stored as -1
or 0.

SELECT studentinfo.sid
, studentinfo.slname
, order.orderid
, wat.pcfprint
, Min(receipt.paid) as PayState
FROM studentinfo INNER JOIN ([order] INNER JOIN
(watusa INNER JOIN receipt ON
wat.orderid=receipt.orderid) ON (order.orderid=wat.orderid)
AND (order.orderid=receipt.orderid)) ON studentinfo.sid=order.sid
GROUP BY studentinfo.sid
, studentinfo.slname
, order.orderid
, wat.pcfprint

If the display format of PayState is important to you then you can apply a
format to it where you are using it.

 

[vincentt via AccessMonster.com]

Thanks! You are brilliant!!! (or I am too....)

You can use FIRST or LAST or MAX or MIN. True/False fields are stored as -1
or 0.

SELECT studentinfo.sid
, studentinfo.slname
, order.orderid
, wat.pcfprint
, Min(receipt.paid) as PayState
FROM studentinfo INNER JOIN ([order] INNER JOIN
(watusa INNER JOIN receipt ON
wat.orderid=receipt.orderid) ON (order.orderid=wat.orderid)
AND (order.orderid=receipt.orderid)) ON studentinfo.sid=order.sid
GROUP BY studentinfo.sid
, studentinfo.slname
, order.orderid
, wat.pcfprint

If the display format of PayState is important to you then you can apply a
format to it where you are using it.

Additional Info, the RECEIPT.PAID field is TRUE or FALSE field, so I could
not use MAX or MIN for it.

--
I am so new to Access... questions questions and questions...

Thanks always!

Message posted via AccessMonster.com

 

[vincentt via AccessMonster.com]

I have tried it, it works fine to eliminate the duplicates. It is good when I
choose EITHER in the CRITERIA. However, if the SEARCH CRITERIA for the field
is FALSE, then the record will disappear, since it has been eliminated.

Any suggestion for it? Should I use another query or change the SQL when I
could TRUE or FALSE?

I need to pay more effort on it...

Thanks!

You can use FIRST or LAST or MAX or MIN. True/False fields are stored as -1
or 0.

SELECT studentinfo.sid
, studentinfo.slname
, order.orderid
, wat.pcfprint
, Min(receipt.paid) as PayState
FROM studentinfo INNER JOIN ([order] INNER JOIN
(watusa INNER JOIN receipt ON
wat.orderid=receipt.orderid) ON (order.orderid=wat.orderid)
AND (order.orderid=receipt.orderid)) ON studentinfo.sid=order.sid
GROUP BY studentinfo.sid
, studentinfo.slname
, order.orderid
, wat.pcfprint

If the display format of PayState is important to you then you can apply a
format to it where you are using it.

Additional Info, the RECEIPT.PAID field is TRUE or FALSE field, so I could
not use MAX or MIN for it.