Add specific data to existing record

[Guest]

If there is an empty field in a record say a record that contains the start
and end times for a job and the end time field is empty, how can the end time
data be added to that field. The user signs on to the job and his information
becomes a record in the track time table. The end time field is empty. The
user signs off from that job. We want to put the sign of time in the same
record within the track time table with the sign on time. We tried using an
update query but we get two records for each user, one record has the sign on
time and the other has the sign off time.

 

[tina]

sounds like you were using an Append query to enter the "end time", rather
than an Update query.

write a Select query that pulls the correct record; make sure the query
includes the "end time" field. then, in design view, change the query to an
Update query (from the menu bar, click Query | Update Query). in the "end
time" column, on the Update To line, put your end time (Now(), or a
reference to a control on a form, or wherever you're getting the "end time"
value).

hth

 

[Guest]

I have another questin that deals with this. That worked BTW, except the
update query changed the end times for all the records in the table. In
trying to correct this behivor I broke something. NP though but how can I
make certain the the update query only applies the update to the correct
record and not change the end time in all records.

Thanx

 

[Guest]

I think I figured it out about the update query.

I have another questin that deals with this. That worked BTW, except the
update query changed the end times for all the records in the table. In
trying to correct this behivor I broke something. NP though but how can I
make certain the the update query only applies the update to the correct
record and not change the end time in all records.

Thanx

 

[Guest]

Um, forget that it didn't work, the update query is still changing the end
time for all records. I tried to get it to write to just the one record but
then it leaves of other data.

 

[tina]

first, create a Select query to pull the specific record you need, as i said
before. you'll need to use criteria, obviously, but i can't direct you in
that because i don't know how you're identifying the record. in the Select
query, only include the "end time" field and whatever fields you need to set
criteria on.

*test the Select query.* once it's pulling the correct record, and only that
record, then change it an Update query. *do not* put a value in the Update
To line of any column EXCEPT the "end time" field.

hth

 

[Guest]

I've done that and it works until time to update the record. It updates the
table globally rather than just the one record. I had to also pull an id
number for the record in question to insure that only the one record gets
changed. The only problem with that is the user is prompted twice for their
number rather than once as when they start the work. Each record is based on
information about a piece of work. When it is started and when it is
finished. It is when the job is finished that the update query needs to
update the end time field in the table.

 

[tina]

if you created a Select query that accurately pulls only one specific
record, and changed that query to an Update query *without changing anything
else in the query*, then there's no way that query can be updating more than
that one single record. it's just not possible, AFAIK. sorry, hon, but i
just don't know how else to instruct you so that you'll understand how to do
it.

 

[Guest]

I have it working now thanks for your help.

first, create a Select query to pull the specific record you need, as i said
before. you'll need to use criteria, obviously, but i can't direct you in
that because i don't know how you're identifying the record. in the Select
query, only include the "end time" field and whatever fields you need to set
criteria on.

*test the Select query.* once it's pulling the correct record, and only that
record, then change it an Update query. *do not* put a value in the Update
To line of any column EXCEPT the "end time" field.

hth

 

[Guest]

The update query needed to see one more field of the record to know which
record needs updating. Without this it updated all records. I have to prompt
for the piece of work. It was clumsey at first because I was trying to to
call only the one field from the record. Not sure this makes since but I got
it to work finally.