By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,096 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Select/Update Query

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a

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

Nov 13 '05 #4

P: n/a

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

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.