compare datas between 2 files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 files that have same fields, date,credit number and amount. some
transacitons may have duplicate entry with same credit number with either the
same amount or different amount. These are true with the second file also. I
need to compare both and match them together and see what are left
outstanding for both files. When I do query with either unique value or
unique record, it still give me duplicate matching. I did have an indexec
number for both files. For example, file#1 has credit number12345 with
$10.00, file#2 has 2 transactions with credit number 12345 with $10, Access
will pick up file#1 twice and match up with both 2 transactions in
file#2,vice versa.

I need to be able to match exactly but not pick up the one that can't match.

Any help will be appreciated.
 
dumm said:
I have 2 files that have same fields, date,credit number and amount. some
transacitons may have duplicate entry with same credit number with either the
same amount or different amount. These are true with the second file also. I
need to compare both and match them together and see what are left
outstanding for both files. When I do query with either unique value or
unique record, it still give me duplicate matching. I did have an indexec
number for both files. For example, file#1 has credit number12345 with
$10.00, file#2 has 2 transactions with credit number 12345 with $10, Access
will pick up file#1 twice and match up with both 2 transactions in
file#2,vice versa.

I need to be able to match exactly but not pick up the one that can't match.

Any help will be appreciated.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well,... they both match. How do you determine which to keep?

One way to get no duplicates:

SELECT [Date], [Credit Number], Amount
FROM table1

UNION

SELECT [Date], [Credit Number], Amount
FROM table2

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjIJ44echKqOuFEgEQKGZwCg20emLHz62l3BjWAchFwSVu0F+JMAoLkH
81O83hqCrB2r9ZNPFwDiNQ/J
=FmZ8
-----END PGP SIGNATURE-----
 
Thanks, but I think I should make it clearer. This is a reconciliation
between cash transactions that were entered in one table and the payment were
entered in another table. I need to match both transactions and to see which
transactions on both table that are not matched. Some of the transactions can
have duplicate credit number and amount. I have to see that it is matched
transaction by transaction. Sometimes it may get entered in table 1 by
mistake and never get paid in talbe2 or vice versa.

MGFoster said:
dumm said:
I have 2 files that have same fields, date,credit number and amount. some
transacitons may have duplicate entry with same credit number with either the
same amount or different amount. These are true with the second file also. I
need to compare both and match them together and see what are left
outstanding for both files. When I do query with either unique value or
unique record, it still give me duplicate matching. I did have an indexec
number for both files. For example, file#1 has credit number12345 with
$10.00, file#2 has 2 transactions with credit number 12345 with $10, Access
will pick up file#1 twice and match up with both 2 transactions in
file#2,vice versa.

I need to be able to match exactly but not pick up the one that can't match.

Any help will be appreciated.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well,... they both match. How do you determine which to keep?

One way to get no duplicates:

SELECT [Date], [Credit Number], Amount
FROM table1

UNION

SELECT [Date], [Credit Number], Amount
FROM table2

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjIJ44echKqOuFEgEQKGZwCg20emLHz62l3BjWAchFwSVu0F+JMAoLkH
81O83hqCrB2r9ZNPFwDiNQ/J
=FmZ8
-----END PGP SIGNATURE-----
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I was going to put this in the first post, but thought you might figure
it out for yourself. You can use the UNION query I posted w/ another
query to get the unmatched items (I believe).

[Access 2002 w/ ANSI SQL-92 option checked]

SELECT [Date], [Credit Number], Amount
FROM table1 as t
WHERE NOT EXISTS (SELECT *
FROM
(SELECT [Date], [Credit Number], Amount
FROM table1
UNION
SELECT [Date], [Credit Number], Amount
FROM table2 ) As A
WHERE [Date] = t.[Date]
AND [Credit Number] = t.[Credit Number])

And, the same query for table 2 - just change the FROM clause in the
main query "table2 as t."

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjOQNIechKqOuFEgEQLIGwCdGTWvqbxsViOG5eglI3xtCm0+NpsAn0Ed
PWNOqzE1L+mIEeUEwU+8EMHp
=RmwA
-----END PGP SIGNATURE-----


dumm said:
Thanks, but I think I should make it clearer. This is a reconciliation
between cash transactions that were entered in one table and the payment were
entered in another table. I need to match both transactions and to see which
transactions on both table that are not matched. Some of the transactions can
have duplicate credit number and amount. I have to see that it is matched
transaction by transaction. Sometimes it may get entered in table 1 by
mistake and never get paid in talbe2 or vice versa.

:

dumm said:
I have 2 files that have same fields, date,credit number and amount. some
transacitons may have duplicate entry with same credit number with either the
same amount or different amount. These are true with the second file also. I
need to compare both and match them together and see what are left
outstanding for both files. When I do query with either unique value or
unique record, it still give me duplicate matching. I did have an indexec
number for both files. For example, file#1 has credit number12345 with
$10.00, file#2 has 2 transactions with credit number 12345 with $10, Access
will pick up file#1 twice and match up with both 2 transactions in
file#2,vice versa.

I need to be able to match exactly but not pick up the one that can't match.

Any help will be appreciated.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well,... they both match. How do you determine which to keep?

One way to get no duplicates:

SELECT [Date], [Credit Number], Amount
FROM table1

UNION

SELECT [Date], [Credit Number], Amount
FROM table2

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjIJ44echKqOuFEgEQKGZwCg20emLHz62l3BjWAchFwSVu0F+JMAoLkH
81O83hqCrB2r9ZNPFwDiNQ/J
=FmZ8
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
I am not a guru in Access. I try the select statement and get an error. I did
try the union before but he problem is the data only show for one table I
need it to show datas from both tables side by side so I know that what I
booked was paid by the bank also.

Thanks

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I was going to put this in the first post, but thought you might figure
it out for yourself. You can use the UNION query I posted w/ another
query to get the unmatched items (I believe).

[Access 2002 w/ ANSI SQL-92 option checked]

SELECT [Date], [Credit Number], Amount
FROM table1 as t
WHERE NOT EXISTS (SELECT *
FROM
(SELECT [Date], [Credit Number], Amount
FROM table1
UNION
SELECT [Date], [Credit Number], Amount
FROM table2 ) As A
WHERE [Date] = t.[Date]
AND [Credit Number] = t.[Credit Number])

And, the same query for table 2 - just change the FROM clause in the
main query "table2 as t."

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjOQNIechKqOuFEgEQLIGwCdGTWvqbxsViOG5eglI3xtCm0+NpsAn0Ed
PWNOqzE1L+mIEeUEwU+8EMHp
=RmwA
-----END PGP SIGNATURE-----


dumm said:
Thanks, but I think I should make it clearer. This is a reconciliation
between cash transactions that were entered in one table and the payment were
entered in another table. I need to match both transactions and to see which
transactions on both table that are not matched. Some of the transactions can
have duplicate credit number and amount. I have to see that it is matched
transaction by transaction. Sometimes it may get entered in table 1 by
mistake and never get paid in talbe2 or vice versa.

:

dumm dumm wrote:

I have 2 files that have same fields, date,credit number and amount. some
transacitons may have duplicate entry with same credit number with either the
same amount or different amount. These are true with the second file also. I
need to compare both and match them together and see what are left
outstanding for both files. When I do query with either unique value or
unique record, it still give me duplicate matching. I did have an indexec
number for both files. For example, file#1 has credit number12345 with
$10.00, file#2 has 2 transactions with credit number 12345 with $10, Access
will pick up file#1 twice and match up with both 2 transactions in
file#2,vice versa.

I need to be able to match exactly but not pick up the one that can't match.

Any help will be appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well,... they both match. How do you determine which to keep?

One way to get no duplicates:

SELECT [Date], [Credit Number], Amount
FROM table1

UNION

SELECT [Date], [Credit Number], Amount
FROM table2

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjIJ44echKqOuFEgEQKGZwCg20emLHz62l3BjWAchFwSVu0F+JMAoLkH
81O83hqCrB2r9ZNPFwDiNQ/J
=FmZ8
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 
Back
Top