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

difference between dates - for a beginner!

P: 3
Hi everyone - I've been using a simple Access 2000 database table for some time for data for animal breeding programme. Every breeding animal has an individual record for each time there is a birth, showing the number born ... and so on. What I want to be able to do is to work out the amount of time - say as a fraction of a year - between the first and the last birth, bearing in mind that there may be anything from 3 to 10 births for any particular animal. Help please - and you need to know I'm not into programming language etc.
Thanks in anticipation!
Nov 13 '06 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,186
DeeBees,

Can you post the details of your record structure so far?
It does make helping easier.

Refer to this post for guidance (POSTING GUIDELINES: Please read carefully before posting to a forum).
Nov 13 '06 #2

P: 3
DeeBees,

Can you post the details of your record structure so far?
It does make helping easier.

Refer to this post for guidance (POSTING GUIDELINES: Please read carefully before posting to a forum).
Okay - columns across from LHS are
1. Parent ref number in question (text)
2. date mated (the first entry will be the starting point)
3. who mated to (text)
4. Date of resulting birth
5. No born alive (numeric)
6. No alive at 12 weeks (numeric)

There will be several lines of entires of successive matings and the resulting data. I want to be able to work out the time period between first and last matings and then divide that into the sum of the numbers alive at 12 weeks to get an annualised "going rate".

Sorry if this is very amateur to you highly experienced professionals - I have tried getting sense from the Access "help" files without any real success so far. Thanks again in anticipation!
Nov 13 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Okay - columns across from LHS are
1. Parent ref number in question (text)
2. date mated (the first entry will be the starting point)
3. who mated to (text)
4. Date of resulting birth
5. No born alive (numeric)
6. No alive at 12 weeks (numeric)

There will be several lines of entires of successive matings and the resulting data. I want to be able to work out the time period between first and last matings and then divide that into the sum of the numbers alive at 12 weeks to get an annualised "going rate".

Sorry if this is very amateur to you highly experienced professionals - I have tried getting sense from the Access "help" files without any real success so far. Thanks again in anticipation!
Try something like this:

SELECT [Parent ref number in question], Max([date mated]) - Min([date mated]) As NoDaysMating, Sum([No alive at 12 weeks]) As Alive12Wks, (Alive12Wks / NoDaysMating) As goingRate
FROM TableName
GROUP BY [Parent ref number in question];
Nov 13 '06 #4

P: 3
I really do appreciate your help - looks like we got there! Thanks so much.
Nov 15 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
I really do appreciate your help - looks like we got there! Thanks so much.
No problem. Glad I could help.
Nov 15 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.