S
SAm
ok, i think i fully understand queries, but this one is beyond me.
i have one query that it go to a odbc tables and calculates two or three
columns based on some complex formulas. then i have complex criteria based on
the calculated columns. this first query is actually a union query because it
needs to be done for two different scenerios. it calculates for one period.
the second query does similar calculation, but the criteria is set up for
all periods and does not need to be restricted by the complex criteria.
instead, i have a third query.
the third query only returns records from the second query if the conditions
of the first are met. basically what i am accomplishing is that i have all
periods if the conditions of the first is met.
i now have a comments table. entries are entered from a form which records
the primary key of the other queries (e.g. employee ID), and then records the
date, and the comment.
i want to add the comment on the query in the third query, that is if there
is a comment. i need all records of the third query, and only matching
comments. i added the table comments to my third query with a left join so
that all the records of the query are returned and comments are returned if
there are any.
problem: i get one record if there is one comment. but let say one
employeeID has two comments for two different dates, then i get twice their
records when there are comments. if there are three comments, then i get
three times their records. and so on. i tried changing to distinct or
distinctrow and it didn't help.
please help. i think i might need to add the comments using VB, if that is
your suggestion, please give me some insight on how to go about it. i am
hoping not to write these queries in vb, because i would spend a few weeks
making it work.
thanks,
sam
i have one query that it go to a odbc tables and calculates two or three
columns based on some complex formulas. then i have complex criteria based on
the calculated columns. this first query is actually a union query because it
needs to be done for two different scenerios. it calculates for one period.
the second query does similar calculation, but the criteria is set up for
all periods and does not need to be restricted by the complex criteria.
instead, i have a third query.
the third query only returns records from the second query if the conditions
of the first are met. basically what i am accomplishing is that i have all
periods if the conditions of the first is met.
i now have a comments table. entries are entered from a form which records
the primary key of the other queries (e.g. employee ID), and then records the
date, and the comment.
i want to add the comment on the query in the third query, that is if there
is a comment. i need all records of the third query, and only matching
comments. i added the table comments to my third query with a left join so
that all the records of the query are returned and comments are returned if
there are any.
problem: i get one record if there is one comment. but let say one
employeeID has two comments for two different dates, then i get twice their
records when there are comments. if there are three comments, then i get
three times their records. and so on. i tried changing to distinct or
distinctrow and it didn't help.
please help. i think i might need to add the comments using VB, if that is
your suggestion, please give me some insight on how to go about it. i am
hoping not to write these queries in vb, because i would spend a few weeks
making it work.
thanks,
sam