Trying to Join specific records...

[mace9984 via AccessMonster.com]

Hey guys!

First off, thanks for you help, I've been a long time reader of Accessmonster.
com and I've learned so much from you guys. I've ran into a problem that I
can't seem to find a resolution for. I'll try to explain it clearly below.


there is a Table called tbl_Occurrence. It list all occurrences that
employees have, each occurrence is on a seperate record. The fields of this
table are...

Emp_ID (employee ID), Date_Of_Occurrence(Date occurrence happened), Points
(Points earned by occurrence) and Coach_Memo(Memo left by employees manager).

Due to constantly shifting schedules I often have to rerun this, So, I save
all coach memos into a temp table (the coach memo's can't be retrieved from
data sources, they are only entered once by the coaches). I then delete the
records from the occurrence table, and rerun it all. So then I have all the
fields repopulated except for the coach memo. So what I'm trying to do is
If the Emp_ID and Date_Of_Occurrence are the same in both tables, I want to
append the coach memo to that specific record. I could do this by looping
through the recordset, but there are on average 12,000 records, and 400
employees I'd have to loop through it for. This seems horribly inefficent.
I've tried using an append query, but it just adds new records, instead of
adding it to the already existing (matching) record... any help would be
sooooo appreciated!!

 

[mace9984 via AccessMonster.com]

Edit...

So what I'm trying to do is

If the Emp_ID and Date_Of_Occurrence are the same in both tables

should be

So what I'm trying to do is

 

[John Spencer]

First, you want an UPDATE query not an APPEND query. Assuming two tables
named tbl_occurrence and tbl_temp (coach memos), then I would think that you
could use something like the following.

UPDATE tbl_Occurrence INNER JOIN tbl_Temp
ON tbl_Occurrence.Emp_ID = tbl_temp.EmpID
AND tbl_Occurrence.Date_of_Occurrence = tbl_Temp.Date_Of_Occurrence
SET tbl_Occurrence.Coach_Memo = [tbl_Temp].[Coach_Memo]
WHERE tbl_Temp.Coach_Memo is Not Null

The real problem here is why do you need to delete all the records in
tbl_Occurrence in the first place. It seems like it would be easier to just
fix the data in tbl_Occurrence instead of deleting all the records and then
somehow rebuilding the table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[mace9984 via AccessMonster.com]

Thanks! I'll give that a shot. The reason I have to delete the records is
because the underlying data (that the data in the table is generated from)
can change quite often. This would make some of the data in the table
incorrect. (don't blame me, corporate enviroment ) The coach memo's will
always apply to the specific record however, so they need to be saved. When
i rerun the data, it's only for those records that need changed (specifically
they are in a given time frame), so those are the only ones that get deleted,
and updated. I would of probably done it a bit different, but I have bosses
who aren't database friendly

First, you want an UPDATE query not an APPEND query. Assuming two tables
named tbl_occurrence and tbl_temp (coach memos), then I would think that you
could use something like the following.

UPDATE tbl_Occurrence INNER JOIN tbl_Temp
ON tbl_Occurrence.Emp_ID = tbl_temp.EmpID
AND tbl_Occurrence.Date_of_Occurrence = tbl_Temp.Date_Of_Occurrence
SET tbl_Occurrence.Coach_Memo = [tbl_Temp].[Coach_Memo]
WHERE tbl_Temp.Coach_Memo is Not Null

The real problem here is why do you need to delete all the records in
tbl_Occurrence in the first place. It seems like it would be easier to just
fix the data in tbl_Occurrence instead of deleting all the records and then
somehow rebuilding the table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hey guys!

[quoted text clipped - 21 lines]

adding it to the already existing (matching) record... any help would be
sooooo appreciated!!

 

[John W. Vinson]

Thanks! I'll give that a shot. The reason I have to delete the records is
because the underlying data (that the data in the table is generated from)
can change quite often. This would make some of the data in the table
incorrect. (don't blame me, corporate enviroment ) The coach memo's will
always apply to the specific record however, so they need to be saved. When
i rerun the data, it's only for those records that need changed (specifically
they are in a given time frame), so those are the only ones that get deleted,
and updated. I would of probably done it a bit different, but I have bosses
who aren't database friendly

Can you perhaps split the table into two one-to-one related tables - one for
the constant data, the other for those fields that are variable?