VBA How Do I Do This

[RexOfNapa]

78 years young, Like Databases Not a programmer. Have played with access vba a little.
Really need some help here

I have two tables called tblActivities the other tblInterests.

tblActivities has three fields [Activity] , [Activity ID] ,[Count] < count is a number field>
tblInterests has two fields [Activity ID], [Badge] <Both are number fields>

The Activity tblActivities is fixed at 100 records <Acitity like Golf, Bowling, Travel, ;;;;>
The Badge can be any number of records. (think membership)

I have vba code that loops thru tblInterests that counts how many times
Each activity occurs in the tblInterests. I use dcount to count how
Many times that activity occurs in tblInterests. Dcount counts To a variable called intMyCount < Dcount is in a loop while .not EOF>

What I need to do is as the loop counts each activity in tblInterests
is to put that count into the [Count] field in tblActivities

I can’t even think how to do this. As the loop counts the first activity lets say <golf >and has the count in a variable called intMyCount
I need update tblActivitys [golf ], [count] = intMyVariable] < if intMyCount is 45 then tblActivity [Golf] count would be 45

When the loop finishes tblActivities count field would have the count of how many members are interested in that activity.

Hope that makes since and thanks as I don’t know where to begin.

Any help would be great and thanks!!!!!
RexOfNapa

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[theDBguy]

Hi. Welcome to AWF!

Storing calculated values in a table is not really recommended. Instead, consider doing the counts in a query.

 

[RexOfNapa]

After the update I want to use the table in a report.
which is no problem. I couldn't figgure out how to
count in a query or sql statment.

RexOfNapa

 

[theDBguy]

After the update I want to use the table in a report.
which is no problem. I couldn't figgure out how to
count in a query or sql statment.

RexOfNapa

As I was saying though, what you're trying to do is usually not recommended. You can still use a query for your report and get the same result as using a table.

 

[plog]

You don't store calculated values. You use a query and calculate them. Then you can base a report off that query. Here's a good tutorial on that:

Count data by using a query - Microsoft Support

Understand ways to count data, count data by using a Total row or by using a totals query, and view the Aggregate function reference in Access.

support.microsoft.com

 

[jdraw]

@RexOfNapa

Activity seems to be a sport or interest based on your posts.
What exactly would Badge represent for Activity Travel?

As others have advised, it is not a good database practice to store calculated values. Using a query if and when needed to get the current "value" is the best practice approach.

A query involving ActivityID and Badge with a Group By would seem appropriate. Have you tried such a query?

 

[pbaldy]

Welcome to the site. I moved your thread to a more appropriate forum. Send wine.

 

[RexOfNapa]

@RexOfNapa

Activity seems to be a sport or interest based on your posts.
What exactly would Badge represent for Activity Travel?

As others have advised, it is not a good database practice to store calculated values. Using a query if and when needed to get the current "value" is the best practice approach.

A query involving ActivityID and Badge with a Group By would seem appropriate. Have you tried such a query?

Badge used as a field with another table key field for other member stuff. I didn't know about the sum query. Sum query solved everything.

 

[RexOfNapa]

@RexOfNapa

Activity seems to be a sport or interest based on your posts.
What exactly would Badge represent for Activity Travel?

As others have advised, it is not a good database practice to store calculated values. Using a query if and when needed to get the current "value" is the best practice approach.

A query involving ActivityID and Badge with a Group By would seem appropriate. Have you tried such a query?

don't know that much about access but the table is sorted by ID so they are already grouped but I have no count no count

 

[jdraw]

What does Badge mean to you in this database in plain English? I know you have it in a table, but what exactly does it represent?
Does it mean you participate in the associated Activity?

So in tblInterests, you may have ActivityIDs 22,12,13,15,17. How does that relate to Badge?

 

[LarryE]

Rex:
Can you attach the database itself so we can look it? I have a feeling it is not even designed according to relational database requirements. I am not sure where you got the idea that looping through a recordset in order to count something was a good idea, but it's not. If you can attach the file for us to download, at least then we can see what you have so far.

 

[The_Doc_Man]

@RexOfNapa -

A COUNT query looks similar to this:

SELECT X, COUNT(X) AS COUNTX FROM TABLE GROUP BY X ;

The reading you need to do here involves "AGGREGATE QUERIES" as subject matter.

There is, of course, more to it than that, but this should get you started on developing your counts. Let us know what else you need to do with the counts, because there are ways to associate the counts with other queries such as you might want for reporting.

Just remember, for reports (which by their nature don't modify any tables) you can use a query or a table as a recordsource. If you need data from more than one table, there is such a thing as a JOIN query, which can happily JOIN tables and queries for various purposes.

 

[Pat Hartman]

The query would be more like:
Select Activity, Count(*) as ActivityCount
Group by Activity
From YourTable;

This will give you a list of ALL activities with counts for each. You would join to this query if you needed the count in a report or if the report showed details, you can just count them in the report itself.

In the future, consider looking for a query solution before you write code. The query solution will usually be easier to build and will almost certainly be more efficient processing.

Now that you know how to count, you don't have to limit the number of activities to 100 or any number. The rows of a table are never limited.