list records

[geebee]

i have 3 tables.

table1. contains idnumbers and other information
table2. contains issues related to the idnumbers in table1
table3. contains information regarding to companies

i want to display the idnumbers along with issues from table2 for each
idnumber.
but only the issues should be listed which have an issueID = min(issueID)
from table1 for each idnumber. the other conditions are that the noticedate
from table2 should be >= to the noticedate from table1 for each record.

thanks in advance,
geebee

 

[Michel Walsh]

SELECT idnumber, MIN(issueID)
FROM table2
GROUP BY idNumber


saved as idWithMinIssue, then:




SELECT *
FROM ( table1 INNER JOIN idWithMinIssue
ON table1.idnumber = idWithMinIssue.idNumber)

INNER JOIN table2
ON table2.idnumber = idWithMinIssue.idNumber
AND table2.noticedate >=table1.noticedate





Vanderghast, Access MVP

 

[KARL DEWEY]

Your post is jumbled!

You say table1 contains idnumbers but none of the other have idnumber.

How are the table2 issues related to the table1 idnumbers?

How can there be a relationship like 'an issueID = min(issueID) from table1'?

Then you say ' the noticedate from table2 should be >= to the noticedate
from table1 for each record.' but you never listed [noticedate] as a field in
the tables. Do both tables have [noticedate]?

Post the actual table structure with field names to include datatype and
some sample data from each table.

 

[geebee]

hi,

table1 and table2 both have idnumber and issueID fields. and they are
linked together by idnumber.

Your post is jumbled!

You say table1 contains idnumbers but none of the other have idnumber.

How are the table2 issues related to the table1 idnumbers?

How can there be a relationship like 'an issueID = min(issueID) from table1'?

Then you say ' the noticedate from table2 should be >= to the noticedate
from table1 for each record.' but you never listed [noticedate] as a field in
the tables. Do both tables have [noticedate]?

Post the actual table structure with field names to include datatype and
some sample data from each table.

i have 3 tables.

table1. contains idnumbers and other information
table2. contains issues related to the idnumbers in table1
table3. contains information regarding to companies

i want to display the idnumbers along with issues from table2 for each
idnumber.
but only the issues should be listed which have an issueID = min(issueID)
from table1 for each idnumber. the other conditions are that the noticedate
from table2 should be >= to the noticedate from table1 for each record.

thanks in advance,
geebee

 

[KARL DEWEY]

Read the rest of the post.

hi,

table1 and table2 both have idnumber and issueID fields. and they are
linked together by idnumber.

Your post is jumbled!

You say table1 contains idnumbers but none of the other have idnumber.

How are the table2 issues related to the table1 idnumbers?

How can there be a relationship like 'an issueID = min(issueID) from table1'?

Then you say ' the noticedate from table2 should be >= to the noticedate
from table1 for each record.' but you never listed [noticedate] as a field in
the tables. Do both tables have [noticedate]?

Post the actual table structure with field names to include datatype and
some sample data from each table.

i have 3 tables.

table1. contains idnumbers and other information
table2. contains issues related to the idnumbers in table1
table3. contains information regarding to companies

i want to display the idnumbers along with issues from table2 for each
idnumber.
but only the issues should be listed which have an issueID = min(issueID)
from table1 for each idnumber. the other conditions are that the noticedate
from table2 should be >= to the noticedate from table1 for each record.

thanks in advance,
geebee