Dsum()

  • Thread starter Thread starter Ezekiël
  • Start date Start date
E

Ezekiël

Is is it possible to sum up the count results of a field?

I have something like dsum("[id]";"table01";"Count([id])"), but it won't
work.

Can somebody help me out

Thx
 
You want to know how many records there are in table01 for each id value?

Paste this into SQL View of a query (View menu):

SELECT table01.id, Count(table01.id) As CountOfID
FROM table01
GROUP BY table01.id;
 
And then sum up each result of the id

Allen Browne said:
You want to know how many records there are in table01 for each id value?

Paste this into SQL View of a query (View menu):

SELECT table01.id, Count(table01.id) As CountOfID
FROM table01
GROUP BY table01.id;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ezekiël said:
Is is it possible to sum up the count results of a field?

I have something like dsum("[id]";"table01";"Count([id])"), but it won't
work.
Click to expand...
Click to expand...
 
The sm of the count of each id would be the same as the count of all records
in the table:
=DCount("*", "table01")

Guess I don't understand what you are trying to do.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ezekiël said:
And then sum up each result of the id

Allen Browne said:
You want to know how many records there are in table01 for each id value?

Paste this into SQL View of a query (View menu):

SELECT table01.id, Count(table01.id) As CountOfID
FROM table01
GROUP BY table01.id;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ezekiël said:
Is is it possible to sum up the count results of a field?

I have something like dsum("[id]";"table01";"Count([id])"), but it won't
work.
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen

Sorry i wasn't clear what i want to say, but basically i want to deduct the count of the duplicates id from the sum of all records

Greetings

Ezekiël
 
You've still lost me.
If one record is duplicated 3 or 5 times, it won't do much good to subtract
the number of records that have duplicates from the total count of records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Ezekiël said:
Hi Allen,

Sorry i wasn't clear what i want to say, but basically i want to deduct
Click to expand...
the count of the duplicates id from the sum of all records.
 
Allen,

I have e.g. 1000 records in total where 100 are double. I want to deduct 100
from 1000 = 900 unique records. Is this possible to do in a dcount or dsum
function?
 
To get a count of the number of unique records/values:

1. Create a query that returns whatever you consider to be unique records.

2. Typically, you would set the Unique Values or Unique reccords property in
the Property box.

3. Set your text box to:
=DCount("*", "YourQueryNameHere")
 
Back
Top