Prev: code still unaible to access my portfolio with code 629YP175n
Next: je ne parle pas nie conprend l,engais
From: patientgrow on 3 Feb 2010 12:16
I have a table with 100 records. One of the fields on the table is
EmployeeName. I want to update 75 records to have EmployeeName "Robert" and
the other 25 to have EmployeeName "Gina". Is there a way to do this?

Thanks!
From: Jerry Whittle on 3 Feb 2010 12:56
If it doesn't matter which records, first limit the returns to 75 records.
The SELECT TOP 75 can do this. You want to use a unique field, such as the
primary key, or you may get extra records if there are duplicates for the
75th record.

Then using that query as the subquery. Joining on the primary key would be
best. It would look something like:

UPDATE ASIF2
SET ASIF2.EmployeeName = "Robert"
WHERE ASIF2.ID IN (SELECT TOP 75 ASIF2.ID
FROM ASIF2
ORDER BY ASIF2.ID);

Change ASIF2 to your table name. Same goes for the ID field name.

Assuming that there are only 100 records, you could do another update query
where EmployeeName <> "Robert" is the criteria.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"patientgrow" wrote:

> I have a table with 100 records. One of the fields on the table is
> EmployeeName. I want to update 75 records to have EmployeeName "Robert" and
> the other 25 to have EmployeeName "Gina". Is there a way to do this?
>
> Thanks!
From: alex on 3 Feb 2010 12:58
On Feb 3, 12:16 pm, patientgrow
<patientg...(a)discussions.microsoft.com> wrote:
> I have a table with 100 records.  One of the fields on the table is
> EmployeeName.  I want to update 75 records to have EmployeeName "Robert" and
> the other 25 to have EmployeeName "Gina".  Is there a way to do this?
>
> Thanks!

You can easily do this with an update query...but you need to
distinguish between the 25/75 records; i.e., what records receive
Robert, what records receive Gina.

It also sounds like you have duplicate data in your db. You may want
to look up normalization!

alex
From: patientgrow on 3 Feb 2010 13:25
That works great! Thank you.

"Jerry Whittle" wrote:

> If it doesn't matter which records, first limit the returns to 75 records.
> The SELECT TOP 75 can do this. You want to use a unique field, such as the
> primary key, or you may get extra records if there are duplicates for the
> 75th record.
>
> Then using that query as the subquery. Joining on the primary key would be
> best. It would look something like:
>
> UPDATE ASIF2
> SET ASIF2.EmployeeName = "Robert"
> WHERE ASIF2.ID IN (SELECT TOP 75 ASIF2.ID
> FROM ASIF2
> ORDER BY ASIF2.ID);
>
> Change ASIF2 to your table name. Same goes for the ID field name.
>
> Assuming that there are only 100 records, you could do another update query
> where EmployeeName <> "Robert" is the criteria.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "patientgrow" wrote:
>
> > I have a table with 100 records. One of the fields on the table is
> > EmployeeName. I want to update 75 records to have EmployeeName "Robert" and
> > the other 25 to have EmployeeName "Gina". Is there a way to do this?
> >
> > Thanks!
 | 
Pages: 1
Prev: code still unaible to access my portfolio with code 629YP175n
Next: je ne parle pas nie conprend l,engais