ksa
Posts: 27
|
| Posted: 10/25/2011, 10:05 AM |
|
I have been sweating over this for days and just cannot make it work although it seems like it should be simple.
I have a table called "my_cds". In that table are columns "login" and "cd_title" among others
Users (login) can create a list of cds that they own, selecting them from a pre-existing list of cds. So far so good.
What I want to do is simply display how many cds each user (login) has on their list.
As the data source for the grid, I have the following:
SELECT login, COUNT(cd_title) FROM my_cds GROUP BY login
When I preview the data, it looks perfect! I get a list of users in one column and the number of cds that they each own in the second column.
What I cannot do is make a label in the grid display that number. I have messed around with adding a dlookup action to the label, but nothing works. I really hope someone here can point me in the right direction. Any help would be greatly appreciated.
|
 |
 |
Gena
Posts: 591
|
| Posted: 10/25/2011, 10:27 AM |
|
try this
SELECT login, COUNT(cd_title) as my_count FROM my_cds GROUP BY login
then use
login and my_count as a Labels source
_________________
Gena |
|
|
|
ksa
Posts: 27
|
| Posted: 10/26/2011, 10:22 AM |
|
Thanks for the reply! I'm afraid I need it spelled out a little bit more. When you say
SELECT login, COUNT(cd_title) as my_count FROM my_cds GROUP BY login
do you mean I should use that as the data source for the grid?
And when you say "login and my_count as a Labels source" where do I specify that? In Control Source? Or through custom code? And how? Sorry that I'm a little dense.
|
|
|
|
datadoit
|
| Posted: 10/26/2011, 10:40 AM |
|
For your label, which should be located somewhere inside your grid, add
the 'Retrieve Number of Records' action.
|
|
|
|
ksa
Posts: 27
|
| Posted: 10/29/2011, 3:01 AM |
|
That didn't work either. Then it just shows the number of the first row in all rows.
|
|
|
|
Gena
Posts: 591
|
| Posted: 10/29/2011, 3:23 AM |
|
Quote ksa:
Thanks for the reply! I'm afraid I need it spelled out a little bit more. When you say
SELECT login, COUNT(cd_title) as my_count FROM my_cds GROUP BY login
do you mean I should use that as the data source for the grid?
And when you say "login and my_count as a Labels source" where do I specify that? In Control Source? Or through custom code? And how? Sorry that I'm a little dense.
yes!! use select I wrote in your data source. Then put 2 Label in your grid and use
login and my_count
have you tried it?
_________________
Gena |
|
|
|
ksa
Posts: 27
|
| Posted: 10/29/2011, 3:57 AM |
|
Whoo hooo!!! That did it! Thank you so much. I finally understood. One thing I was doing wrong, however, was that I had the control source type set for Code Expression rather than Database Column.
Your help has indeed been useful as I have spent hours and hours trying to solve it. Much appreciated.
|
|
|
|
Gena
Posts: 591
|
| Posted: 10/29/2011, 4:24 AM |
|
Great that it helps you!
and thank you 
_________________
Gena |
|
|
|
