Select dates in range

F

Francisco

Good day to all,

I've a table with some records of events for subjects and on certain dates,
now I need to get a query that returns me the Subject, Subject creation date,
3 months after creation date, Value at 3 months, 6 months after creation
date, Value at 6 months:

This is for example:

Table subject:

ID Name Born
1 Jon 6/6/95
2 Betty 8/12/95
3 Rudolf 15/01/96

Table wheight
Date subjectID Wheight
6/7/95 1 10
7/8/95 1 18
5/9/95 1 25
................... (continue)
6/10/95 1 22
................... (continue)
10/12/95 1 45
10/12/95 2 11
8/1/96 1 51
8/1/96 2 18
7/2/96 1 58
7/2/96 2 25
7/2/96 3 9
.......... (continue)

Now I want to get a query that has this format:

ID Born Value aprox Value aprox Value
aprox
3 Months 6 Months
9 Months
1 6/6/95 22 51
.....
2 8/12/95 ..... ....
.....
3 15/01/96 .... ....
.....



The dates where I recorded the events aren't exactly 3,6 and 9 months after
the initial date, so it has to be aprox.


I know that I'll have to:
Select [tblsubject].IDsubject, [tblSubject].Dateborn, a.Value as 3Months,
b.Value as 6Months, c.Value as 9Months
....... and now I'm completely blank!!!


I hope someone can be of my assistance.

Sorry for the confusing question

Francisco T. B.
Universidade de Évora

P.S. - I hope this is not a duplicate question, I haven't found anything
related.
 
M

Michel Walsh

SELECT a.id, MIN(a.name), MIN(a.born), MIN(b.weight), MIN(c.weight),
MIN(d.weight), MIN(e.weight)
FROM (((subjects AS a LEFT JOIN weights AS b ON a.id=b.id AND b.date >=
a.born)
LEFT JOIN weights AS c ON a.id=c.id AND c.date >= dateAdd("m", 3,
a.born) )
LEFT JOIN weights AS d ON a.id=d.id AND d.date >=dateAdd("m", 6,
a.born) )
LEFT JOIN weights AS e ON a.id=e.id AND e.date >= dateAdd("m", 9,
a.born)
GROUP BY a.id



is a possible solution.



Another one would be to use a crosstab, definitively simpler...


TRANSFORM MIN(b.weight)
SELECT a.id
FROM subjects AS a INNER JOIN weights AS b ON a.id=b.id
GROUP BY a.id
PIVOT 3*(datediff("m", a.born, b.date)\3) & "month"



Hoping it may help,
Vanderghast, Access MVP
 
F

Francisco

The crosstab solution worked great, as I then made another query selcting the
"months" I wanted. The first kept giving an Error on the JOIN operation.


Thank you very much again, your help is priceless.
Francisco T.B.
Universidade de Évora

Michel Walsh said:
SELECT a.id, MIN(a.name), MIN(a.born), MIN(b.weight), MIN(c.weight),
MIN(d.weight), MIN(e.weight)
FROM (((subjects AS a LEFT JOIN weights AS b ON a.id=b.id AND b.date >=
a.born)
LEFT JOIN weights AS c ON a.id=c.id AND c.date >= dateAdd("m", 3,
a.born) )
LEFT JOIN weights AS d ON a.id=d.id AND d.date >=dateAdd("m", 6,
a.born) )
LEFT JOIN weights AS e ON a.id=e.id AND e.date >= dateAdd("m", 9,
a.born)
GROUP BY a.id



is a possible solution.



Another one would be to use a crosstab, definitively simpler...


TRANSFORM MIN(b.weight)
SELECT a.id
FROM subjects AS a INNER JOIN weights AS b ON a.id=b.id
GROUP BY a.id
PIVOT 3*(datediff("m", a.born, b.date)\3) & "month"



Hoping it may help,
Vanderghast, Access MVP




Francisco said:
Good day to all,

I've a table with some records of events for subjects and on certain
dates,
now I need to get a query that returns me the Subject, Subject creation
date,
3 months after creation date, Value at 3 months, 6 months after creation
date, Value at 6 months:

This is for example:

Table subject:

ID Name Born
1 Jon 6/6/95
2 Betty 8/12/95
3 Rudolf 15/01/96

Table wheight
Date subjectID Wheight
6/7/95 1 10
7/8/95 1 18
5/9/95 1 25
.................. (continue)
6/10/95 1 22
.................. (continue)
10/12/95 1 45
10/12/95 2 11
8/1/96 1 51
8/1/96 2 18
7/2/96 1 58
7/2/96 2 25
7/2/96 3 9
......... (continue)

Now I want to get a query that has this format:

ID Born Value aprox Value aprox
Value
aprox
3 Months 6 Months
9 Months
1 6/6/95 22 51
.....
2 8/12/95 ..... ....
.....
3 15/01/96 .... ....
.....



The dates where I recorded the events aren't exactly 3,6 and 9 months
after
the initial date, so it has to be aprox.


I know that I'll have to:
Select [tblsubject].IDsubject, [tblSubject].Dateborn, a.Value as 3Months,
b.Value as 6Months, c.Value as 9Months
...... and now I'm completely blank!!!


I hope someone can be of my assistance.

Sorry for the confusing question

Francisco T. B.
Universidade de Évora

P.S. - I hope this is not a duplicate question, I haven't found anything
related.
Click to expand...
Click to expand...
 
M

Michel Walsh

The error is probably about ( ), around the ON clause, like:


.... ON (a.id=b.id AND b.date >= a.born) ...


Vanderghast, Access MVP


Francisco said:
The crosstab solution worked great, as I then made another query selcting
the
"months" I wanted. The first kept giving an Error on the JOIN operation.


Thank you very much again, your help is priceless.
Francisco T.B.
Universidade de Évora

Michel Walsh said:
SELECT a.id, MIN(a.name), MIN(a.born), MIN(b.weight), MIN(c.weight),
MIN(d.weight), MIN(e.weight)
FROM (((subjects AS a LEFT JOIN weights AS b ON a.id=b.id AND b.date >=
a.born)
LEFT JOIN weights AS c ON a.id=c.id AND c.date >= dateAdd("m", 3,
a.born) )
LEFT JOIN weights AS d ON a.id=d.id AND d.date >=dateAdd("m", 6,
a.born) )
LEFT JOIN weights AS e ON a.id=e.id AND e.date >= dateAdd("m", 9,
a.born)
GROUP BY a.id



is a possible solution.



Another one would be to use a crosstab, definitively simpler...


TRANSFORM MIN(b.weight)
SELECT a.id
FROM subjects AS a INNER JOIN weights AS b ON a.id=b.id
GROUP BY a.id
PIVOT 3*(datediff("m", a.born, b.date)\3) & "month"



Hoping it may help,
Vanderghast, Access MVP




Francisco said:
Good day to all,

I've a table with some records of events for subjects and on certain
dates,
now I need to get a query that returns me the Subject, Subject creation
date,
3 months after creation date, Value at 3 months, 6 months after
creation
date, Value at 6 months:

This is for example:

Table subject:

ID Name Born
1 Jon 6/6/95
2 Betty 8/12/95
3 Rudolf 15/01/96

Table wheight
Date subjectID Wheight
6/7/95 1 10
7/8/95 1 18
5/9/95 1 25
.................. (continue)
6/10/95 1 22
.................. (continue)
10/12/95 1 45
10/12/95 2 11
8/1/96 1 51
8/1/96 2 18
7/2/96 1 58
7/2/96 2 25
7/2/96 3 9
......... (continue)

Now I want to get a query that has this format:

ID Born Value aprox Value aprox
Value
aprox
3 Months 6 Months
9 Months
1 6/6/95 22 51
.....
2 8/12/95 ..... ....
.....
3 15/01/96 .... ....
.....



The dates where I recorded the events aren't exactly 3,6 and 9 months
after
the initial date, so it has to be aprox.


I know that I'll have to:
Select [tblsubject].IDsubject, [tblSubject].Dateborn, a.Value as
3Months,
b.Value as 6Months, c.Value as 9Months
...... and now I'm completely blank!!!


I hope someone can be of my assistance.

Sorry for the confusing question

Francisco T. B.
Universidade de Évora

P.S. - I hope this is not a duplicate question, I haven't found
anything
related.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subquery not integrating to query to display previous sum of quantities 3
Finding the right style field in VBA 0
Paragraph numbering in Word 1
type mismatch error in macro in excel 0
If Function 1
IF and VLOOKUP 2
Ranking (Look for previous ranking) 3
Countifs or a pivot 1

Top