SELECT DISTINCT HELP

[arran1180]

table 1 has 2 records of $100 on 1st Jan (primkey is "Ikey", eg 245 and 246))
table 2 has 1 record of $100 on 1st Jan (primkey is "PBKey" eg PB60001)

Currently my qeury uses the cash amout and the date from table 2 to match
the table 1 data twice, i want my query to only match one of these records -
ie no dupes of primary keys used! this example can happen either way round -
sometimes table 2 will have more similar records... I know that my solution
involves the DISTINCT keyword, but it is just the primary keys i want to be
distinct on each table. currently my SQL is :

SELECT DISTINCT tbl_CSFB_PB_tmp.*, qry_CSFB_PB_ImagineTotalsAmended.*
FROM tbl_CSFB_PB_tmp INNER JOIN qry_CSFB_PB_ImagineTotalsAmended ON
Abs(tbl_CSFB_PB_tmp.SumofCashAmount-qry_CSFB_PB_ImagineTotalsAmended.ICash)<0.01
And (tbl_CSFB_PB_tmp.SettlementDate)=(qry_CSFB_PB_ImagineTotalsAmended.Date);


I am very new to SQL so apologies if this is an obvious one!

 

[Ken Snell \(MVP\)]

Show sample data for both tables... too hard to tell what you need without
knowing more about how the data are related in the two tables.

The query you have posted appears to suffer from syntactical errors as well,
but we'll need to see data in order to better understand what you're trying
to do.

 

[arran1180]

thanks for helping on this,

the Query seems to run fine at the moment (apart from the dupes). Please see
sample data below: info is from one table an one qry:, both come from
different external sources and i am trying to reconcile the two...

tbl_CSFB_PN_tbl:
fields: SumofCashAmount, ccy, SettlementDate,description,Ckey
Ckey is the primary key assigned to the data upon import into the database
using autonumber
100.00,USD,4th-Jan-05,itemdescription1,400001
100.00,USD,4th-Jan-05,itemdescription2,400002

qry_CSFB_PB_ImagineTotalsAmended:
ccy,Date,trdid (this is a non-unique reference),ICash,Ikey
Ikey is the primary key assigned to the data upon import into the database
using autonumber
USD,4th-Jan-05,ref10001,100.00,59840

Currently the SQL query will return records from the above where "Icash"
matches the "SumofCashAmount" (within 0.01) AND where the dates are equal.

Problem is in the above example the query will return the records with Ikey
59840 twice - matching it against both Ckey refs from the table. I need to be
able to just use each record once so i can compare back to the original
tables to see what is unmatched. it does not matter which ikey/ckeys are
matched (as long as they have same cash and date), just that they are only
used once.

any help greatly appreciated - let me know if you need any more info
thx

 

[Ken Snell \(MVP\)]

The obstacle for you is that your original query contains too many fields so
that the DISTINCT adjective will do what you seek.

You need to narrow down the fields to just the ones you need to see to show
that you found a match. Your query is returning records where all fields in
the two tables are distinctly different, and of course the Ckey value is
different for each record even when the date and currency amounts are the
same; hence, you get two records.

Identify exactly what you need to see when your query runs, and then we'll
see if we can identify how to write the query you want.

 

[arran1180]

thanks ken..

my aim here is to recocnicel my two tables, and in the end be able to run
two queries that will let me know which records in eact table are unmatched.
The criteria i use to match one with the other is the Cash amount and Date, i
guess using the DISTINCT method my problem will be how to relate the
"matched" data back to the orig tables...
Each record on the two tables is identified by its unique ref - ikey or ckey.
So in the query i use fo matching (below), all i really need is the below
fields

tbl_CSFB_PN_tbl: SumofCashAmount,Date,Ckey

qry_CSFB_PB_ImagineTotalsAmended: Date,Icash,ikey

problem is ensuring that each ikey or ckey ref is only used once in the
matching query..

Any advice will be greatly appreciated - perhaps there is another way to
achieve the same result?
thx

 

[Ken Snell \(MVP\)]

I am hesitant to suggest a specific approach at the moment because I just am
not "seeing" the result that you want to achieve -- this is mostly because I
have no familiarity with your data nor its "end use". I just don't
understand what the "meaning" of the data are in the two separate tables,
and how you want to reconcile the data.

Are you looking to see if the sum of currency values in one table (for each
date) is the same as the sum of currency values in the other table for the
same date?

Perhaps if you can show me a few examples of data that "reconcile" and data
that don't "reconcile", I may begin to understand more quickly. Sorry.
--

Ken Snell
<MS ACCESS MVP>