Counting in a query

  • Thread starter Thread starter MyVi
  • Start date Start date
M

MyVi

Hi everyone,

There's something about a query that I wonder how it could be done.

How would you count how many people there is within a column (from a
Query) and that this information it would appear in another column
(same query) all in each cel of the column? Well, I need to get this
result and if it is shown just once, as long as I can use it in a FORM
in all the lines of the query it is fine too.

Let me explain you a bit more in detail.

In the query there is a column (among others) named [NAMES], with this
information:

[NAMES]
JON
JON
JAMES
LAURA
RICHARD

and what I need to know how to.. is to count how many people there is
in this column. So in this case we have jon + james + laura + richard =
4 people.
Well, it would be interesting to get a simply new column in the same
query showing "4" all the time, so in each cell of the column, but what
I need it for is to show it in a Form, where it shows the informacion
of the query, and in one TextBox I need to see (for all the filds) the
result number 4.

Thank you for your support.

Vic
 
MyVi said:
Hi everyone,

There's something about a query that I wonder how it could be done.

How would you count how many people there is within a column (from a
Query) and that this information it would appear in another column
(same query) all in each cel of the column? Well, I need to get this
result and if it is shown just once, as long as I can use it in a FORM
in all the lines of the query it is fine too.

Let me explain you a bit more in detail.

In the query there is a column (among others) named [NAMES], with this
information:

[NAMES]
JON
JON
JAMES
LAURA
RICHARD

and what I need to know how to.. is to count how many people there is
in this column. So in this case we have jon + james + laura + richard =
4 people.
Well, it would be interesting to get a simply new column in the same
query showing "4" all the time, so in each cell of the column, but what
I need it for is to show it in a Form, where it shows the informacion
of the query, and in one TextBox I need to see (for all the filds) the
result number 4.

Thank you for your support.

Vic
Click to expand...

Well, for a start, if all you want to do is show, in a bound form, how many
records there are in it's recordsource, that is already shown in the
navigation buttons at the bottom of the window.

If you have removed the nav buttons, which is why you want to show the
number in a text box, you don't need to have it included in the
recordsource. You could add a function like this to your form:

Function RecordCount() As Long

With Me.RecordsetClone
If .BOF And .EOF Then
RecordCount = 0
Else
.MoveLast
RecordCount = .RecordCount
End If
End With

End Function

and set your text box's ControlSource to:

=RecordCount

To do EXACTLY as you requested, you could create a query along these lines:

SELECT * FROM
SomeTable, (SELECT Count(*) As RecordCount FROM SomeTable) AS RecordCount;
 
SELECT YourTable.LAST_NAME, Count(YourTable.LAST_NAME) AS CountOfLAST_NAME
FROM YourTable
GROUP BY YourTable.LAST_NAME;
 
Hi Baz.
First at all, thank you so much for the support.

Well, YES, right. What you say it is normal. I can see the number of
fields I have in the Query within the Bottom bar. But what I need is to
count the number of people on the column of the query, 'cause many
times these people do more than one thing and simply is shown in the
column.query more than once, but I don't want to count them more than
once.


Let's see the Query column.

[NAMES]
JON
JON
JAMES
LAURA
RICHARD
JAMES
RICHARD
RICHARD
JON

Here, in this column, we have 9 fields but we have 4 people (that is
what I need to be counted and be shown in a TextBox in a Form where I
see the info of the query).
Well, what I need is to get counted the number of differents names (or
could be numers as well) that you can see on the NAMES column. In this
exemple are 4 but each time can change. I would like to get it counted
the same if I change a real name for a number (lets say that a persona
is known by a number instead).


Karl, I've tried what you have wrote but, well, it shows me a bit more
about the Count option in the Query but it does not gives me the
resoult I really need.
I've tried two diferent things on what you have wrote. First I've
created a NEW query putting the SQL code and it does something but
weird, so far away from what I really need. Second, understanding what
I've got doing the first one I've tried to simply adding a new column
using the COUNT (in the TOTAL place in the Query Design View) but it
does just show me the nº 1 instead. So you have said no more details
and this is all I can go throught with some investingation.

Thank you guys for your support.

Vic
 
Gotcha!

So, what you want is something like this:

SELECT * FROM
SomeTable, (SELECT Count(*) As RecordCount FROM (SELECT DISTINCT NAMES FROM
SomeTable)) AS RecordCount;
 
Baz,
Now it works almost fine. BUT
there is still missing something.
Now it is counting properly the information you have into the TABLE
(the one you say as "SomeTable". In the column NAMES).
Well, that is good but not enough 'cause what I do in the Query is to
filter some things about this column and due to this, you don't have
the same people showing in the TABLE than in the QUERY. I've been
trying and I see perfectly that it does just count the column directly
on the table not the one of the QUERY (filtered).

What is the small thing we have to change to fix this?

Thank you.
We almost have it done.

Vic
 
MyVi said:
Baz,
Now it works almost fine. BUT
there is still missing something.
Now it is counting properly the information you have into the TABLE
(the one you say as "SomeTable". In the column NAMES).
Well, that is good but not enough 'cause what I do in the Query is to
filter some things about this column and due to this, you don't have
the same people showing in the TABLE than in the QUERY. I've been
trying and I see perfectly that it does just count the column directly
on the table not the one of the QUERY (filtered).

What is the small thing we have to change to fix this?

Thank you.
We almost have it done.

Vic
Click to expand...

Hi Vic,

Whatever criteria you are using filter the records in the form's
recordsource, just use the same criteria in the sub-query to which the count
is applied. Something like this:

SELECT * FROM
SomeTable, (SELECT Count(*) As RecordCount FROM (SELECT DISTINCT NAMES FROM
SomeTable WHERE some_field = "something")) AS RecordCount WHERE some_field =
"something";
 
YES,
now it works. I'm not sure whether I understood your correctly or
not... but based on what you said I did something else diferent and now
it works.

Thank you so much Baz.
 
Well, what I've done is:

0.- First to put the CODE in the Module:

Function RecordCount() As Long
With Me.RecordsetClone
If .BOF And .EOF Then
RecordCount = 0
Else
.MoveLast
RecordCount = .RecordCount
End If
End With
End Function

1.- I have created a NewQuery with the same data as the Table has.
2.- I have created AnotherQuery where I've put this SQL code:

SELECT *
FROM [SELECT Count(*) As RecordCount FROM (SELECT DISTINCT Names FROM
NewQuery)]. AS RecordCount, NewQuery;

3.- I made a FORM where Record Source = AnotherQuery.
4.- In the form, I put the TextBox where Record Source = RecordCount
5.- And WALA! It works!

Thank you so much Baz, for your support.

Vic
 
MyVi said:
Well, what I've done is:

0.- First to put the CODE in the Module:

Function RecordCount() As Long
With Me.RecordsetClone
If .BOF And .EOF Then
RecordCount = 0
Else
.MoveLast
RecordCount = .RecordCount
End If
End With
End Function

1.- I have created a NewQuery with the same data as the Table has.
2.- I have created AnotherQuery where I've put this SQL code:

SELECT *
FROM [SELECT Count(*) As RecordCount FROM (SELECT DISTINCT Names FROM
NewQuery)]. AS RecordCount, NewQuery;

3.- I made a FORM where Record Source = AnotherQuery.
4.- In the form, I put the TextBox where Record Source = RecordCount
5.- And WALA! It works!

Thank you so much Baz, for your support.

Vic
Click to expand...

You're very welcome.

If I understand you correctly, you've done what I suggested but you've
broken it down into a couple of steps, which is reasonable.

However, again if I understand you correctly, the code in the module is
redundant, it isn't doing anything, because you are getting the record count
from the form's record source (which is what you wanted all along).
 
Well, Baz...
Honestly speaking... I'm not such sure if the code in the module is
doing anything. I've not tried to erased it. Now it is working and I'm
pleased.
Now that I'm telling you this... I would like to say something else. At
the end I had to do another step on the FORM to get it work... 'cause
I've got a problem the day after (when I was using it). The fact is
that I got the form like just visual and not able to edit and add
anything. WOW! Bad thing! I have to input new data all the time and
this small thing is just giving me some information and not to stoping
me to edit the date. Well, what I had to do is anothef form with the
work of showing the date and then use a subform to put it in the
regular form. This way was showing and not stoping to edit anything.

Just giving this as more information (in case anyone could need it some
day) and do n't ask me much about deep program things 'cause I am not
an expert.

Although it is true that, in case the code would do nothing... it would
be better not having it, 'cause, at least, it eats some resource on
access while the db is working. Although in my case I don't think I'm
gonna feel it.

Thanks once again.

Vic
 
MyVi said:
Well, Baz...
Honestly speaking... I'm not such sure if the code in the module is
doing anything. I've not tried to erased it. Now it is working and I'm
pleased.
Now that I'm telling you this... I would like to say something else. At
the end I had to do another step on the FORM to get it work... 'cause
I've got a problem the day after (when I was using it). The fact is
that I got the form like just visual and not able to edit and add
anything. WOW! Bad thing! I have to input new data all the time and
this small thing is just giving me some information and not to stoping
me to edit the date. Well, what I had to do is anothef form with the
work of showing the date and then use a subform to put it in the
regular form. This way was showing and not stoping to edit anything.

Just giving this as more information (in case anyone could need it some
day) and do n't ask me much about deep program things 'cause I am not
an expert.

Although it is true that, in case the code would do nothing... it would
be better not having it, 'cause, at least, it eats some resource on
access while the db is working. Although in my case I don't think I'm
gonna feel it.

Thanks once again.

Vic
Click to expand...

It is inevitable that the form is not editable. A record source such as you
finished up with, full of sub-queries and whatever, is sure to be read-only.

The simplest way to get what you want whilst leaving the form editable would
be to:

1. Set the form's record source back to exactly what you had in the
first place i.e. with no attempt to count the unique values in the NAMES
field.

2. Create a query similar to this, and save it as, say, qryUniqueNames:

SELECT DISTINCT NAMES FROM SomeTable WHERE some_field = "something"

2. In the text box where you want to display the count, set it's Control
Source property to this:

=Dcount("NAMES","qryUniqueNames")

It isn't going to be particularly quick, though.
 
Back
Top