INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with update query

Help with update query

(OP)
Hello Everyone,
I've built a simple select query that filters the data on a certain criteria:
SQL code is listed below:
SELECT ContractsDataSet.[Company Name], ContractsDataSet.StreetAddress1, ContractsDataSet.Contact, ContractsDataSet.CompanyID
FROM ContractsDataSet
WHERE (((ContractsDataSet.[Company Name]) Like "BLUESKY*") AND ((ContractsDataSet.StreetAddress1) Not Like "11 Abc Rd*")) OR (((ContractsDataSet.[Company Name]) Like "SUNSHINE*"));

I get a list of companies that I am looking for. Now, I was trying to take the very first record in this list, specifically CompanyID and update the CompanyID for the remaining companies in the list to match the first record.
Company Name StreetAddress1 Contact CompanyID
BLUESKY 500 Bell Street Tom Xxx 45
SUNSHINE 300 Central Ave Amy Yyy 38
BLUESKY 400 Saks Ave Jon Zzz 39
SUNSHINEONE 300 Central Ave Amy Yyy 38
Desired result:
Company Name StreetAddress1 Contact CompanyID
BLUESKY 500 Bell Street Tom Xxx 45
SUNSHINE 300 Central Ave Amy Yyy 45
BLUESKY 400 Saks Ave Jon Zzz 45
SUNSHINEONE 300 Central Ave Amy Yyy 45

Is there any way it can be done? If yes, can someone please help me to figure it out. SQL or VBA Code will work fine. Thank you for your help.

RE: Help with update query

Let's do this:
You have this (a little shorter) SQL:

SELECT [Company Name], StreetAddress1, Contact, CompanyID
FROM ContractsDataSet
WHERE (([Company Name] Like "BLUESKY*")
AND (StreetAddress1 Not Like "11 Abc Rd*"))
OR (([Company Name] Like "SUNSHINE*"));

and you get this:
Company Name StreetAddress1   Contact CompanyID
 BLUESKY     500 Bell Street  Tom Xxx   45
 SUNSHINE    300 Central Ave  Amy Yyy   38
 BLUESKY     400 Saks Ave     Jon Zzz   39
 SUNSHINEONE 300 Central Ave  Amy Yyy   38
 
and you want this Desired result (same CompanyID):
 Company Name StreetAddress1    Contact CompanyID
 BLUESKY      500 Bell Street   Tom Xxx   45
 SUNSHINE     300 Central Ave   Amy Yyy   45
 BLUESKY      400 Saks Ave      Jon Zzz   45
 SUNSHINEONE  300 Central Ave   Amy Yyy   45
 
>take the very first record in this list

You do not have any ORDER BY in your Select statement.
Your *first* record could be different every time you run it.
What makes *first record* a first record?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Help with update query

(OP)
Hello Andy,
Thank you for your reply. I found the way how to correct my issue and make it work. Thanks again.

RE: Help with update query

Would you mind posting your solution?
Others may have the same issue and may find this post useful when searching for the answer...

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close