By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Counting!

P: 1
Hello!

Sorry - new here! First query!

I want to display all of the records in Table1 but also there are loads of records in Table2 that have the same ID and I want to count each of these for each record in Table1

e.g.

Table1 =

ID
----
1
2
3
4

Table2 =
ID
-----
1
1
1
2
2
2
2
3

And I want an output that looks like:

ID Count
--- ---------
1 3
2 4
3 1

Sorry for being confusing!

Many thanks in advance!
Jul 26 '06 #1
Share this Question
Share on Google+
1 Reply


100+
P: 179
Create a main form using record source from table 1, and a subform on this main form, with the record source for the subform being table 2. On the subform, create an unbound textbox, and in the control source, type:

=Count([field])

[field] can be any field on the subform.

Open the subform in design form by itself (not part of the main form), and leave it open. Then open the main form, and create another text box on the main form. Click on Build Event, and use the Expression Builder. You then click on Forms, and work down to loaded forms, and down to the subform.

You will see your subform there twice. Click on the one under Loaded Forms, and double click on the text box you created on your subform.

Save everything and close.

Hope this helps.
comteck
Jul 26 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.