Joining or Combining two tables

[escuro19 via AccessMonster.com]

Hi,

I spend alot of time trying to work this out but failed so please could
someone help.

Data:
(this table the intday varies depending on the person)

Table1
Person | intDay
1 | 22
1 | 23
1 | 24
1 | 25
2 | 14
2 | 15
2 | 16
2 | 17

Table2
(this data is fixed, its like a scale)

Table2
+day | -day
8 | -7
9 | -6
10 | -5
11 | -4
12 | -3
13 | -2
15 | -1
n | n

This is what i want the output to be:

Person | intDay | +day | -day

1 | 22 | 8 | -7
1 | 23 | 9 | -6
1 | 24 |10 | -5
1 | 25 | 11 | -4
2 | 14 | 8 | -7
2 | 15 | 9 | -6
2 | 16 | 10 | -5
2 | 17 |11 | -4

So basically matching table1 with table2, but for each person the table2
matches resets. And also is it possible to stop matching once the Person's
intday stops.

Thankyou for your help

 

[Tom van Stiphout]

On Thu, 12 Nov 2009 09:49:41 GMT, "escuro19 via AccessMonster.com"

That's not a job for SQL but for a new "temporary" table, VBA, and
recordsets.
This code could then also ensure you're not running out of Table2
data.

-Tom.
Microsoft Access MVP

 

[vanderghast]

Why 1 | 22 matched 8 | -7 and not , say, 15 | -1 ?


Do you mean, the 'first' (when order on intDay) 'matches' the 'first' or
the table2 (order by [+day] ) ?

Make three queries. The first one:

SELECT a.person,
a.intDay,
b.[+day] AS bdayp,
a.intDay-b.[+day] as offset,
LAST(b.[-day]) AS bdaym
FROM table1 As a, table2 AS b
GROUP BY a.person, a.intDay, b.[+day], a.intDay-b.[+day]

saved as q1.

The second query will only keep the MAXimum value for the offset, for each
person, for its first day:


SELECT person, MAX(offset) as mOffset
FROM q1
WHERE intDay=(SELECT MIN(table1.intDay)
FROM table1
WHERE table1.person=q1.intDay)
GROUP BY person


saved as q2

And the last query join the last two queries together:

SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.person = q2.person
AND q1.offset = q2.maxOffset



Another solution would be to rank each tables (table1, rank by person, over
intDay, table2, rank over all [+day]), and to join over the obtained rank.



Vanderghast, Access MVP

 

[escuro19 via AccessMonster.com]

Thankyou for reply,

To answer your question, you are exactly right.

I copied your example but there seem to be a problem with q2? when clicked on
the query "Data type mismatch in criteria expression" message. hence q3
doesnt work.

escuro19