Select in Query causes cartesian results

[Al]

I have a query based upon a table (ClientInfo) with 362 rows that because of
a Select statement in one of the Fields it translates to 14560 rows.

Here's what I am doing:

ClientInfo Table: SiteChange Table:
ClientNo (Text) OldSiteNo (Text)
Status (bit) NewSiteNo (Text)
Comment (Text)

Site number is buried in the ClientNo. left([ClientNo],3) extracts it. My
Select is as follows:

Site: (SELECT [NewSite] FROM [SiteChange] WHERE [OldSite]=Left([ClientNo],3))

The SQL for this query looks like this:

SELECT ClientNo, (SELECT [NewSite] FROM [792_SiteChange] WHERE
[OldSite]=Left([PNO],3)) AS Site FROM ClientInfo;

My ultimate goal is to update the ClientNo with the newSite Information.
How can I stop the cartesian results?

Thanks for you replies!!

 

[John Spencer]

I would add a new field to the Client Info table an "exhume" (unbury) the
SiteNo. It really should be in a separate field.

If you can't fix this design flaw in the data, then try the following untested SQL

UPDATE ClientInfo INNER JOIN SiteChange
ON LEFT(ClientInfo.ClientNo,3) = SiteChange.OldSiteNo
SET ClientNo = [NewSiteNo] & Mid([ClientNo],4)
WHERE ClientNo is not null

If that doesn't work the build a query on Client info that breaks out the old
site no and then use the query and the site change table joining on the
calculated site no and the oldSiteNo field. Then you can convert that to an
update query.