473,837 Members | 1,595 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

comparing two tables to look for mismatches and a way to edit the table value

12 New Member
Hello Everyone,

I am trying to compare data in the country field between two tables to look for differences. The problem is one table has value as "United States" and the other has it as "USA". Another example is England and United Kingdom. So it takes both of these as a mismatch.

I cannot really change value in either of the tables and so I need a way to edit value just for my query and get results.

thanks for me helping me out.....
S :)
Mar 6 '08 #1
4 1980
code green
1,726 Recognized Expert Top Contributor
I have had similar dilemma with our product range.
Their name can vary between countries as well as having name variations and nicknames.
We found a way around by adding an alias field which grew to an alias table.
In other words a simple look up table with name variations is one possible solution
Mar 6 '08 #2
srathi
12 New Member
Thanks! How do I do that?


I have had similar dilemma with our product range.
Their name can vary between countries as well as having name variations and nicknames.
We found a way around by adding an alias field which grew to an alias table.
In other words a simple look up table with name variations is one possible solution
Mar 6 '08 #3
ck9663
2,878 Recognized Expert Specialist
I could be wrong, that sounds like what I would call an intermediate table. You have a table with 2 columns: CountryAlias and CountryName. You relate your table to this table using the CountryAlias and return the CountryName. The initial population of this table will be the unique list of what you currently have on your transaction table. The bloody part is to look at this list one record at a time and try to figure out if you need to insert it on your intermediate table or not.

There could be two way to implement this:

1. You can insert all country on this table with some of the values CountryAlias = CountryName

The cons: It's not really relational.
The pros: If there's CountryName that is not properly "named" and it's not on this table, you'll be able to identify it.

2. Insert those who needs alias, if the Country that you're looking for is not on this table that means it's properly "named".

The pros: It's somehow relational.
The cons: If there's a CountryName that's not on this list and you assumed that it's the proper name (even if it's not), you won't find it, at least not immediately.

Good luck.

-- CK
Mar 6 '08 #4
code green
1,726 Recognized Expert Top Contributor
That is correct ck9663.
The donkey work is collecting the data to populate the table,
although with countries it won't be that much.
And yes, 2 columns is all that is needed
Expand|Select|Wrap|Line Numbers
  1. Country    Alias
  2. ==========================
  3. USA        United States of America
  4. USA        The States
  5. UK          United Kingdom
  6. UK          Great Britain
I would add another pro.
A simple JOIN is all that is required to make comparisons with the main table
Mar 6 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
1570
by: Ian N | last post by:
Hi, i was wondering if anyone would be able to help me with a problem i'm having, I currently have two tables, in a database, one is Called products, and one is called groupproducts, their are multiple groups which can be added to or removed from. I want to creat a screen which lists all of the products and then places a tick in a tick box if that products in the specified group. >From this i'd submit it to another page which update...
3
2048
by: Robert Dell | last post by:
I have a problem comparing strings in an order form i'm writing. I want to give a running total at the bottom of the page and it appears to be working except it doesn't compare correctly (it always adds things up when you didn't select them) so i only added 2 of the items until I got it worked out. here's the page source (you can look at it yourself at http://robertdell.dyndns.org/shifts/ <!DOCTYPE HTML PUBLIC> <html> <head>
41
3973
by: Odd-R. | last post by:
I have to lists, A and B, that may, or may not be equal. If they are not identical, I want the output to be three new lists, X,Y and Z where X has all the elements that are in A, but not in B, and Y contains all the elements that are B but not in A. Z will then have the elements that are in both A and B. One way of doing this is of course to iterate throug the lists and compare each of the element, but is there a more efficient way? ...
3
24040
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked tables in the database where the code resides. If we move the database with the data tables to a new directory, the links are no longer valid. I tried to update the links by changing the Connect property and refreshing: Set td = db.TableDefs(0)...
11
4541
by: dskillingstad | last post by:
I've been struggling with this problem for some time and have tried multiple solutions with no luck. Let me start with, I'm a novice at Access and I'm not looking for someones help to design my database,just help in getting me pointed in the right direction. I have a database with 8 tables, which from what I have read, cannot be linked on a single form, and be updatable. I have created a query which includes all 8 tables, and then...
4
3656
by: Krzysztof Bartosiewicz | last post by:
Hi! I haven't been using Access for a very long time and I forgot everything :) I will be very greatful for help since I have been fighting with this problem for a few hours... I have three tables: Medicines (ID_Medicine, ID_MedicineGroup, MedicineName), MedicineGroups (ID_MedicineGroup, MedicineGroupName) and Treatment (ID_MEDICINE, ID_CONSULT, ID_COMMENT). After the user chooses appropraite MedicineGroup and later Medicine from...
2
7409
by: Scamjunk | last post by:
I have been desperately looking for a treeview-type solution for my problem for the past three weeks and have been greatly unsuccessful. I am totally new to the world of XSLT and I *don't know* JavaScript. Still, I have managed to get something together, which I am putting across here. Any help (even pointing me to the place to look) is welcome. The problem I have is as follows:
5
1863
by: Franck | last post by:
how come unchanged always true even if data changed This code come from my saving button: ============================================ DataSet ds1 = new DataSet(); DataSet ds2 = new DataSet(); DataSet ds3 = new DataSet(); //Static Dataset which contain values when my form load
4
6411
by: gillianbrooks91 | last post by:
Forgive me for asking this question, I've trawled through nearly every available post on this subject that I can find for a few weeks now but nothing quite points me in the right direction. I'm quite new to trying to mess around with VB and ADO within MS Access and have realised the steep learning curve I have, but, I want to try and solve this problem quickly and was wondering if anyone would help me out?? I want to be able to...
0
9833
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10562
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10263
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9391
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5666
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4469
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4036
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3122
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.