Solved

Access Database To Track Coworker Daily Fitness Accomplishments

Posted on 2016-09-23
32
56 Views
Last Modified: 2016-10-12
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. :-)
0
Comment
Question by:Paul Wagner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 14
  • 2
32 Comments
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
ID: 41813281
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
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813297
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
 
LVL 20
ID: 41813302
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813313
@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
 
LVL 20
ID: 41813319
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
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813327
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
 
LVL 20
ID: 41813330
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
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813332
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
 
LVL 20
ID: 41813333
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
 
LVL 20
ID: 41813334
PplActivityID should be an AutoNumber and the Primary Key
0
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813336
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
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813337
PplActivityID should be an AutoNumber and the Primary Key
Done
pplactivityid
0
 
LVL 20
ID: 41813343
you should have something like this:
table designs for tracking activitiesrelationships for tracking activities
0
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813350
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
 
LVL 20
ID: 41813352
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
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813361
This is probably wrong
form viewform view
0
 
LVL 20
ID: 41813368
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
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813374
row source
design2
view 2
0
 
LVL 20
ID: 41813376
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
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813380
Build... is greyed out
build
0
 
LVL 20
ID: 41813382
RowSourceType should be Table/Query not Value List
0
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813398
I'll have to continue tomorrow. Need to get some sleep.

Thank you for your help thus far!!!
0
 
LVL 20
ID: 41813401
you're welcome.  If I am not around, hopefully Pat will jump in ... Farmer's market is in the morning :)
0
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41814111
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
 
LVL 20
ID: 41814122
looking now .. and changing things
0
 
LVL 20

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 450 total points
ID: 41814144
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 41814252
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
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41840681
I'm really sorry for the delay!
I've been out of pocket for a couple weeks.
0
 
LVL 20
ID: 41840686
Thanks, did this work for you?
0
 
LVL 5

Author Closing Comment

by:Paul Wagner
ID: 41840688
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
 
LVL 20
ID: 41840697
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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question