Re: Update and Select Query results don't agree

Tech-Archive recommends: Fix windows errors by optimizing your registry

Some of the records are below. by the way, the select query selects these
records just fine.

Clock# FirstName LastName MI Hourly Probationary YTD_Absence Monthly_Accruement_Rate Previous_Vacation_Balance Current_Vacation_Hours Seniority_Date Benefit_Date Department Skip_Accrual Union_ Default_Hours_Worked Class Employment_Length Manual_Vacation_Amount Additional_Hours_Added Normal_Shift Opportunities_Granted Opportunities Supervisor RemoveFlag upsize_ts
4074 ROGER WAGENIUS P -1 0 4 13.333 67.022 80.355 2/16/1981 2/16/1981 A326 0 0 10 30 297 0 27 3A 0 0 4256 0
4179 DAN JOHNSON E -1 0 0 13.333 168.746 182.079 4/9/1984 4/9/1984 A311 0 0 10 30 260 0 0 3A 7 7 4256 0
4367 JIM CHUTE A -1 0 6.5 13.333 66.732 80.065 5/16/1988 5/16/1988 A326 0 0 10 30 210 0 8 3A 0 0 4256 0
4370 DARYL ANDERSON L -1 0 1.5 13.333 84.666 97.999 5/23/1988 5/23/1988 A310 0 0 10 10 210 0 0 3A 0 0 4256 0
4372 TIM MCKELLAR K -1 0 1 6.667 34.563 41.23 7/22/2002 7/22/2002 A311 0 0 10 40 40 0 0 3A 8 8 4256 0
1758 TEDDY HAGEMAN C -1 0 3.5 16.667 86.8760000000001 103.543 9/27/1973 9/27/1973 A326 0 0 10 30 386 0 0 3A 7 7 4256 0
1832 WAYNE SULLIVAN T -1 0 3 16.667 91.145 107.812 1/19/1976 1/19/1976 A326 0 0 10 10 358 0 0 3A 5 5 4256 0
4400 MIKE LINDUS J -1 0 3 13.333 97.041 110.374 10/23/1989 6/5/1989 A326 0 0 10 30 198 0 0 3A 3 3 4256 0
4587 TERRY LARSON A -1 0 0 10 102.009 112.009 10/10/1994 10/10/1994 A311 0 0 10 40 134 0 0 3A 1 1 4256 0
4609 RODNEY ERICKSON C -1 0 3.5 10 50.342 60.342 11/7/1994 11/7/1994 A314 0 0 10 30 133 0 0 3A 3 3 4256 0
4611 ROBBI KOSTER K -1 0 4.5 10 0.342 0.341999999999999 11/14/1994 11/14/1994 A326 -1 0 10 70 133 0 16 3A 9 9 4256 0
4728 MICHAEL TAYLOR D -1 0 0 10 56.329 66.329 5/12/1997 5/12/1997 A314 0 0 10 30 103 0 0 3A 2 2 4256 0
4734 SCOTT MARTINI A -1 0 1 10 85.745 95.745 6/2/1997 6/2/1997 A326 0 0 10 40 102 0 0 3A 8 8 4256 0
4754 DALE ROWELL L -1 0 0.5 10 62.329 72.329 10/13/1997 10/13/1997 A314 0 0 10 30 98 0 0 3A 2 2 4256 0
4755 JAMES FRISBEE S -1 0 0 10 64.497 74.497 10/14/1997 10/14/1997 A310 0 0 10 50 98 0 0 3A 8 8 4256 0
4784 BARB WUOLLET A -1 0 4 10 58.663 68.663 2/2/1998 2/2/1998 A326 0 0 10 70 94 0 0 3A 0 0 4256 0
4788 TOM HOLMQUIST A -1 0 2.5 10 53.663 63.663 2/6/1998 2/6/1998 A314 0 0 10 40 94 0 0 3A 2 2 4256 0
4789 MARGO PETERSON R -1 0 6 10 50.331 60.331 2/13/1998 2/13/1998 A310 0 0 10 50 94 0 0 3A 0 0 4256 0
4794 BRENDA JOHNSON L -1 0 6.5 10 58.335 68.335 2/27/1998 2/27/1998 A326 0 0 10 50 93 0 13 3A 0 0 4256 0
4871 CARSON CHRISTIAN -1 0 0.5 6.667 68.445 75.112 6/7/2004 6/7/2004 A326 0 0 10 50 18 0 0 3A 2 2 4256 0

"[MVP] S.Clark" wrote:

> Post an example of one of the records that did not get updated??
>
> "tkosel" <tkosel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:AFAE07C0-7686-4BBB-A613-C9624C41B4F6@xxxxxxxxxxxxxxxx
> > If I let it update the 52 records, they update. When I look at the
> > records
> > that didn't get updated but should have, there appears to be absolutely no
> > reason why they didn't get updated.
> >
> > I go back to my original statement. If a select query selects all the
> > desired records then why doesn't the same query criteria perform the
> > update?
> > All I do is to change the query type from Select to Update. SQL for the
> > Query is exactly the same except for the word SEKECT instead of UPDATE.
> > Select Yields 100 records, update only wants to update 52. Normal shift
> > of 2
> > and 3 gets updated, 2A, 2B, 3A and 3B don't get updated, but they do get
> > selected. What gives? Doesn't make any sense to me at all.
> >
> >
> > SELECT Employees.Opportunities
> > FROM Employees
> > WHERE (((Employees.Normal_Shift)="2" Or (Employees.Normal_Shift)="3") AND
> > ((Employees.Employment_Length)>12)) OR (((Employees.Normal_Shift) Like
> > "2%")
> > AND ((Employees.Employment_Length)>12)) OR (((Employees.Normal_Shift) Like
> > "3%") AND ((Employees.Employment_Length)>12));
> >
> > UPDATE Employees SET Employees.Opportunities = 9
> > WHERE (((Employees.Normal_Shift)="2" Or (Employees.Normal_Shift)="3") AND
> > ((Employees.Employment_Length)>12)) OR (((Employees.Normal_Shift) Like
> > "2%")
> > AND ((Employees.Employment_Length)>12)) OR (((Employees.Normal_Shift) Like
> > "3%") AND ((Employees.Employment_Length)>12));
> >
> >
> > "[MVP] S.Clark" wrote:
> >
> >> Let it perform the update, then analyze the records that didn't get
> >> updated.
> >> See why they fail to meet the criteria.
> >>
> >> --
> >> Steve Clark, Access MVP
> >> http://www.fmsinc.com/consulting
> >> *FREE* Access Tips: http://www.fmsinc.com/free/tips.html
> >>
> >> "tkosel" <tkosel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:9DDD4ECF-32F5-403C-BBC0-5F0910E10562@xxxxxxxxxxxxxxxx
> >> >I have an update query the SQL appears below.
> >> >
> >> > UPDATE Employees SET Employees.Opportunities = 9
> >> > WHERE (((Employees.Employment_Length)>12) AND
> >> > ((Employees.Normal_Shift)="2"
> >> > Or (Employees.Normal_Shift)="3")) OR
> >> > (((Employees.Employment_Length)>12)
> >> > AND
> >> > ((Employees.Normal_Shift) Like "2%")) OR
> >> > (((Employees.Employment_Length)>12)
> >> > AND ((Employees.Normal_Shift) Like "3%"));
> >> >
> >> >
> >> > It wants to update 52 records. When I change it to a select query, It
> >> > selects 100 records which I believe is correct and I want it to do.
> >> > What
> >> > am
> >> > I missing?
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: Query with Form as a parameter
    ... You might try opening your form in design view and then use the build feature ... when I built my query. ... However, when I call the form from the query criteria, it is ...
    (microsoft.public.access.queries)
  • Error trying to use form control value in query criteria
    ... I have been using form control values in my query criteria forever. ... If I hard code the actual value in the query it runs fine. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Requery subform
    ... reference the ProjectType on the main form in its query criteria? ... requries the subform, which is based on this query. ...
    (comp.databases.ms-access)
  • Re: Query with Form as a parameter
    ... check your spelling on the name of the control. ... in the query, Access will display an input parameter box since it cannot ... YourQueryName and keep the quote marks). ... However, when I call the form from the query criteria, it is ...
    (microsoft.public.access.queries)
  • Re: Academic Year date criteria in a query
    ... > of the following DLookup ... > value and DLookup plus one) and the query will not need to ... > How could I build a query criteria to do this? ...
    (microsoft.public.access.queries)