update query not updating when it should

  • Thread starter Thread starter AngiW
  • Start date Start date
A

AngiW

Ok...I've been working on this for 2 days...can't figure it out. Any
suggestions would be helpful! I have two tables...Employees and Accruals.
Primary keys are EmployeeID for employee table and PageID for accrual table.
Using employeeID as the common key to accrual table. Ok...my relationship is
one to many. I've also done a junction table, but that didn't work either, so
now i'm back to this.

Here's my query:
UPDATE [Employee Main] INNER JOIN [Accrual Main] ON [Employee Main].[Employee
ID] = [Accrual Main].employeeID SET [Accrual Main].SickAvail = 5
WHERE ((([Employee Main]![Status])="Full time"));


The funny thing is...it will update 98 records, which is right, but it doesn't
"attach" those records with the employees. I see where the problem is...there
aren't any records in Accrual main until it updates so the employeeID's are
never equal. But how do i fix that? It's driving me crazy thinking it's
something simple!

THANKS!!!!
 
The problem is that you're trying to run an update query to a table with no
records, but what you need to run is an append query (the latter query will
add records to your table). Try this:

INSERT INTO [Accrual Main] ( employeeID, SickAvail )
SELECT E.].[Employee ID], 5
FROM [Employee Main] AS E
WHERE E.Status="Full time";
 
Ken,
Thank you so much! I knew it was something easy! It works great, but there's
still one problem. I need it to ADD 5 to sickavail when full time. I tried
editing it, but it didn't work (of course). Now is where my update comes in, i
think, but it's still not working.

Here's what i have now for the append:
INSERT INTO [Accrual Main] ( employeeID, SickAvail )
SELECT [Employee main].[Employee ID], 5 AS Expr1
FROM [Employee Main]
WHERE ((([Employee main].Status)="Full time"));

Here was my update one:
UPDATE [Employee Main] INNER JOIN [Accrual Main] ON [Employee Main].[Employee
ID] = [Accrual Main].employeeID SET [Accrual Main].SickAvail = +5
WHERE ((([Employee Main]![Status])="Full time"));

Says it's going to update 98 records, but nothing changes. What am i still
doing wrong??

Again, thanks so much!
 
You need to have the [Accrual Main].SickAvail field on both sides of the
expression -- you're adding 5 to what is already there, so you must add 5 to
that value.

UPDATE [Employee Main] INNER JOIN [Accrual Main] ON [Employee
Main].[Employee
ID] = [Accrual Main].employeeID SET [Accrual Main].SickAvail = [Accrual
Main].SickAvail +5
WHERE ((([Employee Main]![Status])="Full time"));
 
Back
Top