Trying to link fields in Access 2007

[JPWolfe]

I have a basic list of phone numbers that I check for functioning properly,
and when I check them, I check a box in my table and create a report. There
are two things that I am trying to accomplish but can't figure out. (I'm a
basic user.)

1) When I check off that the phone was checked, I want another field in the
table to automatically post the date.

2) Once I get that set, I want to archive the dates that the phones were
checked on.

The simplest possible way of doing this would be greatly appreciated.

 

[S.Clark]

Do you need to historically retain all of the Phone Checks, or just the last
check date? If the phone is checked and is not working, what is the state of
the checkbox(Checked, not checked, null)?

Neither way is 'easy' unless you know a little about Access automation (VBA
and/or Macros), but give more specs and we'll work up some code for you.

 

[KARL DEWEY]

A couple of recommendations.
Just use a datetime field, no checkbox.
Use a form, not enter data in the table.

Either add another field for active (Yes/No) or using two table, one for
telephone information and user, and the second for check history.

On close of form run an append query to create new check records for all
active phones that do not have a check record with CheckDate null.

Use a query for form to show all records with CheckDate null and last
CheckDate older than your check period.

 

[JPWolfe]

Ok, sorry for taking so long, but here are some details.

I have a long list of phones, about 280, in a table. On certain nights, I
check the phones, and the lights that accompany them, if there is one with
the phone.

I have it set up currently so that I put a check mark in a column labeled
"Checked?" (Yes or No) if it was checked, or blank if it was not.

I also want to catalog if the connection was working, and if the light was
working later on, but I think once I get this figured out, I can get those
pretty easily. (A check mark for these would mean they are not working, blank
is working.)

At the end of the night, I have a report, that automatically filters all
those that were checked, and then I can send the report to my boss to contact
the appropriate people to fix them.

And I have absolutely no experience with Access except for this new
assignment I have, I took one C++ class, but that was about 2 years ago. So
Macros and code are mostly a foreign language to me.


Thanks for all your help!

 

[John W. Vinson]

I have a basic list of phone numbers that I check for functioning properly,
and when I check them, I check a box in my table and create a report. There
are two things that I am trying to accomplish but can't figure out. (I'm a
basic user.)

1) When I check off that the phone was checked, I want another field in the
table to automatically post the date.

2) Once I get that set, I want to archive the dates that the phones were
checked on.

The simplest possible way of doing this would be greatly appreciated.

I would suggest two tables: a table of Phones and a table of Checks, since
each phone would be checked multiple times, and you want to record when.

The Phones table would probably have a Text field for the phone number as its
primary key. Some would recommend an autonumber, either way will work. You
might want additional fields to identify or describe the phone (where it is,
whose it is, etc.) if appropriate.

A second table would record each instance of checking the phone. It should
have an Autonumber field as its primary key, and a field to link to the phones
table - either a text field of the same size to hold the phone number, or a
Long Integer if you use an Autonumber. The table should have a DateTime field
(named WhenChecked say) with a Default Value property of Now() if you want the
computer to record when you enter a record as the time checked; leave it
without a default if you'll be checking the phone at one time and entering the
data later, in which case you'll have to type it in manually. Add any
additional fields having to do with checking the phone (a text or Memo field
to record any notes or comments, for example).

You'ld use a Form based on the Phones table with a Subform based on Checks,
using the master/child link fields to connect the phone to the checks for that
phone.

You'ld be able to create a Report by using a Query joining the two tables,
most likely with a criterion on the WhenChecked field to display all of the
checks that occurred in a desired time span.