Form to show latest Record

  • Thread starter BYoung via AccessMonster.com
  • Start date
B

BYoung via AccessMonster.com

I am developing a training database that keeps track of when an employee
completes different training course. I have 2 tables for the records.
tblTrainingTopic
idsTrainingTopicID
chrTrainingTopic
blnTrainingRequired

tblTrainingRecords
idsTrainingRecordID
chrEmpID (FK to Employee Information Table)
chrTrainingTopic (lookup list from tblTrainingTopic)
dtmTraining Date
chrTrainingFrequency (annual, new hire, accident)
memNotes

I have a tabular form that shows all training completed by an employee and
allows you to add new training records, but I would also like to create a
form that shows all the training topics in tblTrainingTopic and the latest
record for that particular training topic for whichever employee is being
viewed by the form. I want the training topic to be visible regardless of if
there is a record tied to that employee and training topic or not. Each
employee could have several records for the same course because many are
taken annually, but I want this form to only view the latest completion date.
Then I plan on adding a " View All Training" button that will open the
tabular form mentioned above.

Is there a way to create the form that I'm asking about?
 
J

Jeff Boyce

Why is your TrainingRecords table recording the character TrainingTopic,
rather than the idsTrainingTopicID? You are using an EmployeeID,
apparently...

When you say "the latest", do you mean the most recent dtmTrainingDate per
Topic, per Employee? If so, you could use a Totals query to GroupBy Topic
and Employee, and get the Max TrainingDate.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
B

BYoung via AccessMonster.com

Jeff, Thank you for your reply. I'm new to access so I'm trying to learn as
much as possible. I get what you're saying about storing the topicID opposed
to the whole name. I changed the database to store idsTrainingTopicID
instead of the name, but how do I get my combo boxes to display the training
name but store the only the ID?

I figured out the total query which shows the most recent dtmTrainingDate per
topic, per employee. Now I want to create a form that shows ALL the training
topics, and the most recent training date for each of those topics if there
is one, however if the employee is missing a training topic then the form
will show blank on that topic. Am I being clear enough. My thought was to
create a form with a text box for each topic. Then somehow bind them to the
total query. Am I headed in the right direction? How would I do this in the
most efficient manner?

Jeff said:
Why is your TrainingRecords table recording the character TrainingTopic,
rather than the idsTrainingTopicID? You are using an EmployeeID,
apparently...

When you say "the latest", do you mean the most recent dtmTrainingDate per
Topic, per Employee? If so, you could use a Totals query to GroupBy Topic
and Employee, and get the Max TrainingDate.

Regards

Jeff Boyce
Microsoft Office/Access MVP
I am developing a training database that keeps track of when an employee
completes different training course. I have 2 tables for the records.
[quoted text clipped - 25 lines]
Is there a way to create the form that I'm asking about?
 
J

Jeff Boyce

See comments in-line below...


BYoung via AccessMonster.com said:
Jeff, Thank you for your reply. I'm new to access so I'm trying to learn
as
much as possible. I get what you're saying about storing the topicID
opposed
to the whole name. I changed the database to store idsTrainingTopicID
instead of the name, but how do I get my combo boxes to display the
training
name but store the only the ID?

A common use of combo boxes in forms (you ARE working in forms, right?!) is
to put the ID in the first column and the "to-be-displayed" value in the
second column of the combo box's row source. Then set the width of the
first column to 0 and the second column to whatever you need. Make the
first column the "bound" column.

This setup stores the key and displays the second column.
I figured out the total query which shows the most recent dtmTrainingDate
per
topic, per employee. Now I want to create a form that shows ALL the
training
topics, and the most recent training date for each of those topics if
there
is one, however if the employee is missing a training topic then the form
will show blank on that topic. Am I being clear enough. My thought was
to
create a form with a text box for each topic. Then somehow bind them to
the
total query. Am I headed in the right direction? How would I do this in
the
most efficient manner?

For a given employee, you want to see all training topics, and any of those
that have a most-recent-date? I'd probably do this by first determining,
per employee, per training topic, the "most-recent-date" ... and it sounds
like you were able to do this.

Then I'd create a new query that joins the training topic table to the
"employee's-most-recent-date" query, using a "directional" join (my term ...
open the query in design mode, join the two on the trainingtopicID, then
modify the properties of the join to show all topics and ANY matching from
the employee-most-recent query). This will leave blanks where there is no
"most recent" for the employee.

You'll still need a way to make the form work for a specific employee. Add
an unbound combo box to select the employee to the form, and in your second
query, "point" to the combo box for a selection criterion, something like:

Forms!YourFormName!YourEmployeeComboBox

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
B

BYoung via AccessMonster.com

Jeff, Thank you for the tip on the combo box, I had one of those Duh moments
because I did that exact thing on another form in my database...Go figure.
As far as the query goes. I was able to create the query that you suggested,
I think. And I used your tip about the directional join (given my limited
knowledge of access I would have never figured that out). So the output of
the query shows me all the training topics, and if an employee has completed
it their Employee ID is "Logged" with the latest date. Thats all good.

I tried creating a form from the query, which looks good except it shows the
Employee ID and last complete date for all who have completed that training.
So I inserted the "frmLastTrainingDate" into my main form using the employee
ID as the link and once again it shows only the training topics completed.
What Am I doing wrong here?

Thank you for your patience.

Jeff said:
See comments in-line below...
Jeff, Thank you for your reply. I'm new to access so I'm trying to learn
as
[quoted text clipped - 4 lines]
training
name but store the only the ID?

A common use of combo boxes in forms (you ARE working in forms, right?!) is
to put the ID in the first column and the "to-be-displayed" value in the
second column of the combo box's row source. Then set the width of the
first column to 0 and the second column to whatever you need. Make the
first column the "bound" column.

This setup stores the key and displays the second column.
I figured out the total query which shows the most recent dtmTrainingDate
per
[quoted text clipped - 10 lines]
the
most efficient manner?

For a given employee, you want to see all training topics, and any of those
that have a most-recent-date? I'd probably do this by first determining,
per employee, per training topic, the "most-recent-date" ... and it sounds
like you were able to do this.

Then I'd create a new query that joins the training topic table to the
"employee's-most-recent-date" query, using a "directional" join (my term ...
open the query in design mode, join the two on the trainingtopicID, then
modify the properties of the join to show all topics and ANY matching from
the employee-most-recent query). This will leave blanks where there is no
"most recent" for the employee.

You'll still need a way to make the form work for a specific employee. Add
an unbound combo box to select the employee to the form, and in your second
query, "point" to the combo box for a selection criterion, something like:

Forms!YourFormName!YourEmployeeComboBox

Regards

Jeff Boyce
Microsoft Office/Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top