ACCESS FEED: Re: [MS_AccessPros] Dcount Function

Rabu, 19 November 2014

Re: [MS_AccessPros] Dcount Function

Thanks Duanne.

Ade

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Tuesday, 18 November 2014, 22:19
Subject: RE: [MS_AccessPros] Dcount Function

Ade,
Another solution would be to normalize your data using a UNION query [quniTest] like:

SELECT [Record No] as RecNo, 1 as Numb, [Median1] as Valu
FROM Test
UNION ALL
SELECT [Record No], 2, [Median2]
FROM Test
UNION ALL
SELECT [Record No], 3, [Median3]
FROM Test
UNION ALL
SELECT [Record No], 4, [Median4]
FROM Test
UNION ALL
SELECT [Record No], 5, [Median5]
FROM Test
UNION ALL
SELECT [Record No], 6, [Median6]
FROM Test

The results would look like:

RecNo Numb Valu
001        1         2
001        2         4
001        3         4
001        4         3
001        5         3
001        6         5

Then a simple totals query:

SELECT RecNo, Count(*) as Num4s
FROM quniTest
WHERE Valu = 4
GROUP BY RecNo;

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 18 Nov 2014 21:44:05 +0000
Subject: Re: [MS_AccessPros] Dcount Function

Thanks John it worked .

it was a perfect job. I have your book Microsoft Access 2003 Inside Out. You are indeed a MVP !

God bless.

Ade.

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, 18 November 2014, 20:53
Subject: Re: [MS_AccessPros] Dcount Function

Ade-

You are counting all the occurrences of the value 4 in column Median5 in any row in the first DCount. The second DCount gives you how many rows have the value 4 in the Median4 field.

To count by record, you need something like:

Count4: IIf([Median1] = 4, 1, 0) + IIf([Median2] = 4, 1, 0) + IIf([Median3] = 4, 1, 0) + IIf([Median4] = 4, 1, 0) + IIf([Median5] = 4, 1, 0) + IIf([Median§] = 4, 1, 0)

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Nov 17, 2014, at 11:45 PM, Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I need help with my query. I will simplify the table called 'Test' its based on as follows. Med1 through Med6 being fields.

Record No Med1 Med2 Med3 Med4 Med5 Med6

001 2 2 4 3 3 5

002 3 3 3 4 4 4

When I tried to create a calculated control to return the count of 4 in each row, I got 3 for record 1 and 3 for record 2 . The correct answer is 1 for record 1 and 3 for record 2.

The expression I used is shown below. Please assist as I do not know what I am doing wrong.

Expr1: DCount("[Median5]","Test","[Median5] = '4'")+DCount("[Median4]","Test","[Median4]='4'")+DCount("[Median6]","Test","[Median6]='4'")

Thanks

Ade