Solved

Access Database To Track Coworker Daily Fitness Accomplishments

Posted on 2016-09-23
32
42 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
  • 15
  • 14
  • 2
32 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
Comment Utility
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 3

Author Comment

by:Paul Wagner
Comment Utility
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 18
Comment Utility
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
 
LVL 3

Author Comment

by:Paul Wagner
Comment Utility
@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 18
Comment Utility
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 3

Author Comment

by:Paul Wagner
Comment Utility
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 18
Comment Utility
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 3

Author Comment

by:Paul Wagner
Comment Utility
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 18
Comment Utility
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 18
Comment Utility
PplActivityID should be an AutoNumber and the Primary Key
0
 
LVL 3

Author Comment

by:Paul Wagner
Comment Utility
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 3

Author Comment

by:Paul Wagner
Comment Utility
PplActivityID should be an AutoNumber and the Primary Key
Done
pplactivityid
0
 
LVL 18
Comment Utility
you should have something like this:
table designs for tracking activitiesrelationships for tracking activities
0
 
LVL 3

Author Comment

by:Paul Wagner
Comment Utility
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 18
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Author Comment

by:Paul Wagner
Comment Utility
This is probably wrong
form viewform view
0
 
LVL 18
Comment Utility
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 3

Author Comment

by:Paul Wagner
Comment Utility
row source
design2
view 2
0
 
LVL 18
Comment Utility
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 3

Author Comment

by:Paul Wagner
Comment Utility
Build... is greyed out
build
0
 
LVL 18
Comment Utility
RowSourceType should be Table/Query not Value List
0
 
LVL 3

Author Comment

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

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

Author Comment

by:Paul Wagner
Comment Utility
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 18
Comment Utility
looking now .. and changing things
0
 
LVL 18

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 450 total points
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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 3

Author Comment

by:Paul Wagner
Comment Utility
I'm really sorry for the delay!
I've been out of pocket for a couple weeks.
0
 
LVL 18
Comment Utility
Thanks, did this work for you?
0
 
LVL 3

Author Closing Comment

by:Paul Wagner
Comment Utility
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 18
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now