Receiving Duplicate data from sql query

[silvafmy]

Hi I have two tables A & B and I am running an SQL query

Table A

currency CR amount Deal1 PrdType Name Matchstatus
1 USD 10 2000000 1014079 BK SEDEINSIN
2 USD 10 2000000 1017719 BK ZEDEINSIN
3 USD 10 2000000 1018170 BK DISTENNSIN
etc

Table B
currency dr amount Deal1 PrdType Name Matchstatus
1 USD 60 -2000000 1012094 BK EDEINSIN
2 USD 60 -789910 1013426 BK DISTENNSIN
3 USD 60 -800000 1017466 BK DISTENNSIN
4 USD 60 -800000 1019755 BK DISTENNSIN
5 USD 60 -2000000 1005207 BK DISTENNSIN
6 USD 60 -2000000 1009178 DD DISTENNSIN
7 USD 60 -2000000 1009579 BK DISTENNSIN
8 USD 60 -2000000 1014986 BK DISTENNSIN
9 USD 60 -2000000 1012082 BK SEDEINSIN
10 USD 60 -2000000 1017717 BK ZEDEINSIN

etc.

How can I run a query whereby Currenty,Amount,ProductType & Name
For
1. Item 1 of Table A matches against Tbl B Item 9
2. Item 2 of Table A matches against Tbl B Item10
3. Item 3 of Table A matches against Tbl B Item 5 ,

When I run my SQL query in Access97 item 3 in Table A appears many times apparently matching against the OTHER amounts in Table B.

My SQL QUERY

SELECT TableDr60.currency, TableCr10.currency, TableCr10.CR, TableDr60.dr, TableCr10.amount, TableDr60.amount, TableCr10.Deal1, TableDr60.Deal1, TableDr60.PrdType, TableCr10.PrdType, TableDr60.Name, TableCr10.Name, TableCr10.Matchstatus, TableDr60.Matchstatus

FROM TableCr10, TableDr60

WHERE ((TableCr10.currency)=[TableDr60].[currency])
AND ((TableCr10.PrdType)=[TableDr60].[PrdType])
And (TableDr60.Name= TableCr10.Name)
AND (([TableDr60].[amount]+[TableCr10].[amount])=0)
AND ((TableCr10.Deal1) In (SELECT distinct [TableCr10].[Deal1] FROM TestCr10 as Tmp));


Pls advise How can I get the system to match exactly one to one record based on the two tables above.I would later Update the MatchStatus in both tables for items matched.

I need to see all the records matched in Table A against the Records in Table B. It should be a one to one exact match.

I have tried using the unmatched query and the duplicate query provided in Access but it only allows one Field to compare. I need the whole FIELDS to be compared.

I have tried joining the tables in the respective fields but when running I encounter duplicate ref Nos. of Table A and duplicate data against Table B. The no of rows increases with data of which I am not so sure why.

Is there a way where I can compare a record in one table against another Table with an exact match and view the matches. Also view the items which are not matched.

Is there also a way where I can add a counter eg A=A+1 for each customer in the particular table where when the amount changes for a customer the counter also changes . The counter will be used for the other table as well & Then try to match based on Name, Product Type, Currency, Amount and Counter.

Pls note the ref Nos are not the same for both tables.

Pls assist or advise whether there is another method? Pls note that I am using Access 97 and Windows 98 as the Operating System.

Thanks

[Dan Hangen]

Hi,

I am not sure if this is works in 97; however, I remember a distinct row capability with SQL. You might try this

SELECT DISTINCT ROW TableDr60.currency, TableCr10.currency, TableCr10.CR, TableDr60.dr, TableCr10.amount, TableDr60.amount, TableCr10.Deal1, TableDr60.Deal1, TableDr60.PrdType, TableCr10.PrdType, TableDr60.Name, TableCr10.Name, TableCr10.Matchstatus, TableDr60.Matchstatus

Hope this helps,
Dan

[silvafmy]

Hi Dan thanks for yr reply which I rec'd today. Currently I am engaged but withing next week I will try and give you my reply.

Thanks for yr reply.

Regards

[Barrie]

Hi,

The following will work provided that you are always trying to match to the first occurance (smallest Deal1).

SELECT TableCR10.ID, TableDR60.ID, TableCR10.currency, TableDR60.currency, TableCR10.CR, TableDR60.dr, TableCR10.amount, TableDR60.amount, TableCR10.Deal1, TableDR60.Deal1, TableCR10.PrdType, TableDR60.PrdType, TableCR10.Name, TableDR60.Name, TableCR10.MatchStatus, TableDR60.MatchStatus
FROM TableCR10 INNER JOIN TableDR60 ON (TableCR10.currency = TableDR60.currency) AND (TableCR10.PrdType = TableDR60.PrdType) AND (TableCR10.Name = TableDR60.Name)
WHERE ((([TableCR10].[amount]+[TableDR60].[amount])=0))
AND TableDR60.Deal1 IN (SELECT Min(TableDR60.Deal1) AS Expr1
FROM TableDR60
GROUP BY TableDR60.Name, TableDR60.amount);

Hope this helps
Barrie