How to get rows from 4 diff permitations??

[Yobbo]

Hi All

Wondered if you could help.

I have 2 tables of data like so:

PUPILS

PID PNAME
1 Fred1
2 Fred2
3 Fred3
4 Fred4

LEVELS

TID PID YEAR TERMID MATHSLEVEL READLEVEL
1 1 2006 0 W1 W2
2 2 2006 0 1B W4
3 3 2006 0 W1 W2
4 4 2006 0 2C 5A
5 1 2007 1 W1 W2
6 2 2007 1 1B W4
7 3 2007 1 W1 W2
8 4 2007 1 2C 5A
9 1 2007 2 W1 W2
10 2 2007 2 1B W4
11 3 2007 2 W1 W2
12 4 2007 2 2C 5A
13 1 2007 3 W1 W2
14 2 2007 3 1B W4
15 3 2007 3 W1 W2
16 4 2007 3 2C 5A

I link the 2 tables simply by joining the PIDs.

What I need to do is get 1 recordset of data that includes all rows with a
TERMID=0 and YEAR=2006 *and* a TERMID=1 and YEAR=2007 *and* a TERMID=2 and
YEAR=2007 *and* a TERMID=3 and YEAR=2007.

I can get the data quite easily if I do the above as 4 seperate queries, but
I need all of the rows relevant to these 4 permitations in 1 recordset.

I tried putting each permitation in brackets, but Access either didn't
like the brackets or brought back hundreds of duplicate rows.

Any ideas?

 

[Jeff Boyce]

Based on the LEVELS table you gave as an example, you don't have any rows
that have ALL of those condition (condition1 AND condition2 AND ...).

Try using "OR" between those conditions. You can use separate row in the
selection criteria area.

Don't use square brackets ("[]") around your selection criteria, as this
tells Access to offer up a prompt, displaying what's between the brackets.

If it were me, I'd start out using only a single set of conditions and
getting that to work first, then adding in one more set (on the next row
down in the query design view window), then ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[James A. Fortune]

Hi All

Wondered if you could help.

I have 2 tables of data like so:

PUPILS

PID PNAME
1 Fred1
2 Fred2
3 Fred3
4 Fred4

LEVELS

TID PID YEAR TERMID MATHSLEVEL READLEVEL
1 1 2006 0 W1 W2
2 2 2006 0 1B W4
3 3 2006 0 W1 W2
4 4 2006 0 2C 5A
5 1 2007 1 W1 W2
6 2 2007 1 1B W4
7 3 2007 1 W1 W2
8 4 2007 1 2C 5A
9 1 2007 2 W1 W2
10 2 2007 2 1B W4
11 3 2007 2 W1 W2
12 4 2007 2 2C 5A
13 1 2007 3 W1 W2
14 2 2007 3 1B W4
15 3 2007 3 W1 W2
16 4 2007 3 2C 5A

I link the 2 tables simply by joining the PIDs.

What I need to do is get 1 recordset of data that includes all rows with a
TERMID=0 and YEAR=2006 *and* a TERMID=1 and YEAR=2007 *and* a TERMID=2 and
YEAR=2007 *and* a TERMID=3 and YEAR=2007.

I can get the data quite easily if I do the above as 4 seperate queries, but
I need all of the rows relevant to these 4 permitations in 1 recordset.

I tried putting each permitation in brackets, but Access either didn't
like the brackets or brought back hundreds of duplicate rows.

Any ideas?

You don't indicate whether the TERMID's keep going up or repeat each
year. Your query seems to indicate the possibility of repetition of
TERMID's.

If the TERMID's keep going up, simply use:

SELECT * FROM (PUPILS INNER JOIN LEVELS ON PUPILS.PID = LEVELS.PID)
WHERE TERMID BETWEEN 0 AND 3;

or

SELECT * FROM (PUPILS INNER JOIN LEVELS ON PUPILS.PID = LEVELS.PID)
WHERE (TERMID=0 and YEAR=2006) Or (TERMID=1 and YEAR=2007) Or (TERMID=2
and YEAR=2007) Or (TERMID=3 and YEAR=2007);

as Jeff implied. Note: The queries shown are the text of the SQL View
of the query.

If there are only four TERMID's, each roughly equivalent to a 'quarter,'
then it would be nice to use the DateSerial function to convert
everything into a date range:

For example,

TERMID=0 => '10, 1)'
TERMID=1 => '1, 1)'
TERMID=2 => '4, 1)'
TERMID=3 => '7, 1)'

Note: It is not important that the actual quarters start exactly on the
dates given.

TERMID Y
0 -> 10
1 -> 1
2 -> 4
3 -> 7

if
X Y
1 -> 1
2 -> 4
3 -> 7
4 -> 10

Y = 3 * X - 2

so the first part needs to be adjusted:

1 -> 1 -> 1
2 -> 2 -> 4
3 -> 3 -> 7
0 -> 4 -> 10

IIf(x = 0, 4, X):

1 -> 1
2 -> 2
3 -> 3
0 -> 4

3 * IIf(x = 0, 4, X) - 2

0 -> 10
1 -> 1
2 -> 4
3 -> 7

SELECT * FROM (PUPILS INNER JOIN LEVELS ON PUPILS.PID = LEVELS.PID)
WHERE DateSerial([LEVELS].[Year], 3 * IIf([LEVELS].[TERMID] = 0, 4,
[LEVELS].[TERMID]) - 2, 1) BETWEEN DateSerial(2006, 10, 1) AND
DateSerial(2007, 7, 1) ORDER BY LEVELS.PID, Year, TERMID;

will produce the 16 records you show (TERMID's 0 through 3).

SELECT * FROM (PUPILS INNER JOIN LEVELS ON PUPILS.PID = LEVELS.PID)
WHERE DateSerial([LEVELS].[Year], 3 * IIf([LEVELS].[TERMID] = 0, 4,
[LEVELS].[TERMID]) - 2, 1) BETWEEN DateSerial(2006, 10, 1) AND
DateSerial(2007, 4, 1) ORDER BY LEVELS.PID, Year, TERMID;

will produce the 12 records from TERMID's 0, 1 and 2.

James A. Fortune
(e-mail address removed)

 

[Yobbo]

Many thanks

This is brill!!!

Hi All

Wondered if you could help.

I have 2 tables of data like so:

PUPILS

PID PNAME
1 Fred1
2 Fred2
3 Fred3
4 Fred4

LEVELS

TID PID YEAR TERMID MATHSLEVEL READLEVEL
1 1 2006 0 W1 W2
2 2 2006 0 1B W4
3 3 2006 0 W1 W2
4 4 2006 0 2C 5A
5 1 2007 1 W1 W2
6 2 2007 1 1B W4
7 3 2007 1 W1 W2
8 4 2007 1 2C 5A
9 1 2007 2 W1 W2
10 2 2007 2 1B W4
11 3 2007 2 W1 W2
12 4 2007 2 2C 5A
13 1 2007 3 W1 W2
14 2 2007 3 1B W4
15 3 2007 3 W1 W2
16 4 2007 3 2C 5A

I link the 2 tables simply by joining the PIDs.

What I need to do is get 1 recordset of data that includes all rows with a
TERMID=0 and YEAR=2006 *and* a TERMID=1 and YEAR=2007 *and* a TERMID=2 and
YEAR=2007 *and* a TERMID=3 and YEAR=2007.

I can get the data quite easily if I do the above as 4 seperate queries,
but
I need all of the rows relevant to these 4 permitations in 1 recordset.

I tried putting each permitation in brackets, but Access either didn't
like the brackets or brought back hundreds of duplicate rows.

Any ideas?

You don't indicate whether the TERMID's keep going up or repeat each
year. Your query seems to indicate the possibility of repetition of
TERMID's.

If the TERMID's keep going up, simply use:

SELECT * FROM (PUPILS INNER JOIN LEVELS ON PUPILS.PID = LEVELS.PID)
WHERE TERMID BETWEEN 0 AND 3;

or

SELECT * FROM (PUPILS INNER JOIN LEVELS ON PUPILS.PID = LEVELS.PID)
WHERE (TERMID=0 and YEAR=2006) Or (TERMID=1 and YEAR=2007) Or (TERMID=2
and YEAR=2007) Or (TERMID=3 and YEAR=2007);

as Jeff implied. Note: The queries shown are the text of the SQL View
of the query.

If there are only four TERMID's, each roughly equivalent to a 'quarter,'
then it would be nice to use the DateSerial function to convert
everything into a date range:

For example,

TERMID=0 => '10, 1)'
TERMID=1 => '1, 1)'
TERMID=2 => '4, 1)'
TERMID=3 => '7, 1)'

Note: It is not important that the actual quarters start exactly on the
dates given.

TERMID Y
0 -> 10
1 -> 1
2 -> 4
3 -> 7

if
X Y
1 -> 1
2 -> 4
3 -> 7
4 -> 10

Y = 3 * X - 2

so the first part needs to be adjusted:

1 -> 1 -> 1
2 -> 2 -> 4
3 -> 3 -> 7
0 -> 4 -> 10

IIf(x = 0, 4, X):

1 -> 1
2 -> 2
3 -> 3
0 -> 4

3 * IIf(x = 0, 4, X) - 2

0 -> 10
1 -> 1
2 -> 4
3 -> 7

SELECT * FROM (PUPILS INNER JOIN LEVELS ON PUPILS.PID = LEVELS.PID)
WHERE DateSerial([LEVELS].[Year], 3 * IIf([LEVELS].[TERMID] = 0, 4,
[LEVELS].[TERMID]) - 2, 1) BETWEEN DateSerial(2006, 10, 1) AND
DateSerial(2007, 7, 1) ORDER BY LEVELS.PID, Year, TERMID;

will produce the 16 records you show (TERMID's 0 through 3).

SELECT * FROM (PUPILS INNER JOIN LEVELS ON PUPILS.PID = LEVELS.PID)
WHERE DateSerial([LEVELS].[Year], 3 * IIf([LEVELS].[TERMID] = 0, 4,
[LEVELS].[TERMID]) - 2, 1) BETWEEN DateSerial(2006, 10, 1) AND
DateSerial(2007, 4, 1) ORDER BY LEVELS.PID, Year, TERMID;

will produce the 12 records from TERMID's 0, 1 and 2.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Many thanks

This is brill!!!

Not quite! Maybe:

ORDER BY LEVELS.PID, Year, IIf(TERMID = 0, 4, TERMID);

since TERMID = 0 is at the end of the year. This would have shown up
after another term.

James A. Fortune
(e-mail address removed)