Quering the same table twice but getting duplicated data.

  • Thread starter Thread starter andy
  • Start date Start date
A

andy

I have a table which shows the maturity dates for deals
done with companies and the exposures for each of those
deals.
I have created two queries.
One queries all the deals between 0 to 5 years and the
second queries all deal over 5 years.
In the first query I get back 30 records which is correct.
In the second query I get back one record 30 times.
There is only one deal which meets the second query
criteria, but the query table fills both answers out side
by side and fills the date and exposure for that one deal
along side the other data.
Here is the SQL Code i have.

SELECT deal_tic.MAT_DATE, deal_tic.NBV,
deal_tic_1.MAT_DATE, deal_tic_1.NBV
FROM deal_tic, deal_tic AS deal_tic_1
WHERE (((deal_tic.MAT_DATE)<DateAdd("yyyy",12,Date())) AND
((deal_tic_1.MAT_DATE)>DateAdd("yyyy",12,Date())));
 
There is only one deal which meets the second query
criteria, but the query table fills both answers out side
by side and fills the date and exposure for that one deal
along side the other data.
Here is the SQL Code i have.

SELECT deal_tic.MAT_DATE, deal_tic.NBV,
deal_tic_1.MAT_DATE, deal_tic_1.NBV
FROM deal_tic, deal_tic AS deal_tic_1
WHERE (((deal_tic.MAT_DATE)<DateAdd("yyyy",12,Date())) AND
((deal_tic_1.MAT_DATE)>DateAdd("yyyy",12,Date())));
Click to expand...

You have no JOIN between the two instances of deal_tic - every record
in deal_tic will be combined with every record in deal_tic_1. The
criterion will return all those records in deal_tic where the MAT_DATE
is less than twelve years in the future, and those records in
deal_tic_1 further off in the future than twelve years hence.

Since you're opening the table twice, and selecting fields from both
instances, you would expect to get the answers side by side! I am not
sure what you are expecting. Do you want to join the two instances by
NBV or by some other criterion, so that you're comparing dates for the
same thing?
 
Back
Top