Access Database To Track Coworker Daily Fitness Accomplishments

My coworkers and I are having a contest to see who can get the most pull-ups and miles run per day. We need to track this daily over the course of every month throughout the year. I tried to make this in Excel, but it was a mess because people kept putting the wrong info in the wrong cell. I think it would work best in Access, but I am totally lost when it comes to building the right tables and forms (read that as: I know nothing about doing this in Access).

I already have a database started.
My first table has all of the names:
Allen James T
Baker Mike L
Collins Susie B
etc
etc

I just don't know how to make a table that tracks daily scores of pull-ups and miles ran. I only know what the output should look like.

Here is what I'm looking for:
example form
example report 1
example report 2
Here is how I picture it looking:
A coworker opens the Access file and there is a form that is user friendly. They can select their name from a dropdown, and enter their pull-ups for the day or their total miles run (it can be a separate form for each if need be). They need to be able to do this every day. (If they don't enter something, then it just shows a zero for that day in the report)

I imagine that a report can be made to show the top people in pull-ups and running. We also want to see a list that shows each day in the month, and then overall scores since tracking began.

I'm sure an Access guru can bang this out pretty fast.

This is more for fun than for work. Hopefully, someone out there can help me out. :-)
LVL 6
Paul WagnerFriend To Robots and RocksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Add two more tables.
tblActivityType
ActivityName  (ex - run, walk, row, pull up, push up, etc (this is the primary key)
UnitName (ex - miles, steps, count, etc)

tblPersonActivities
PersonName (primary key field 1, Foreign key to tblPeople)
ActivityName (primary key field 2, Foreign key to tblActivityType)
ActivityDate (primary key field 3)
Units

This is a many-to-many relationship between people and activities (which is why it is so difficult to represent in Excel).  The glue is tblPersonActivities.  It is the junction table and it relates the other two tables together.  I've attached a working sample that you can examine to see how to build your data entry forms.
ManyToManyAXP.mdb
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
How do I make multiple fields a primary key in tblPersonActivities?

In your template example, which tables are correlated to each other in the database I'm trying to make?
(i.e. - Does tblBookings represent tblPeople in my database?)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Rather then multiple fields for keys, I like to use artificial keys that join using numbers that don't mean anything to the users -- AutoNumber in the main table and Long Integer (no Default Value) in the related table.  Here is a short video to explain relationships using that method to you:

video Tip: Enforce Referential Integrity on Access Relationships (cc) closed-caption
http://www.youtube.com/watch?v=_zxxc9jzWEg
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Paul WagnerFriend To Robots and RocksAuthor Commented:
@Crystal - Thanks for the video link. That was helpful!

Based on the instructions (or what I think you're asking me to do), I opened the relationships for these tables:
tblActivityType
tblPeople
tblPersonActivities

Is this right?
I'm sure this is wrong.
How do I get the one-to-many for the people's names?
Also, how do I make multiple fields a primary key in one table?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome and thank you

first, name the ID fields so you can keep them straight.  ActivityID, PeopleID, and PplActivityID will be AutoNumber fields in each of their respective tables.

Don't link on ActivityName -- use the primary key -- artificial number keys will be hidden on the entry forms but work because Access is wonderful :)

... so delete the relationships you created.

in tblPersonActivities, you need to have foreign keys for: PeopleID and ActivityID. The foreign keys will be Number and then Long Integer data type in the bottom pane.  When you make the fields, be sure to delete the default value of 0 (zero) that Access automatically supplies when you create a number.

Now that you have fields with the same names in each of the tables, it is more obvious where the links are.  Drag from the primary key (the field with the key next to it) to the foreign key.  PplActivities will have 2 lines going to it -- one from People and one from Activities.

Don't worry about the activity name not actually being IN the pplactivities table -- the foreign key means you can show this information anytime :)

... and since PeopleID is a foreign key in that table too, you do not need to repeeat any of the fields that are already in the People table (Grade, Lastname, Firstname, MI, etc)

In People, RENAME Section to be --> Sectn since Section is actually a reserved word and will cause problems if you use it.  Again, don't worry about the fieldnames -- on the form you can make a caption that says "section"
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
first, label the ID fields so you can keep them straight.  ActivityID, PeopleID, and PplActivityID will be AutoNumber fields in each of their respective tables.
Done
ID changes
Here are the Fields I have made:
Fields
The drag and drop of relationships is still unclear to me.
Am I supposed to do this?
relationships round 2
When I try that, it only makes a 1-1 relationship
create round 2
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you are getting closer.  A couple points though

I think you missed this:
"... and PplActivityID will be AutoNumber fields in each of their respective tables."

and this:
"...in tblPersonActivities, you need to have foreign keys for: PeopleID and ActivityID. The foreign keys will be Number and then Long Integer data type in the bottom pane.  When you make the fields, be sure to delete the default value of 0 (zero) that Access automatically supplies when you create a number." -- these fields get added. They are not yet there.

With all due respect to you, I edited my post after you read it to add more information ... so read it again please.

____________________
it is not valid to create a relationship from one AutoNumber to another ... think about it.  These numbers are being generated in each table, how could they possibly match on the right records except by chance? (and don't worry about this, it is a common mistake)
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
PplActivityID will be AutoNumber fields in each of their respective tables.
You lost me.
PplActivityID is only listed in one table: tblPersonActivities

When you make the fields, be sure to delete the default value of 0 (zero) that Access automatically supplies when you create a number.
OK, did that:
Table corrections
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in tblActivityType, the PrimaryKey will be ActivityID. ActivityName can have a Unique Index on it so the values are not duplicated.  In the lower pane --> Index = Yes (No Duplicates)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
PplActivityID should be an AutoNumber and the Primary Key
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
in tblActivityType, the PrimaryKey will be ActivityID. ActivityName can have a Unique Index on it so the values are not duplicated.  In the lower pane --> Index = Yes (No Duplicates)
OK
ActivityID
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
PplActivityID should be an AutoNumber and the Primary Key
Done
pplactivityid
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you should have something like this:
table designs for tracking activitiesrelationships for tracking activities
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
HAHAHA! A picture is worth a THOUSAND words!! That makes a lot of sense now!

I have now made the flux capacitor!!
working relationships
flux
Looks like I have my tables, fields and relationships.

Now, do I make the forms or is there some more back-end work to be done?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
good, I am glad you see :)

The entry form is actually collecting information for two records in the PeopleActivities table.  It is easier to make this form unbound, which means it won't have a Record Source and the controls won't have a Control Source. Then when SAVE is clicked, Access can go create the records in PplActivities

the first column of the listbox is PeopleID and it will have a width of zero so it doesn't show. The second column, the one that does show, will concatenate the names.
ColumnWidths: 0;1.5

then we have NbrUnits to fill for the two records -- but if something was 0 or not filled out, there is no reason to create a record for it. ... unless you want them to be in the habit of using the program everyday even if they did nothing (perhaps a point for entering data?)

Once you have the form created, post back ~
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
This is probably wrong
form viewform view
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
to make an unbound form, choose Form Design from the Create ribbon.  This form will not have a record source.

turn on the Property Sheet if it is not showing (Alt-Enter, or right-click somewhere and choose Properties from the bottom of the shortcut menu)

From the Design ribbon, pick the Listbox tool and make a listbox.  I have my wizards off so I will tell you what properties to set:

Listbox Properties:
Name --> PeopleID
RowSource -->
SELECT People.PeopleID, [Lastname] & ", " & [Firstname] & " " & [MI] AS FullName
FROM People
ORDER BY [Lastname] & ", " & [Firstname] & " " & [MI];

ColumnCount --> 2
ColumnWidths --> 0;1.8
Width --> 2
Status Bar Text --> pick your name

then create 2 unbound textboxes to fill the number of pull-ups and the total miles run.  After you create them, give them good Names (using the Property Sheet) like NbrPullups and NbrMiles
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
row source
design2
view 2
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
when you are in the RowSource property, click the Builder ... button and it will take you to something that looks like a query design screen.  Add the People table. Fill out the grid yourself.  Click on the Datasheet View to see what the records look like and to get a good idea how wide the column that shows needs to be. Just use what I gave you as a guide ~

RowSourceType should be Table/Query not Value List
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
Build... is greyed out
build
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
RowSourceType should be Table/Query not Value List
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
I'll have to continue tomorrow. Need to get some sleep.

Thank you for your help thus far!!!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome.  If I am not around, hopefully Pat will jump in ... Farmer's market is in the morning :)
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
If you see what I see, maybe you can help get me to the goal.

I attached an example of where we are so far.
The tables look good and the form seems to work (probably needs to look prettier, but I can work on that).

I found a simple form for what I was trying to do by using combo boxes and it works!
Now, we just need to get that placed into a report. I made a report, but it only showed the IDs and not the "people" information like name, activity (pull-ups, etc.) and date.

Looking at my examples in the OP, how can I make reports to look like that?

Here is one I tried:
bad report

This report seems to break each individual entry down onto the report. See Beltran for an example. There are both entries made for pull-ups when I am trying to show all pull-ups overall to date.

Also, I am trying to make a report that shows each person's pull-ups and miles ran in each report for the month of October, November, etc.
We want to see overall scores and then monthly scores.

I hope that makes sense.
EXAMPLE.accdb
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
looking now .. and changing things
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Paul,
switched your database to overlapping windows and removed AutoCorrect.

added dtmAdd tracking field (data type = date/time, DefaultValue=Now() ) to each of your tables so the date/time a record was created can be known.

tblPeopleActivities: removed DefaultValue of 0 for NbrUnits
(if miles may be a fraction, data type should be changed to Double Precision)

tblActivityType:
changed SIZE of ActivityName to 50 (may still be too long), and UnitName to 20

tblPeople:
changed SIZE of Grade to 10, LastName to 50, FirstName to 50, MI to 5, Platoon to 50
also removed Format =@ from each field (you imported this, didn't you?)

Removed RecordSource from your entry form so it can look more like you envision.  Removed controlSource for each control since the form is no longer bound.

Changed the entry form to look more like your example.

Removed Layout so the controls can be sized independently

Changed Combobox for PeopleID to a listbox. Removed controlSource and changed Name to PeopleID
RowSource:
SELECT tblPeople.PeopleID, [LastName] & ", " & [FirstName] & " " & [MI] AS Fullname, tblPeople.Grade, tblPeople.Platoon
FROM tblPeople
ORDER BY tblPeople.LastName, tblPeople.FirstName, tblPeople.MI;

ColumnWidths: 0;1.2;.6;.8
ColumnCount: 4
Width: 2.8 (sum of ColumnWidths + 0.2" to allow for scrollbar)

removed combo for ActivityID since now the form is unbound (to be more like you imagine)

renamed NbrUnits to NbrPullups and added another textbox for NbrMiles

made default value of ActivityDate =Date()  (today)

changed label names accordingly

created command button:
Name = cmd_Save
Caption = Save

click event:
Private Sub cmd_Save_Click()
'160924 crystal (strive4peace)
   'set up error handler
   On Error GoTo Proc_Err
   
   Dim sSQL As String _
      , nDate As Date _
      , iNbrPullups As Integer _
      , iNbrMiles As Integer _
      , dtDate As Date _
      , nPeopleID As Long
      
      
   With Me.PeopleID
      If IsNull(.Value) Then
         .SetFocus
         MsgBox "You must choose a name from the list" _
            , , "Missing information"
         GoTo Proc_Exit
      End If
      nPeopleID = .Value
   End With
   With Me.ActivityDate
      If IsNull(.Value) Then
         .SetFocus
         MsgBox "You must enter an activity date" _
            , , "Missing information"
         GoTo Proc_Exit
      End If
      dtDate = .Value
   End With
   With Me.NbrPullups
      iNbrPullups = 0
      If Nz(.Value, 0) = 0 Then
         .SetFocus
         If MsgBox("No pull-ups entered -- is this correct?" _
               , vbYesNo, "Number of Pull-ups is 0") <> vbYes Then
            GoTo Proc_Exit
         End If
      Else
         iNbrPullups = .Value
      End If
   End With
   With Me.NbrMiles
      iNbrMiles = 0
      If Nz(.Value, 0) = 0 Then
         .SetFocus
         If MsgBox("No miles entered -- is this correct?" _
               , vbYesNo, "Number of Miles is 0") <> vbYes Then
            GoTo Proc_Exit
         End If
      Else
         iNbrMiles = .Value
      End If
   End With
   
   Dim db As DAO.Database
   Set db = CurrentDb
   
   'pull-ups ActivityID=1
   sSQL = "INSERT INTO [tblPeopleActivities] " _
      & "( PeopleID, ActivityID, ActivityDate, NbrUnits ) " _
      & " SELECT " & nPeopleID _
      & ", 1" _
      & ", #" & dtDate & "#" _
      & ", " & iNbrPullups _
      & ";"
   db.Execute sSQL
   
   'miles ActivityID=2
   sSQL = "INSERT INTO [tblPeopleActivities] " _
      & "( PeopleID, ActivityID, ActivityDate, NbrUnits ) " _
      & " SELECT " & nPeopleID _
      & ", 2" _
      & ", #" & dtDate & "#" _
      & ", " & iNbrMiles _
      & ";"
   db.Execute sSQL
   
   MsgBox "Done creating activity records for " & Me.PeopleID.Column(1) _
      , , "Done"
      
   'clear values on form
   Me.NbrMiles = Null
   Me.NbrPullups = Null
   
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set db = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   cmd_Save_Click "

   Resume Proc_Exit
   Resume
   
End Sub

Open in new window


space underscore at end of line means statement is continued on the next line

Ask questions about any code you do not understand ... and take a minute or so to look at each line ... it is logical

for information on error handling, watch this EE video:

video: basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

attached:
Activities_Paul_160924_5p__ACCDB.zip
Activities_Paul_160924_5p__ACCDB.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Although I don't disagree that most of the time you should use surrogate fields as the primary key, I didn't suggest that in this case since the database size would always be small and the database was for personal use so there would never be an advantage to the numeric autonumber ids.  And, if I had suggested the use of surrogate keys, I would have had to explain the use of and reason for creating unique indexes on all the natural keys.

If you are going with the surrogate keys, you need to understand that you still have to create unique indexes on the natural keys.  So even if you do not use the natural keys as the table's PK, you must create unique indexes (some simple, some compound) in order to enforce business rules.  For example, it makes no sense to allow duplicate ActivityNames and doing so will cause extreme confusion so you must create a unique index on ActivityName.  The autonumber primary key is useless when you actually have natural keys.
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
I'm really sorry for the delay!
I've been out of pocket for a couple weeks.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Thanks, did this work for you?
0
Paul WagnerFriend To Robots and RocksAuthor Commented:
Thank you for all of the help. I really appreciate it!
Yes, your solution/suggestions worked!
Now, I'll just work on making it look pretty. :-)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ please give points to everyone who helped, not just me.  When I close, since I am not a moderator, I don't get an option of how to distribute points -- only pick people who helped and the useful posts.

> "Now, I'll just work on making it look pretty. :-)"

you have a good eye for design so I'll bet you make it beautiful!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.