Find count of records that use a particular Foreign Key

[cheese]

This is an extremely basic thing I wish to do but I do not know how to implement it in Access. I am not allowed to use VBA.

I wish to be able to count how many times a primary key is being used in another table. In other words:

I have a Game table that lists a set of games by catalogue number, CatalogueNo. I need to create a way to find out how many times that key has been used in a GameCopy table. In the GameCopy table I create a number of stock items (StockNo) using the CatalogueNo from the Game table.

If I can in some way create a query that simple lists each CatalogueNo with its total number of copies that will suffice. I know it is most likely very simple but I just do not know how to implement it within Access without using VBA.

 

[CJ_London]

sounds like this is a question you have been set, so you need to do your own research - this link may help

http://www.w3schools.com/sql/sql_func_count.asp

 

[cheese]

sounds like this is a question you have been set, so you need to do your own research - this link may help

http://www.w3schools.com/sql/sql_func_count.asp

No, it is a question that I have set in http://Access-Programmers.co.uk. I am familiar with the page you have provided a link to in the same way as I am familiar with http://Google.com. I am making a reference to doing this in Access. Also, it is not a question that has been set for me; it is merely part of a project!

 

[jdraw]

So did you follow the link that CJ gave you?
Did you try something? Do you want to show us what you tried? What exactly do you want help with at this point?

Look at Access query wizard; and SQL view.

 

[cheese]

I thought that I had described what I wished to do in the original post. I wrote it as clearly as possible. I wish to be able to calculate the number of times that a Primary key value has been used in another table and wish to display it within a query based on the Game table itself.

I can get a count of the total number of records within either table (query design or SQL) but I am unaware of how to narrow it down within an Access query. I need to get the total count of those records for each CatalogueNo. I do not know how to put it any more clearly than this.

I was under the impression that this would be extremely easy to do for someone who actually knew how to do it within access. It is easy to do on a web site with a SSS language and SQL. I am trying to avoid dodgy techniques like incrementing a table field value through a form event. They can be hazardous because you have to make sure that the event is triggered under all conceivable circumstances or restrict certain insert methods.

 

[jdraw]

You did describe it terms you understand, since you are familiar with the problem. Communicating that probhlem to others out of context seems to be the issue.

Anyway are you saying you didn't look at the link CJ gave you?
The place that has several SQL samples including
Count and Group By

You haven't told/shown us your table structure.
What is a Game table as compared to a GameCopy Table, and where does CatalogueNo fit.

Here's another link that is relevant
http://www.w3schools.com/sql/sql_groupby.asp

Good luck

 

[cheese]

...I am familiar with the page you have provided a link to...

I was making a reference to the link that was given by CJLondon. I am familiar with count in SQL and I use W3schools extensively.

ANYWAY... I have done it and thank you for the GroupBy suggestion jdraw. That was the one I needed!

SELECT Game.CatalogueNo,COUNT(GameCopy.CatalogueNo) AS CopiesHeld FROM GameCopy
LEFT JOIN Game
ON GameCopy.CatalogueNo=Game.CatalogueNo
GROUP BY Game.CatalogueNo;

The problem is that I have a bad tendency to use scripting (c#/ PHP) to do what I need as opposed to SQL. It means that when it comes to Access I tend to struggle. There are possibly several things that I do in scripting that can be done easier with SQL but we cannot know everything. I am totally self taught so I am bound to do things in an impractical way from time to time.

 

[cheese]

Actually you do not even need to do the sql; you can just use the query design and then use Totals/ Count/ GroupBy within there.

 

[jdraw]

Glad you have it working.
Yes it can all be done in query designer, if you're visual and understand the graphics. The query designer is just a "friendlier view" above the SQL view -- or at least that's what it says in M$oft materials.

You'd be surprise how many posters have never seen the query designer, let alone sql.

Anyway, glad it's working.