Dcount in query

[Radhika]

I have a query that involves three tables: 'tbl_GlissonAnalysis1',
'tbl_GlissonAnalysis2' and 'tbl_GeneralInfo'.
All three are linked by a field called 'ID#'. 'tbl_GlissonAnalysis1' has a
field called Trial#, where the Trial# is set as default to 1.
'tbl_GlissonAnalysis2' also has a field called Trial# that has the Trial# set
as default to 2. I want to add a column to the query that counts the # of
Trials for each ID#. For example, if ID# A has Trial 1 + Trial 2, I get '2'
and if ID# A has only Trial 1, I get '1'in the DCount field. The expression I
added to my qry to count the # of Trials for each ID# is:

DCount ( (*) "tbl_GlissonAnalysis1"l, "Trial#=1" AND "tbl_GlissonAnalysis2",
"Trial#=2")

However, I keep getting an error msg. What am i doing wrong? Is there any
other way I can count the # of Trials per ID#.

 

[Jeff Boyce]

Check the syntax on the DCount() function. I believe you'll need to count
the trial=1 in one table, then add that to the count of trial=2 in the other
table. That's two DCount() calls, not one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[PatHartman]

Your biggest problem is your schema. There should be only a single trial
table. That way you are not limited to two trials, you can have as many as
you want. you are also not bound up creating queries that have to join
multiple tables when simple totals queries will work -
Select IDNum, Count(*) as TrialCount
From yourtable
Group by IDNum;

If you have a single trial per ID in each table, you can do the count using
a left join:
Select tbl_GlissonAnalysis1.IDNum,
IIf(IsNull(tbl_GlissonAnalysis2.IDNum),1,2) As TrialCount
From tbl_GlissonAnalysis1 Left Join tbl_GlissonAnalysis2 on
tbl_GlissonAnalysis1.IDNum = tbl_GlissonAnalysis2.IDNum;

If you have multiple trials in each table, you can create a union query that
contains a totals query of each trial table and then a totals query that
sums the rows in the union.

 

[Ken Sheridan]

As Pat Hartman says your problem stems from the design flaw of having two
tables for the analyses. You should really merge them into a single table
with the ID# and Trial# columns as the composite primary key. This easily
done by creating an empty table, which you can do by copying it and pasting
the structure only, amending its design so the two columns form the primary
key, and then inserting all rows from each of the two existing tables with an
'append' query.

You can then count the rows per ID# in the query with:

DCount("*", "tbl_GlissonAnalysis", "[ID#] = " & tbl_GeneralInfo.[ID#])

or with a subquery:

(SELECT COUNT(*)
FROM tbl_GlissonAnalysis
WHERE tbl_GlissonAnalysis.[ID#] = tbl_GeneralInfo.[ID#])

You could do it with your current set up, by creating a UNION ALL query first:

SELECT [[ID#]
FROM tbl_GlissonAnalysis1
UNION ALL
SELECT [[ID#]
FROM tbl_GlissonAnalysis2;

and then counting the rows per ID# returned by the UNION ALL query, e.g.

DCount("*", "qryBothTrialAnalyses", "[ID#] = " & tbl_GeneralInfo.[ID#])

where qryBothTrialAnalyses is the name of the UNION ALL query. However, I'd
strongly recommend you merge the tables into a single table.

Ken Sheridan
Stafford, England

 

[bensalem]

Your biggest problem is your schema. There should be only a single trial
table. That way you are not limited to two trials, you can have as many
as you want. you are also not bound up creating queries that have to join
multiple tables when simple totals queries will work -
Select IDNum, Count(*) as TrialCount
From yourtable
Group by IDNum;

If you have a single trial per ID in each table, you can do the count
using a left join:
Select tbl_GlissonAnalysis1.IDNum,
IIf(IsNull(tbl_GlissonAnalysis2.IDNum),1,2) As TrialCount
From tbl_GlissonAnalysis1 Left Join tbl_GlissonAnalysis2 on
tbl_GlissonAnalysis1.IDNum = tbl_GlissonAnalysis2.IDNum;

If you have multiple trials in each table, you can create a union query
that contains a totals query of each trial table and then a totals query
that sums the rows in the union.