Select Based on Count

[CanWest]

I am feeling really silly right now. This should be simple but .....

I have a problem i just simply can not get my head around. Hope some one can help.

There are two tables in this db.

There is a one to many relationship set up between them

I want a query that will show records in the parent table only when there are a specific number of related records in the child table.

Does anyone know if this can be done and if so how?

 

[Sanjeet Prasad]

select the two tables in the query wizard.After that select the relationship and this is where youcan specifiy the retrival criteria to only get reords as you want.

 

[CanWest]

Select Based on count

select the two tables in the query wizard.After that select the relationship and this is where youcan specifiy the retrival criteria to only get reords as you want.

I have gotten this far. What i can't seem to get my head around if the criteria. I am not even sure if is the criteria row I should be using. What I want to do is show the record(s) from the parent table and the record(s) from the child table when the count of matching records in the child table equals a specific value, say 3.

The query would then show all records in the parent tables that had 3 related records in the child table.

 

[stopher]

I've used tow tables Names and CarOwnership as an example. Names has the field NameID and CarOwnership has the fields NameID and CarID.

SELECT Names.NameID, CarOwnership.CarID
FROM [Names] INNER JOIN CarOwnership ON Names.NameID = CarOwnership.NameID
Where Names.NameID IN 
            (SELECT Names.NameID
            FROM CarOwnership INNER JOIN [Names] ON CarOwnership.NameID = Names.NameID
            GROUP BY Names.NameID
            HAVING Count(Names.NameID)=3 );

If you take a look at the subquery you will see that it is an aggregate query that counts cars owened by NameID and selects only the ones that equal 3. You can the use this with the IN clause to create the criteria for your main query (as I've already done here).

hth
Stopher

 

[CanWest]

That Worked Great. I modified it to fit my tables and I changed last line to incorporate a parameter that says take the value entered in the parameter box, - 1 from that and use that

See the following code.

SELECT tbl_Contacts.ContactID, tbl_Calls.CallID
FROM [tbl_Contacts] INNER JOIN tbl_Calls ON tbl_Contacts.ContactID = tbl_Calls.ContactID
Where tbl_Contacts.ContactID IN 
            (SELECT tbl_Contacts.ContactID
            FROM tbl_Calls INNER JOIN [tbl_Contacts] ON tbl_Calls.ContactID = tbl_Contacts.ContactID
            GROUP BY tbl_Contacts.ContactIDD
            HAVING Count(tbl_Contacts.ContactID)= [Enter the Call Number]-1 );

When the variable is 0 the query does not show the records in the parent table that have no matching records in the child table. Is there a way around this?

 

[CanWest]

Oooops Spoke to soon

It worked but caused an unxpected side effect

Lets say the result was one record in the parent table with two matches in the child table. If I base a form on this query with a parent and child similar to the query I will see the parent record twice. Am I missing something really obvious or is this just the way it is.

 

[stopher]

It worked but caused an unxpected side effect

Lets say the result was one record in the parent table with two matches in the child table. If I base a form on this query with a parent and child similar to the query I will see the parent record twice. Am I missing something really obvious or is this just the way it is.

Do you mean you are using a form/sub form and you want contacts in the main form and the "2" CallIDs in the subform ?? To do this just using the subquery for the main form source and use the CallID table as the subform source because the subform just gives you the list on contacts that have only 2 Calls. Then your main form will scroll through all Contacts that have two Calls and the subform will display the two calls corresponding to the Contact. This should also solve your problem where there are no Calls.

hth
Stopher

 

[CanWest]

Do you mean you are using a form/sub form and you want contacts in the main form and the "2" CallIDs in the subform ?? To do this just using the subquery for the main form source and use the CallID table as the subform source because the subform just gives you the list on contacts that have only 2 Calls. Then your main form will scroll through all Contacts that have two Calls and the subform will display the two calls corresponding to the Contact. This should also solve your problem where there are no Calls.

hth
Stopher

I hope i am explaining and understanding this correctly. You are correct that is what i want. I tried using the subquery as the source from. By the subquery do you mean the part that starts at the second SELECT or am I not getting what you are trying to say.

 

[stopher]

I hope i am explaining and understanding this correctly. You are correct that is what i want. I tried using the subquery as the source from. By the subquery do you mean the part that starts at the second SELECT or am I not getting what you are trying to say.

What are you trying to do more generally ? There are many ways to skin a cat (apparently) and I wonder if your overall requirements may be solved in a more straightforward fashion.

Here's my guess...
You want a form to open where you specify a number of calls. The form then filters only Contacts where the number of calls equals the number of calls specified minus 1 (!?!). I'm guessing you want the user to review all Contacts that are on the nth call.

I reckon the simpler way to approach this is to create a form/subform where the form is based on all contacts and the subform is based on the calls. Then add an unbound text box to your main form where you enter the number of calls. Then when the number of calls is entered, a bit of code runs which applies a filter whichs filters only contacts with n. number of calls. I haven't thought this through fully and I'm sure other folks will have different ways of approaching this.

If I get time I'll maybe knock up an example but it's getting late here and my wife wants some attention

Stopher

 

[stopher]

Hi
Here's an example of a form/subform setup where you enter the number of calls in the box on the form and the form will filter to show all records that have the number of calls entered -1 (I'm not exactly sure why you have the minus 1 bit but hey ho)

So open Form1. Note that the total number of contacts is 5 (from the navigator at the bottom)

Enter 4 into the "Enter Calls NUmber" box. Then when you hit tab or enter, you will see the number of of available records change to 2 i.e. only the CallIDs that have 3 calls.

There's also a clear filter button for convenience.

The theory...

The main form is based directly on the contacts table

The subform is based directly of the calls table

There is an AfterUpdate event for the enter call number button that implements a filter on the form depending on the value of the call number. If you take a look at the code for this event you'll see how the SQL is constructed. Note how I've dealt with the case where call numbers is zero (the problem you were experiencing). I've used a slightly different SQL statement to manage this case.

Hope that helps or gives you some ideas.

Stopher

 

[CanWest]

Hi
Hope that helps or gives you some ideas.

Stopher

Oh man does it ever! This is so simple now that I see it in action. This will eliminate three queries. Thank you so much. I would never have taken that approach.