Regarding duplicate records in a query

[ritimajain]

I am new to access. i m trying to combine two tables using query . I have two tables 'TX' and 'TRX'. I need a field 'EXCHID','CELL,'BLSTATE' from TRX and Field 'BAND' from 'TX' . 'EXCHID' and 'CELL' is common to both the tables.


I m using following SQL

SELECT TRX.EXCHID, TRX.CELL, TRX.BLSTATE, TX.[BAND]
FROM TRX, TX
WHERE TRX.EXCHID=TX.EXCHID AND TRX.CELL=TX.CELL;

But each record get multipied 3 times.It is showing more than valid records.
I can't make exchid or cell a primary key because they are not unique.
Please help me.

[JoeM]

Typically, it is more common to put the fields you are joining on in the JOIN clause instead of a WHERE clause, i.e.

SELECT TRX.EXCHID, TRX.CELL, TRX.BLSTATE, TX.[BAND]
FROM TRX
INNER JOIN TX
ON TRX.EXCHID=TX.EXCHID 
AND TRX.CELL=TX.CELL;

I am unable to download your example (corporate policy), but it sounds like you may have a many-to-many relationship going on (or possibly there are other fields that would show that these records aren't actually duplicates, but they are not being returned in your query.

You may be able to get rid of that if there are other fields you can join on. If not, you can always weed out the duplicate by adding the word DISTINCT after SELECT, i.e.

SELECT DISTINCT TRX.EXCHID, TRX.CELL, TRX.BLSTATE, TX.[BAND]
FROM TRX
INNER JOIN TX
ON TRX.EXCHID=TX.EXCHID 
AND TRX.CELL=TX.CELL;

[ritimajain]

I cant use distinct because by using it decreases total no of records . U will understand my problem more efficiently if u will once look at my attachments.

[June7]

Then the two fields are not enough to establish unique compound key. Looks like need the MO field included as a link between tables. However, the TRX table has more records than TR. Which is the number you want?

[ritimajain]

Ya you are right MO field is included in bot the tables but both tables have different values .In 'TRX' table MO field contain values 'RXOTRX-0-0' while in 'TX' Table MO field contain values 'RXOTX-0-0'. No doubt both tables has 'MO' field but it contains different values , so i cant join them. One more thing both tables also contain field 'BLSTATE' But they are also not same in both tables.
I need same no of records as in 'TRX' table that is 121067.

[JoeM]

If you want the same number of records as in one of your tables, then you have to create a unique, one-to-one relationship between the records in your two tables. As June said, you might need to add another join key.

One way to do that is create a query based on one of your tables and use string functions to manipulate your MO field so that it is in the same format as the one from the other table. Then you can use that query and the other table in a new query, where you can now add a new join to the relationship.

[June7]

As JoeM stated, manipulate the 'almost' identical fields so that they can be used in links.

One way to modify the MO data from TRX table with expression in query:

Replace([MO], "TRX", "TX")

Do similiar with any other fields needed to create unique compound identifier.

Then use the query with calculated fields in another query joining to other table.

[ritimajain]

thanks for suggestion i m almost there, i have done as u suggested but records are not exact , now also query is showing more than needed records . Now it is showing 125120 records , but i need exact no as in trx table which is 121067. I m using following SQL

SELECT TRX.EXCHID, TRX.MO, TRX.CELL, TRX.BLSTATE, TX.[BAND]
FROM TRX INNER JOIN TX ON (TRX.CELL = TX.CELL) AND (TRX.MO = TX.MO) AND (TRX.EXCHID = TX.EXCHID);

One thing more i want to automate the manipulation of string functions of MO field.How can i do it?

[June7]

As already suggested, build a query that manipulates the MO field with the given expression:

SELECT *, Replace(MO, "TRX", "TX") AS MOfixed FROM TRX;

Now join that query to table TX. Link MOfixed with MO.

SELECT Query.EXCHID, Query.MOfixed, Query.CELL, Query.BLSTATE, TX.[BAND]
FROM Query INNER JOIN TX ON (Query.CELL = TX.CELL) AND (Query.MOfixed = TX.MO) AND (Query.EXCHID = TX.EXCHID);

[ritimajain]

I have done exactly as u said bt, By doing this i m again getting same no records as earlier i.e 125120 which is not exact , i want same as TRX table which is 121067 or TX Table which is 121042, i m enabling in getting why i am getting duplicate records . Please help.

[June7]

This is odd. Since data in MO field was different in each table, how could any records have joined in your query that linked the tables directly? Manipulating the MO field from TRX table should have shown different number of records.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ritimajain]

my db is greater tan 2 mb so i m unable to post it here .
i have made MO field common in both tables as u said earlier so i can link them now. but it is not giving same no records as in trx or tx table

[JoeM]

i have made MO field common in both tables as u said earlier so i can link them now. but it is not giving same no records as in trx or tx table

Then you still not have created a one-to-one relationship yet.

If you research it and identify your "duplicate" records, that will probably help you zero in on the problem and help figure out what still needs to be done.

[June7]

Did you run Compact and Repair? Did you zip the file? 2mb zip allowed. Otherwise, upload to a fileshare site such as box.com and post link to file.

[ritimajain]

i have done in same way as u mention but den too db is greater than 2 Mb anyway i have shared my db on box.com and here is link
https://app.box.com/s/qtd2fv1dh5b5y9p16wli ,, Please have a look at d2.zip. In this query2 is based on trx table in which manipulation of string function is done and query3 links tx and query2
And one thing more , I m unable to find duplicate records in query3 as records are very large in number as JOEM suggested to do me earlier