Dcount - Domain?

[TammyT]

Report is on a table of review items - each item can have a value of 1 (yes), 2 (no) or 3 (na). Each item will be listed several times - one time for each completed review.

I need to get the sum of all the 1s to then do an average based on the number of reviews completed. It looks like distint count is my best option, but I'm stuck on the 3 parameters! The first appears to be the field, the last would be the value; it's the domain field that has me stumped.

Example to help understand the report:

field name is RR027

Expression would be DCount([RR027], domain, 1)

I need to know what goes in domain.

Thanks!

 

[Remou]

DCount("[RR027]", "table of review items", "[RR027]=1")

 

[GingerR]

You have to use quotes, and the "domain" is the table name.

DCount("RR027","TableNameHere","RR027 = 1")

But are you saying that the 1,2,3 are in RR027? Typically you COUNT a unique identifier in the table, like an autonumber field or other unique field.

Dcount("RecordID","TableNameHere","RR027 = 1")

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mp9]

As an aside, I believe

Dcount("*","TableNameHere","RR027 = 1")

would be faster than

Dcount("RecordID","TableNameHere","RR027 = 1")

 

[TammyT]

Here's another problem, then: There are 42 fields on this report that will need that formula, & they come from several tables! I have about 175 review items that need to have data input on, & for each item there is the answer (yes/no/na), plus a comments field, so we're talking well over 300 "columns" in a table - can't be done - had to break it down, so I did it by section in the review tool, & now I have 10 tables capturing the data.

So I had to build queries to pull in the specific records that apply to our performance measure outcomes, & then I have to do some averaging. Also, the queries have data parameters so that only records between certain dates get pulled into the reports.

The formula is still not working - I'm getting an error. I think it's the "RR027=1" part, because when I take out RR027= & just leave "1" I get a result, but it's wrong - both RR027 records were answered no, which means they show a value of 2, so I should get a result of 0; however, I'm getting just a strict count & a result of 2.

Would I be better off somehow converting my answers from 1,2,3 to yes,no,na, which is what they should mean anyway?

Sorry if I've made it more complex! I thought that this should be easy!

 

[GingerR]

Sounds like you do not have your tables normalized. What is the structure of your tables and some sample data? By "structure" I mean to list for example:

Table: Table1
Field: RecordID (Autonumber)
Field: EmpName (text)
Field: EmpID (Integer)

etc

Sounds like you have a field for each "Review Item" when instead there should be two columns of data and 175 rows of data, i.e.:

ItemID Response Comment
1 Yes Here's comment 1
2 No Another comment


If you have your tables set up the way I think you do, you will have problems forever, as you can see already.....


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[TammyT]

I know where you're going, & I thought about that path; however, I was really stuck on how it would work.

Here's the Db plan:

It's designed to be a tool for case record reviews. Each review has some basic data that I put into tblReviewCaseInfo; the fields are:

ReviewNumber (Autonumber to keep each review unique)
CaseName (Text)
CaseManager (Text)
UnitNumber (Text because it's actually alpha-numeric data)
ReviewDate (Date/Time)
ReviewerA (Text)
ReviewerB (Text)
ReviewPeriodStart (Date/Time)
ReviewPeriodEnd (Date/Time)
Agency (Text)
County (Text)

Then there were the 10 tables to address the answers to the 175 or so questions & the comments that might be added to each answer. Each of those tables has the ReviewNumber field, to link them all together, & then a field RR001 & a field RR001Cmnt, going from 001 to 149, with some of them having letters as well, such as RR120a, RR120b, etc - I was matching the pattern of the Excel document that we're trying to replace, with all it's formulas & tedius entry!

Because each review has to have an answer to every one of the 175 questions (either yes, no or n/a), I couldn't figure out how to do that in just 1 table, using something like the method you're suggesting. I hope my Db explanation is clear. Once the data gets in, I have lots of formulas/percentages to work out!

If you can tell me how I'd be very appreciative!

Thanks.

 

[TammyT]

DUHHH!!!

I realized that, even though the option group choices are a value of 1,2 or 3 - in the table I had set the data type to text!

So, I just had to put quotes around the value & it works!

Both of these formulas work:

=Abs(Sum([RR028]="1"))
&
=Count([RR029]="1")

Does anyone have an opinion as to which is the better/more accurate/more stable/safer formula to use?

 

[GingerR]

Hi. In regards to the structure of your database: without seeing more detail of the structure of your other tables, it's hard to help. In general, copying the structure of an Excel spreadsheet isn't the way to go. An excel spreadsheet is not a relational database. A good rule of thumb is this: when you start repeating field names (Comment1, Comment2, Comment3) it's time to make a new table.

So what happens when (theoretically) when you add item # 150? You have to go add new fields to the table, change/add your queries, forms, reports, etc. Nightmare in the waiting....

For basics on normalization and relational db design, check out the links in my (and others') signatures.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[TammyT]

Thanks for your tips!

I've some moderate training in Access, none in VBA except what I've picked up (& "borrowed"!), & just lots of hands-on experience. Plus the great help from this forum!

I also just found out that one of our IT guys was a programmer for many years - I'll have him take a look at the structure & see what we can do to make the Db more efficient.

Thanks again!