Count increment in query (1 Viewer)

smartenuk

Registered User.
Local time
Yesterday, 17:53
Joined
May 14, 2010
Messages
18
Greetings,
I am fairly new to Access and have enjoyed some success developing very simple forms and queries for my volunteer group. But now they have asked me to get a little more sophisticated with the data and I will agree I am over my head and need some assistance.
Help with incrementing a number of volunteers for specific role where there are a mixed collection is paramount right now.
 

MarkK

bit cruncher
Local time
Yesterday, 17:53
Joined
Mar 17, 2004
Messages
8,178
Welcome to the forum!!!

Help with incrementing a number of volunteers for specific role where there are a mixed collection is paramount right now.
Remember that we know nothing about your data or your system or anything, except what you've told us.
hth
Mark
 

smartenuk

Registered User.
Local time
Yesterday, 17:53
Joined
May 14, 2010
Messages
18
Thank you. Wasn't even sure anyone would reply.
I have 3 tables:
Volunteers Roster
Available Positions
Position/Volunteer Assignments

I need to be able to assign a volunteer to an assignment, see how many have already signed up for each position and keep a tally of each position.

Hope that explains my dilemma a little
 

MarkK

bit cruncher
Local time
Yesterday, 17:53
Joined
Mar 17, 2004
Messages
8,178
What fields are the in each table? How are the tables related? What is an assignment? What is a position? How does an assignment differ from a position?
The more we understand the problem, the better we can help.
hth
Mark
 

smartenuk

Registered User.
Local time
Yesterday, 17:53
Joined
May 14, 2010
Messages
18
I understand. an assignment is assigning a person to a position. Is there some way I can send you the tables or the entire data base. I am getting desperate due to a deadline looming for a working application.
 

smartenuk

Registered User.
Local time
Yesterday, 17:53
Joined
May 14, 2010
Messages
18
Attached is a sample of the data base I need help with.
I need a way to show how USERS can sign up for a VOLUNTEER POSITION in the VOLUNTEER POSITIONS table and increment and save the count each time someone joins that ASSIGNMENT.
 

Attachments

  • BalloonFestVolunteers.accdb.zip
    187.1 KB · Views: 33

MarkK

bit cruncher
Local time
Yesterday, 17:53
Joined
Mar 17, 2004
Messages
8,178
I would not expect the definition of a position (so one row in the the position table, which I would rename to simply tPosition) to include any date/time data. A position is an abstract idea of a thing, and that thing exists apart from any instance of it, like when and where it occurs. Your position table is like a course catalog at a school, and the list of courses offered by the school exists completely separately from WHEN and WHERE those courses will be offered as classes. See what I mean? A school might offer a course, but not this semester, so the course must be able to exist in the catalog independent of when and where you will create an "instance" of it. Likewise, you need to have a definition of a position that is completely agnostic about StartShift and EndShift.

Now, if you have shifts, where is the shift table? A shift is a thing, but it is not the same as someone actually showing up to work. The shift is an abstract idea that you might select from a list of shifts, and in this sense it is it's own thing, so you might need a shift table, "tShift" or "tblShift"

What is a user? In my mind this is a user of the software. Is that so, or is this a Volunteer? If the latter, rename the table to tVolunteer, or tblVolunteer.

Then, what is the purpose of this system? Are you tracking actual times that people show up and do things? Is that what a VolunteerAssignment is? You need to describe, and this is not clear to me from your data, what you are keeping track of here. Are these actual instances of people showing up somewhere to do things? If so, do you track the time they actually show up and the time they actually leave, and notice how that is different from a shift?

Is this making sense? But mainly, you need to describe, without jargon or technical language, what this system is for. What is your purpose? Notice how different the system will need to be if you are just planning a schedule, versus keeping track of actual hours worked for managing payroll or some other reckoning of actual time spent, vs proposed time spent. That definition is not clear to me from your data or your descriptions.

Also, this system needs a lot of work. Not sure of your time frame, but there are a lot of changes that need to occur, and since your tables are not very well developed, therefore I predict that your forms and queries will need to go thru significant changes too. I would stop all development on forms and queries until we have worked out the data model, the tables.

hth
Mark
 

smartenuk

Registered User.
Local time
Yesterday, 17:53
Joined
May 14, 2010
Messages
18
Thank you for your observations. The school course catalog is a very good analogy, however, most of the Classes listed in the POSITION table have unique starting dates times and only for specific duration. For this reason, the shift start and end times have to be a part of each position in the POSITION table. The sample data base has two purposes, first to verify the volunteer data via the VOLUNTEER DATA MAINTENANCE form, but more importantly the volunteers need to be able to scroll thru the positions and see if there is a vacancy and to sign up for the position via the VIEW JOB DESCRIPTION form.
 

smartenuk

Registered User.
Local time
Yesterday, 17:53
Joined
May 14, 2010
Messages
18
I appreciate the comment about its poor form but I am a beginner and inherited this mainly because I knew how to spell the word volunteer. :)
 

MarkK

bit cruncher
Local time
Yesterday, 17:53
Joined
Mar 17, 2004
Messages
8,178
Let me know if you have questions.
Cheers,
Mark
 

smartenuk

Registered User.
Local time
Yesterday, 17:53
Joined
May 14, 2010
Messages
18
I would appreciate any specific recommendations for making this work.
 

plog

Banishment Pending
Local time
Yesterday, 19:53
Joined
May 11, 2011
Messages
11,613

smartenuk

Registered User.
Local time
Yesterday, 17:53
Joined
May 14, 2010
Messages
18
plog thanks for the suggestion, but I just cant get it to work, the table is "tblPositions", the field I need to count by a group is "Qty" and the result field I would like is "signedUp". Can you make a suggestion? I have read your recommendations but still confused.
 

plog

Banishment Pending
Local time
Yesterday, 19:53
Joined
May 11, 2011
Messages
11,613
Have you found a thread? What have you tried in your code?
 

Users who are viewing this thread

Top Bottom