Joins between 'almost' identical fields (1 Viewer)

[AC5FF]

I'm working with some databases that were not fully thought out and I need to join some fields where the data might not be the same.

I could use another query or two and make it work - but I'm trying to avoid doing that if at all possible.

How would I join the two tables if my customer table shows an account number of "002-123" and my product table shows the account number as "W02-123"

Thx

 

[plog]

What exactly are you trying to avoid? Why is your 2 query matching system sub-optimal?

Other than teasing out matches (via a query or function), the only option I see is to make a mapping table. You build a new table with 2 columns, 1 for the value in Table1 and then another column for its matching value in Table2.

 

[AC5FF]

Just trying to reduce steps. I've gotten the data I needed using two queries.
But there are SOO many queries in this DB; the less I have to create and keep track of the better (i think...).

I've tried to modify the query in SQL using right(acct#,6) etc... but I get errors when I do (I've never gotten the hang of writing SQL)

 

[George Moore]

Suggest you post the SQL you are trying........there may be an obvious syntax error

 

[AC5FF]

Here's what I had:
I tried adding the RIGHT(x,y) function in the bold line.

SELECT Count(MetricsGraphAptCount.[D_Code]) AS [CountOfD_Code], CGASCUST.[account status active/inactive]
[B]FROM CGASCUST INNER JOIN MetricsGraphAptCount ON CGASCUST.right([Account #],6) = MetricsGraphAptCount.right([CCC-AAA],6)[/B]
GROUP BY CGASCUST.[account status active/inactive]
HAVING (((Count(MetricsGraphAptCount.[D_Code]))>0) AND ((CGASCUST.[account status active/inactive])="a"));

 

[Pat Hartman]

Perhaps normalizing the database would be the best way to spend your time rather than wasting it trying to overcome deficiencies.

 

[AC5FF]

I would 100% agree Pat.
Problem doing that is i'm working with half a dozen databases that all tie together or into our billing system in some shape or form. It is just a nightmare for trying to maintain and update.

It's something I've been working on for the past year or so. I'm making progress, but until I can feel comfortable with everything working I can't finalize that switch. Every time I do a test I find another area that has to be modified to work. I am not sure I'll EVER get to that point.

 

[Mark_]

AC5FF,

Fixing your data FIRST will help make the rest much easier.

 

[jdraw]

I agree with Pat and Mark. If this/these database(s) are meant to complement each other and, more importantly, support and integrate the business of the organization, then it seems the management of that business must be feeling some pain. You need a plan with a sponsor who is hurting from this current set up. If your data structures are not well designed/normalized, you'll spend a lot of fruitless time trying to create workarounds. Seems like a business and requirements review is needed.

Good luck.

 

[CJ_London]

I agree with the other comments but for a quick fix your code

CGASCUST.right([Account #],6)

needs to be

Right(CGASCUST.[Account #],6)

 

[George Moore]

and following on from CJ.....

MetricsGraphAptCount.right([CCC-AAA],6)

needs to be

Right(MetricsGraphAptCount.[CCC-AAA],6)

 

[arnelgp]

there is no issue of Normalization here.
its the way it is.
other department may have different
coding system.
that's why that system exists.
and the earlier programmer has
that in mind.

it can be solved by creating
a junction table, eg:

tblCommon

sysCode dbCode
========================
1 002-123
1 W02-123
1 ZZ2-123

/-
add this table to your query and Join on
common sysCode.

 

[AC5FF]

CJ / George - Thank You! I see where my mistake was. This helps a lot!

This business is close to 40yrs old; the databases were started back around 2000 and created around existing requirements at the time. As the company grew and modified the way it ran; instead of creating a better database the people at the time just added to or modified existing ones to fit their needs. Trust me - it is a complete nightmare.

But, we are under new management now that can 'see' these deficiencies. It is keeping me VERY busy and my new dbase will hopefully address all these issues.

 

[Pat Hartman]

It is very common to create conversion tables to help to integrate separate systems. Sometimes the tables will be static - one system assigns autonumbers to the state table and another uses the two character abbreviation as the natural key. You need a cross reference to sync these two but the cross reference isn't likely to change. One of my clients uses a third-party company to handle all of its interactions with utility companies. EDI is used to communicate between the utility and the service bureau. The utilities occasionally change their names so we end up with old records with "Company A" as the company name but new records have "New and Improved Company A" as the company name so we have to maintain a cross reference that allows us to identify both as the same company. One of the utilities we do business with has four names and others use the same name for both their gas and electric business which causes a different problem so we had to include product type in the lookup table so we could differenciate "Company B", "Gas" from "Company B", "Electric"