unique records in a dcount? 1

[kmcculler]

Hi, I need a little help trying to select a count of unique records from a select. Currently I'm doing this using DCOUNT :

RCs = DCount("parent_id", "LinkTable", "PARENT_TYPE='RC' AND CHILD_TYPE='SR' AND CHILD_ID In (SELECT child_id FROM LinkTable WHERE parent_id=" & E3 & &quot&quot


This gives me all of the type RC that are linked to the type SR in LinkTable. It works exept for the fact is a M:M relationship and when a single SR is linked to multiple RC's I need the UNIQUE RC's not the total. (This number is used in a calculation of a score rating on the same form). Does anyone have any idea how I could get a count of UNIQUE records from this? Kris McCuller
Programmer Portiva Corp.
kmcculler@portiva.com

 

[Dustman]

Well.. I'm not following your code too well.. I've never really used the dcount. What exactly is the "In" statement doing?

Could you possibly do this with an sql statment using "SELECT DISTINCT"? -Dustin
Rom 8:28

 

[kmcculler]

acutally I wouldn't mind using an sql statement at all if I could figure out how to use it right. I know how to contruct my query to produce the right answer in SQL but, currently the only way I know how to do an sql statement in code and get a value returned is using OPENRECORDSET. That always produces the following error:

object variable or with block variable not set

The sql statement would be:

  select count(distinct parent_id) from LinkTable where  PARENT_TYPE='RC' AND CHILD_TYPE='SR' AND CHILD_ID In (SELECT child_id FROM LinkTable WHERE parent_id=" & E3 &")

If you could tell me how to impliment this so I can get the number of unique RC's into a variable that will work!!!

As for the IN statement a little explaining is in order.
The way the data is structured here:
type ACT is a parent to type SR
type RC is a parent to type SR
When I enter this form I have the ID of ACT (I could also get each of the SR's but I would rather not do it that way because the number of children is never the same) Thus the query inside the IN statement gives the ACT ID ("E3&quot and retrives ACT's child ID's ("the SR's&quot. The IN statment reqires that the CHILD_ID seleced in the where section of the SQL to be in that list of ACT's Children.

So the completed where statement in english would be:
where the parent type is RC, the child type is SR, and the child's ID is also a child of the ACT's ID Kris McCuller
Programmer Portiva Corp.
kmcculler@portiva.com

 

[kmcculler]

Update: actually I solved my
object variable or with block variable not set
problem. (I forgot to assign db as currentdb).
However I still have the problem of the openrecordset will not allow me to use
select count(distinct parent_id)
if the distinct is removed it works, but i get the same results as the original dcount staement.... I need a way to exclude those repeating id's.
plus seem to be having a problem refrencing the value returned but Im sure I can work that out on my own. Kris McCuller
Programmer Portiva Corp.
kmcculler@portiva.com

 

[Dustman]

Okay.. that makes sense. This should do it for ya.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("select count(distinct parent_id)

 AS rcCnt

 from LinkTable where  PARENT_TYPE='RC' AND CHILD_TYPE='SR' AND CHILD_ID In (SELECT child_id FROM LinkTable WHERE parent_id=" & E3 &")")

NumberOfRcs = rst!rcCnt

If you get an error, check your references (select Tools/References in the code window) and make sure Microsoft DAO 3.6 Object Library is selected.

If you are using Access97, you don't have to include the ADO, just use database and recordset.

Just remember, you can reference any column in your recordset by using recordset!column.

I hope that helps you out! -Dustin
Rom 8:28

 

[Dustman]

Ack.. I must have just missed you! Try using SELECT DISTINCT COUNT(parent_id) instead. I think that should work.
-Dustin
Rom 8:28

 

[kmcculler]

Thanks but placing DISTINCT outside of the count() doesn't give me distinct parent_id's only distinct counts. Thus it produces the same results as the DCOUNT function.

The actualy result Im getting is 10 when E3 is 1559 most of those are repeats and I only have 4 unique RC's.

I tried building an actually query for it and I've made it work. (through alot of tweaking), but acess for some reason doesn't want to recognize that it exists. (I can refrence others but not my new query)

so Im still out of luck. and just FYI Im working in Access 2000 and the DAO library is refrenced correctly.
Thanks Kris McCuller
Programmer Portiva Corp.
kmcculler@portiva.com

 

[Dustman]

Hmm.. I'm having trouble picturing what the layout looks like. I'm about to leave for work but I'll try to get back on this tonight and see what I can come up with.

I am curious.. does using DISTINCTROW produce different results than DISTINCT? I'm pretty sure that won't be the right number but does it change things? -Dustin
Rom 8:28

 

[kmcculler]

DISTINCTROW produced the same results. (10) Kris McCuller
Programmer Portiva Corp.
kmcculler@portiva.com

 

[kmcculler]

Thanks but Ive got it now...
The solution was to open a recordset selecting distinct parent ID's and then to use RECORDCOUNT which I wansn't aware of till now.

Set rst = dbs.OpenRecordset("select DISTINCT parent_id from LinkTable where  PARENT_TYPE='RC' AND CHILD_TYPE='SR' AND CHILD_ID In (SELECT child_id FROM LinkTable WHERE parent_id=" & E3 &")")

NumberOfRcs = rst.RecordCount

This gives me the number (4) of unique parent_id's.... Thanks for all your help Kris McCuller
Programmer Portiva Corp.
kmcculler@portiva.com