Table/Form/Query Question

[Erin]

I have a query that uses employee records to determine if an employee needs
to receive a warning or not.

I want the user to be able to see the results of the query, and check a box
once the warning has been issued.

Finally, I would like for these checks that the user enters to somehow
filter back to the query, so that the next time the query is run they would
see any new warnings that need to be issued PLUS warnings that have already
been issued.

What is the proper flow in order to get that done? I am really struggling
with whether or not I need a table feeding my form, and how to get the
information back to the query (I was initially thinking joining the table
created for the form to the query) without making any duplicate records or
circular references.

I hope that made sense... thanks for the help!!!!!

 

[Jeff Boyce]

Erin

It all starts with the data...

Are you keeping track of the warnings already delivered? It sounds like
you're using a query to find situations that could lead to a warning, but
how do you "know" that the warning was actually delivered?

If you use a form that lists 'warnings delivered' and lists 'impending
warnings', you might be able to get away with using paired listboxes to
"move" an impending warning over into "delivered" status.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Erin]

Yes, I am using the query to determine which employees need a warning and for
what.

How would you structure the form and table using this "two check box"
method? I am really struggling because the user needs to know who already got
a warning. However, the info will be updated via both the query and now these
check boxes, so I am stuck.

Maybe it is not possible???

 

[Jeff Boyce]

Erin

Depends on what you define as possible ...

If you require that the approach you've been using is the only way that it
can be done, it may be possible but require more work that you care to
spend...

If, by "possible", you mean a way to keep track of who has received a
warning already, and who is eligible to receive one, then that's much more
likely/possible.

Again, it all starts with the data... and I don't have a very clear picture
what your data structure looks like. Please post back a description of your
table structure. Here's an example (simplified) of a data structure for an
enrollment database:

tblStudent
StudentID
FName
LName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Please provide your table(s) in a similar format.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Erin]

Jeff-

I have a table, tblAttendanceRecords (which includes all the fields on a
form where the user enters attendance records).

tblAttendanceRecords:
EmployeeID
EmployeeName
Event Date
NoCallNoShow (check box)
Tardy (check box)
MissedPunch (check box)
Occurrence (check box)

Those are the fields that are relevant to my warnings.

I also have tables that outline what happens when the employee hits a
certain amount of warnings, they all are set up like this:

tblTardyWarnings:
CountWarnings
WarningMessages

The query took me three steps to build, but it includes all the counts of
the different kinds of warnings, and if the employee needs to receive a
warning. It is LIMITED by the input of a small form, which asks the user to
put it an ending date for the time period they want calculated. This is
important because warnings are calculated on a rolling 12-month basis.

The final query, the one that will be in my report and have the format that
I would like to have the user check a box when the warning has been issued,
has these fields:

EmployeeID
EmployeeName
SumOfOccurrenceValue
SumOfTardyCount
SumOfNoCallNoShow
SumOfMissedPunches
OccurrenceWarning
TardyWarnings
MissedPunchesWarnings

I know this is probably overly complicated, but it is the only way I knew
how to organize it all in my mind. Any suggestions?!?

THANKS FOR THE HELP!!!!!

Erin

 

[Jeff Boyce]

see comments in-line below

Jeff-

I have a table, tblAttendanceRecords (which includes all the fields on a
form where the user enters attendance records).

tblAttendanceRecords:
EmployeeID
EmployeeName
Event Date
NoCallNoShow (check box)
Tardy (check box)
MissedPunch (check box)
Occurrence (check box)

A couple of observations ... first, by putting BOTH EmployeeID AND
EmployeeName in each row/record, you are unnecessarily duplicating
information, and increasing your risk of having data out of sync. Next, by
having multiple checkbox fields, you've told Access that any (OR ALL!) of
those conditions could be true simultaneously. I may not understand your
situation well enough yet, but it seems to me that a person would not be
BOTH Tardy and NoCallNoShow.

This table appears to be set up more like a spreadsheet than a relational
database table.

Those are the fields that are relevant to my warnings.

You may have attempted to create a table designed to fill a report -- this
is backwards in Access! First you design a well-normalized relational
database structure, then use queries to join the tables and collect the data
you want to display in a report.

I also have tables that outline what happens when the employee hits a
certain amount of warnings, they all are set up like this:

tblTardyWarnings:
CountWarnings
WarningMessages

The query took me three steps to build, but it includes all the counts of
the different kinds of warnings, and if the employee needs to receive a
warning. It is LIMITED by the input of a small form, which asks the user
to
put it an ending date for the time period they want calculated. This is
important because warnings are calculated on a rolling 12-month basis.

My suggestion would be to stop trying to "drive nails with a chainsaw". It
seems like you are trying to make Access do something with data that it
isn't designed to do. If this is the case, the 'how' you are using is just
going to frustrate both you and Access.

I'd recommend stepping back and revisiting your data structure. If the
terms "normalization" and "relational database design" are unfamiliar, plan
on learning your way up this curve before attempting to get Access to handle
the data as you now have it structured.

Regards

Jeff Boyce
Microsoft Office/Access MVP