Help with update query

[Magnolia1]

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.

 

[Andrzejek]

Let's do this:
You have this (a little shorter) SQL:
[tt]
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*"));
[/tt]
and you get this:
[pre]
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
[/pre]
and you want this Desired result (same CompanyID):
[pre]
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
[/pre]
>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.

 

[Magnolia1]

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

 

[Andrzejek]

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.