469,323 Members | 1,483 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

limit query results to no duplicates on 1 field

Dom


I need to run a query based on a query in Access. The second query has
a number of conditions which all work well, but there is one more
contition I need to set to make it run properly.

the first query returns a number of data items, consisting, among other
fields, of a Company_ID and a Rating_Date. For most companies, the
latest value of Rating_Date is equal to a value in a seperate table
(tblVariables) which logs the last time a ratings update took place on
the database. To return one line per company for these companies, the
second query has a condition

...
WHERE (((qryCompanyInfo.Rating_Date)=[Variables]![LastRatingDate] ...

However, this does not show companies which are no longer being rated,
and therefore have no record where Rating_Date = LastRatingDate.
Companies which are no longer being rated have a Company_Type value of
9, so to show these companies, I added to the SQL as follows:

...
WHERE (((qryCompanyInfo.Date)=[Variables]![LastRatingDate] OR
(((qryCompanyInfo.Company_Type)=9));
...

The problem is that this now returns 1 line for all rated companies,
using the first condition, but then every record for the unrated
companies. I need to somehow filter the second condition, so that only
1 instance of each company appears in the final query.

I hope somebody can help on this, as I've been going around in circles
trying to figure it out. Thanks

dom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
2 18332
Dom,

If I understand you correctly, you need to use an "Outer Join". You can
do this by opening your second query in design view and double-clicking
the black join line connecting qryCompanyInfo and tblVariables.

Choose the option: "Include ALL records from 'qryCompanyInfo' and only
those records from 'tblVariables' where the joined fields
are equal."

The resulting SQL will look something like:

SELECT qryCompanyInfo.Company_ID, qryCompanyInfo.Rating_Date,
tblVariables.LastRatingDate
FROM qryCompanyInfo LEFT JOIN tblVariables ON qryCompanyInfo.Rating_Date
= tblVariables.LastRatingDate;

HTH

Stuart

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2
Dom <cr**************@bilge.net> wrote in message news:<3f*********************@news.frii.net>...
I need to run a query based on a query in Access. The second query has
a number of conditions which all work well, but there is one more
contition I need to set to make it run properly.

the first query returns a number of data items, consisting, among other
fields, of a Company_ID and a Rating_Date. For most companies, the
latest value of Rating_Date is equal to a value in a seperate table
(tblVariables) which logs the last time a ratings update took place on
the database. To return one line per company for these companies, the
second query has a condition

..
WHERE (((qryCompanyInfo.Rating_Date)=[Variables]![LastRatingDate] ...

However, this does not show companies which are no longer being rated,
and therefore have no record where Rating_Date = LastRatingDate.
Companies which are no longer being rated have a Company_Type value of
9, so to show these companies, I added to the SQL as follows:

..
WHERE (((qryCompanyInfo.Date)=[Variables]![LastRatingDate] OR
(((qryCompanyInfo.Company_Type)=9));
..

The problem is that this now returns 1 line for all rated companies,
using the first condition, but then every record for the unrated
companies. I need to somehow filter the second condition, so that only
1 instance of each company appears in the final query.

I hope somebody can help on this, as I've been going around in circles
trying to figure it out. Thanks

dom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


hello,

Why do you make the rather complicated construct of joining with a
separate query containing records with the last ratingdate ?
Didn't you try to use aggregate functions like Max(ratingdate) to
filter out the latest ratings for each company ? You just have to make
a query consisting of some company-idtifier and the ratingdate to
achieve this (and eventually some other conditions ..) and make it an
aggregation by pressing the sum-button (the greek E-like symbol on the
menu) and to select max in stead of 'group by' for the ratingdate.
After that you can join this query with other data based on the
company-id and the ratingdate to get other relevant data by using the
query again as a table for another query.
May be you tried this procedure, but then you had to make a separate
small aggregation-query for companies not rated with some dummy or
empty value for the last rating-date if these are not already result
of the other aggregation-query for some reason and make a union, but
this method is not an elegant one.
Often this can be avoided by ensuring all companies are retained in
the first query when these are joined with other tables. In that case
you have to connect the other tables with an outer-join (selecting the
connection-lines and changing the jointype so the companies are all
kept into the query and also adding a 'or is null' clause for every
select-condition for fields in the tables connected by the outer
join-conditions. If the companies not rated are retained, the
resulting aggregation-query automatically produces null-values for
company's without any ratingdates.
May be I dont quite understand why it seems so difficult, but I hope
you get some suggestions to find a more elegant solution.

Marc
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Guy Erez | last post: by
1 post views Thread by AP | last post: by
3 posts views Thread by Sim Zacks | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.