Count problems in Query

[Mich2too]

Basics: Using Access & Word 2000
Member Table - provides name, address, etc. for primary member. Unique field is membership #.
Dependant Table - lists all adult children of the primary member who have membership cards. Unique field is membership#.

Need to create mailing labels using information from Member Table and including a Count of dependants for each Primary member (if any) so that the proper number of validation stickers can be mailed to the primary member when they pay thier membership dues.

Have experimented creating a query with all primary membership info listed first and the count of membership# from the dependant table and the query returned 14587 records. When the there are only 3400 members in the primary table, it doesn't take a brain surgeon to know this wasn't working. Moved the Count field to the beginning of the query grid and query returned only information on primary members who had dependants with membership cards.

Suggestions?? Help?? I am not familiar with Visual Basic or SQL.

 

[Toga]

In the design grid, relate the 2 tables to list all the dependents of the primary member.
From the menu, select View / Totals. This will add a Total field to the design grid.
Use GroupBy for your member fields and then select Count for the dependent field to count the related dependents.

SQL should look something like.....
SELECT MemberTable.MemberName, Count(DependentTable.DependentName) AS CountOfDependentName
FROM MemberTable LEFT JOIN DependentTable ON MemberTable.MemberNameID= DependentTable.MemberNameID
GROUP BY MemberTable.MemberName;

Toga

 

[Mich2too]

Been there....done that.

The query results only give me 429 records from the Member Table when I need ALL 3400. This query only gives me those members whose adult children have membership cards. I need to be able to print labels for those primary members whose adult children DO NOT have cards as well as those who do. Could make seperate sets of labels, but would be too time consuming to try to figure out which list to search for the label in. Want to keep things as effeciant as possible.

 

[Toga]

Is all 3400 adult children of the primary member related to the main table via the membership#? I'm assuming it must be. Assuming that, Your query link should be set up to pull all records from the main table and only the ones that match in the subtable.....Should work. Sounds like your somehow including the members with cards in your query expression somewhere if the one time you got a result of 429 and they all had cards..... That cannot be a part of the query.

 

[Mich2too]

The primary member table has 3400 plus records.
The dependant table has 937 records.
The two tables are linked via membership#.

I am pulling the membership #, name, address, etc from the primary member table and am pulling ONLY the membership# field from the dependant table to use as the count field. Have also tried pulling all fields from dependant table and only doing a count on the membership# field from that table with similar results. And yes...this "should work" but has not.

And yes... I DO want ALL the primary member listed. For security reasons, all mailing MUST be sent to the primary member ONLY.

 

[Toga]

I would have thought it was the other way around....937 records in the primary table with 3400 in the dependent table... I'm obviously missing something.

If you want to email me a small snippet of the 2 tables, I'll take a look at it fo ya.

toga@acmenet.net

 

[petermeachem]

Try this:

My tables are as follows. Table1 contains field member as a number. table2 contains the same member field and a child field.

First Query. Count the children.

( I'll just put the sql in as it is easier for both of us. Copy it in and change to design view.)

SELECT member, Count(child) AS CountOfchild
FROM Table2
GROUP BY member;

Save this as Query3

Second Query

SELECT Table1.member, Query3.CountOfchild
FROM Table1 LEFT JOIN Query3 ON Table1.member = Query3.member;

This will give the number of children if any.

Does that help?
Peter Meachem
peter@accuflight.com

 

[Mich2too]

Can understand how you'd think they were the other way around. We just recently started allowing adult children to have membership cards for mom & dad's memberships.

Have created a scaled down version of the tables to send to you. Members table only has 92 records, Dependants table only has 20 records. Actually, only 12 primary members have allowed their adult children to obtain membership cards. That means that the remaining 80 members still need validation stickers for their own cards while those 12 will need stickers for themselves as well as for their adult children. It is not manditory for a primary member to allow their adult children to obtain membership cards.

Anyone else wanting to see my sample DB...please e-mail me at shayes@t-one.net and I will send it. File is 3056kb.

 

[petermeachem]

Thats a wee bit large. What does it come down to if you compress it and/or zip it? Peter Meachem
peter@accuflight.com

 

[Mich2too]

Peter,

Nope...you pretty much lost me as I am not familiar with the SQL portions of Access. Even have me more confused as to which query you would have me use for the data when creating the labels in Word 2000.

Sample DB zips down to 533kb.

 

[petermeachem]

Query New. In dialog box select Design View and OK On Show table dialog click close. You can see a SQL button (actually a dropdown) on the far left of the toolbar. Click it. You get a big text box with SELECT; in it. Delete that and paste in

SELECT member, Count(child) AS CountOfchild
FROM Table2
GROUP BY member;

You see that the button has changed to the blus triangle design thing.

You will obvously need to change the query table and fields to suit your data. The second query will do for the merge, but you need to do select table1.* I expect.

Can't be more precise cos I don't know what your data looks like.


Peter Meachem
peter@accuflight.com

 

[Mich2too]

A GREAT BIG THANK YOU to EVERYONE who helped with this problem!!! My bosses were thrilled to death to see positive results so quickly from my original posting!! Have been allowed extra access to Tek-Tips from my work connection thanks to all of your help!!