Something like Countif in Access?

[Gunti]

Hi,

I want to do something comparable to countif in Excel. I'll create a
(simple) sketch of how it should work.

I've got a list like this:

Water Fire 10 100
Water Earth 55 100
Water Wind 23 101
Fire Water 23 101

Basicly, what i want is this in a Query

Water Fire 10 100 (= countif ( column 1 = water))

I want the query to count how many times water (or whatever value is in
column 1 on that row) is in the entire column 1. In this case it would be:

Water Fire 10 100 3
Water Earth 55 100 3
Water Wind 23 101 3
Fire Water 23 101 1

I have to note that i have little to none experience in Access, i can only
use the 'design' mode. To create simple command like (and, or, right etc.).
If something like this needs SQL is needed, can anyone also refer to a simple
tutorial on how to use the advice given.


Thanks alot for anyone helping!
Gunti

 

[S.Clark]

DCount() function.

 

[KARL DEWEY]

Water Fire 10 100 3
Water Earth 55 100 3
Water Wind 23 101 3
Fire Water 23 101 1
The above seems poor. Why not this --
Water 3 Fire 1
Fire 1 Earth 1
Wind 1
Water 1
Or this --
Earth 1
Fire 2
Water 4
Wind 1

 

[Gunti]

@ Karl Dewey:
This was an example, the real access table consists of 15000+ records.

I think DCount should help me toward getting this far. I'm having problems
to define some stuff however.

I'll sketch the current situation

4 Columns:
Master, Sub, Techn, Comm,

940, 546, Pen, Cil,
940, 547, Nep, Cil,
940, 548, Pen, Cil,
940, 549, Pen, Cil,
941, 549, Pen, Cil,
941, 548, Pen, Cil,

Basicly, i want to introduce a fifth column where i need access to count how
often for example the current Techn + Master combo exists in the sheet. For
example (row 1): how often the combination Master 940 & Pen exists in the
list.

I'm having difficulties figuring this out with DCount.

 

[John Spencer]

SELECT Master, Sub, Pen, Cil
, (SELECT Count(Master)
FROM [YourTable] as Temp
WHERE Temp.Master = [YourTable].Master
AND Temp.Techn = [YourTable].Techn) as TheCount
FROM [YourTable]


Using DCOUNT you would enter an expression like the following:

DCount("Master","[YourTable]","Master=" & Master & " AND Techn=""" & Techn &
"""") as TheCount

If Master is not a number field you will need to change the third argument to
"Master=""" & Master & """ AND Techn=""" & Techn & """"
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Gunti]

First i want to say thanks for the effort so far.
It's just i still can't get it to work, feel like a dumbass :O

I'll try to explain exactly what i'm doing.

Two tables
A link between those two tables through 'Contract Number' & 'Sales Doc'
Creating a new query (Query1)

Adding some fields ('Tech Name', 'S Emplcomm name').

Now when i run it, it creates a list of ~ 9000 fields with 'Master
Contract', 'Contractnumber', 'Tech Name' & 'S Emplcomm name')

Basicly what i want, every mastercontract should have the same techname & s
Emplcomm name. I figured out i could get this through counting the
combination, the number a master contract exists & substracting this number
(If this doesn't equel 0 it means not every master contract has the same
name).

What i've done:

DCount("Master Contract";"[Query1]";"Master Contract=" & [Master Contract] &
" AND [Tech name]=""" & [Tech name] & "") as TheCount

I named my query 'Query1'. It tells me 'As' is an invalid syntax.

Thanks again for everything and i hope i made everything clear!

Gunti

SELECT Master, Sub, Pen, Cil
, (SELECT Count(Master)
FROM [YourTable] as Temp
WHERE Temp.Master = [YourTable].Master
AND Temp.Techn = [YourTable].Techn) as TheCount
FROM [YourTable]


Using DCOUNT you would enter an expression like the following:

DCount("Master","[YourTable]","Master=" & Master & " AND Techn=""" & Techn &
"""") as TheCount

If Master is not a number field you will need to change the third argument to
"Master=""" & Master & """ AND Techn=""" & Techn & """"
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

@ Karl Dewey:
This was an example, the real access table consists of 15000+ records.

I think DCount should help me toward getting this far. I'm having problems
to define some stuff however.

I'll sketch the current situation

4 Columns:
Master, Sub, Techn, Comm,

940, 546, Pen, Cil,
940, 547, Nep, Cil,
940, 548, Pen, Cil,
940, 549, Pen, Cil,
941, 549, Pen, Cil,
941, 548, Pen, Cil,

Basicly, i want to introduce a fifth column where i need access to count how
often for example the current Techn + Master combo exists in the sheet. For
example (row 1): how often the combination Master 940 & Pen exists in the
list.

I'm having difficulties figuring this out with DCount.

.