Select/Update Query

[osmethod]

Hello,

I've had help from this group before and I hope someone could be of
help again.

I have 2 tables as follows

tbl_joinings

Mnum JoinDate
1001 01/10/04
1001 02/11/04
tbl_matings

Mnum MateDate JoinDate
1001 31/10/04
I need to generate a query which will
"select the date from tbl_joinings just before the date 31/10/04 in
tbl_matings, i.e. 01/10/04. This to be repeated for 1000 or so records.

The tbl_matings table after this update would be as follows..

tbl_matings

Mnum MateDate JoinDate
1001 31/10/04 01/10/04
Note - There is not a 1 to many relationship between these tables. In
fact it would be a many to many relationship.

Any help would be most appreciated

Thank you...

osmethod

[Pachydermitis]

maybe I don't understand your question but why not
SELECT j.Mnum, m.MateDate, Max(j.JoinDate) as JoinDate
FROM tbl_joinings as J INNER JOIN tbl_matings as M ON j.Mnum=m.Mnum
WHERE m.MateDate>=j.JoinDate
GROUP BY j.Mnum, m.MateDate

HTH
Pachydermitis

[Pachydermitis]

maybe I don't understand your question but why not
SELECT j.Mnum, m.MateDate, Max(j.JoinDate) as JoinDate
FROM tbl_joinings as J INNER JOIN tbl_matings as M ON j.Mnum=m.Mnum
WHERE m.MateDate>=j.JoinDate
GROUP BY j.Mnum, m.MateDate

HTH
Pachydermitis

[osmethod]

Pachydermitis wrote:

maybe I don't understand your question but why not
SELECT j.Mnum, m.MateDate, Max(j.JoinDate) as JoinDate
FROM tbl_joinings as J INNER JOIN tbl_matings as M ON j.Mnum=m.Mnum
WHERE m.MateDate>=j.JoinDate
GROUP BY j.Mnum, m.MateDate

HTH
Pachydermitis

Why not indeed....

That worked perfectly "Pachydermitis" - Sincere Thank you..

osmethod

[osmethod]

Pachydermitis wrote:

maybe I don't understand your question but why not
SELECT j.Mnum, m.MateDate, Max(j.JoinDate) as JoinDate
FROM tbl_joinings as J INNER JOIN tbl_matings as M ON j.Mnum=m.Mnum
WHERE m.MateDate>=j.JoinDate
GROUP BY j.Mnum, m.MateDate

HTH
Pachydermitis

Why not indeed....

That worked perfectly "Pachydermitis" - Sincere Thank you..

osmethod