avoid multiple queries

[slimjen]

all, using 2010. I have two tables. :

tblone: 
cust_id 
mtr_id
state

tbltwo: 
cust_id
 state
name

They both have the main field; cust_id in both tables. In table one; there is a mtr_id field which is associated with cust_id field. I need to match this field in table two. So I setup a query to do this which which works fine:

SELECT tblone.mtr_id, tblTwo.cust_id, tblTwo.CNAME, tblTwo.EFF_DTE, tblTwo.TAX_DESC, tblTwo.STATE, tblTwo.[rate], tblTwo.[Acctnum] tblTwo.Notes
FROM tblone LEFT JOIN tblTwo ON (tblone.State = tblTwo.STATE) AND (tblone.cust_id = tblTwo.cust_id);

Table one is kept updated by the user. My problem is in tblone; the user mixed up the fields. What I mean is; instead of putting the cust_id in the cust_id field; they put it in the mstr_id field. So now I have some records with the mstr_id field in the cust_id field and cust_id in the mstr_id field and my query is set up by joining the cust_id There are too many records; almost 10000 to correct this. So my question is does anyone know a way I can use the above query without having to use multiple queries to accomplish what I need. I hope I didn't confuse the issue. Thanks

[burrina]

First, let me say that is a bad database design that would allow this to happen and I strongly suggest you correct it. That said, you can probably accomplish this by query(s) or else a make table and then clean up the data.

Good Luck.

[slimjen]

Yes; I failed to mention this is not my database and I've been asked to help. I just thought it was a quick way out of this. Thanks anyway

[burrina]

Not a problem. Feel sorry for you.

Good Luck!

[ItsMe]

I would attempt to fix the data. I would look carefully at the data first. Determine how and when the user did this (what method did they use). If the data is not correct, it needs to be fixed. If some of the data is not correct, you can not depend on any of the records.

After isolating the bad records, maybe using queries to isolate records that are orphaned because the JOIN is looking for a non existing PK, I would design some Update queries to populate the correct Key field with the value from mstr_id.

If you can create a query based on bad data and you can depend on the query, you can fix the bad data. So fix the bad data.