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
