VBA How Do I Do This (1 Viewer)

RexOfNapa

New member
Local time
Yesterday, 22:11
Joined
Jun 9, 2022
Messages
5
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

Access World Site Owner
Staff member
Local time
Today, 05:11
Joined
Sep 28, 1999
Messages
7,305
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

I’m here to help
Staff member
Local time
Yesterday, 22:11
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

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

RexOfNapa

New member
Local time
Yesterday, 22:11
Joined
Jun 9, 2022
Messages
5
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

I’m here to help
Staff member
Local time
Yesterday, 22:11
Joined
Oct 29, 2018
Messages
21,358
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

Banishment Pending
Local time
Today, 00:11
Joined
May 11, 2011
Messages
11,613
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:

 

jdraw

Super Moderator
Staff member
Local time
Today, 01:11
Joined
Jan 23, 2006
Messages
15,364
@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

Wino Moderator
Staff member
Local time
Yesterday, 22:11
Joined
Aug 30, 2003
Messages
36,118
Welcome to the site. I moved your thread to a more appropriate forum. Send wine. :p
 

RexOfNapa

New member
Local time
Yesterday, 22:11
Joined
Jun 9, 2022
Messages
5
@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.
 
Last edited:

RexOfNapa

New member
Local time
Yesterday, 22:11
Joined
Jun 9, 2022
Messages
5
@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

Super Moderator
Staff member
Local time
Today, 01:11
Joined
Jan 23, 2006
Messages
15,364
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

Active member
Local time
Yesterday, 22:11
Joined
Aug 18, 2021
Messages
562
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

Immoderate Moderator
Staff member
Local time
Today, 00:11
Joined
Feb 28, 2001
Messages
27,001
@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

Super Moderator
Staff member
Local time
Today, 01:11
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom