Not counting duplicate recrds in parent-child relatnshp(one2many)query 1

[Triacona]

Dear All,

Please could I get help on the following.
Your help will really be greatly appreciated.

I have a query (RpIvaCount)

That query has two tables RPINFO and XIREC.
Those tables are linked as follows:
One RPINFO case for many XIREC cases (One to many relationship)

The RPINFO case ref no field is:
UNI7LIVE_RPINFO.REFVAL (UNI7LIVE_RPINFO is Table REFVAL is the field)
The XIREC case reference no field is:
UNI7LIVE_XIREC.REFVAL (UNI7LIVE_XIREC is table REFVAL is the field)

So I get replication/duplication.
I.E. 06/00001/HMO has two child reference no's
06/00499/HMO
06/00498/HMO
So it will appear twice in the list, therefore counted twice.

So my question is how do I count only the one reference no. UNI7LIVE_RPINFO.REFVAL, with the Dcount function, without counting the replicates/duplicates?

I tried the below but it counted the duplicates.

=dcount("*","RpIvaCount","UNI7LIVE.REFVAL")

=DCount(IIf([UNI7LIVE_XIREC.REFVAL] Is Not Null,DCount("*","RpIvaCount","UNI7LIVE_RPINFO.REFVAL"),0),"RpIvaCount","UNI7LIVE_RPINFO.REFVAL")

=IIf(DLookUp("UNI7LIVE_XIREC.REFVAL","RpIvaCount") Is Not Null,DCount("*","RpIvaCount","UNI7LIVE_RPINFO.REFVAL"),0)

This within a new query....

IvaCount: count([UNI7LIVE_RPINFO] iif([UNI7LIVE_XIREC.[REFVAL] is not null,0))

Please help this is driving me bananas
Your help will be greatly appreciated.
Thank you

Kind regards
Triacona

 

[Remou]

Consider using DCount on a different query. here is an example I posted earlier:

http://www.tek-tips.com/viewthread.cfm?qid=1577626&page=1

http://lessthandot.com

 

[Triacona]

Thank you for your reply Remou.

I have tried to do it with other queries, but the still have the same two tables...

So there will be duplicates no matter what.
I what to count only a single reference number UNI7LIVE_RPINFO.REFVAL for the Query RpIvaCount without counting the duplicates/replicates.
Because UNI7LIVE_RPINFO.REFVAL can have many instances of UNI7LIVE_XIREC.REFVAL, I need a way to only count the first instance of UNI7LIVE_RPINFO.REFVAL in the query.

Is there a SQL code I could use? Is hhere a way I can do it within the query? Or create another query using that one.

Thanks again

 

[Ray1127]

Not sure exactly what you're looking for but try this

=Dcount("*","Select distinct UNI7LIVE_RPINFO.REFVAL from rpivacount")

 

[Triacona]

You're brilliant Ray, thank you!
I created another query (test) and used IpRvaCount as the table in that query.
I then edited the SQL to reflect your above code: SELECT distinct UNI7LIVE_RPINFO.REFVAL from RpIvaCount and it takes all the duplicates away!! yay!!!
Thank you so much!