Combining/Joining a range(scale) of data to a table

[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

And below is the data which i want to combine with the table above. (the data
range always starts at 8 and -7 and should continue depending on how many
inDay a person have)

(data range/scale)
+day | -day
8 | -7
9 | -6
10 | -5
11 | -4
12 | -3
13 | -2
15 | -1
16 | 0
17 | 1
18 | 2

This is what i want the output to be(output to a table or report format):

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 for each person in the table, 'the data range/scale' matches
with it. And also is it possible to stop matching once the Person's intday
stops.

Thankyou for your help

 

[Daryl S]

Escuro19:

It looks like the everything is based on the first intDay for each person.
In that case, create a query that pulls that first intDay by person:

SELECT Table1.Person, Min(Table1.intDay) AS MinOfintDat
FROM Table1
GROUP BY Table1.Person;

Then use this query with the other two tables to drive the results as
follows (assuming the first query is named MinIntDatePerPerson):

SELECT Table1.Person, Table1.intDay, Table2.[+day], Table2.[-day]
FROM Table2, Table1 INNER JOIN MinIntDatePerPerson ON Table1.Person =
MinIntDatePerPerson.Person
WHERE (((Table2.[+day])=[intDay]-[MinOfintDay]+8));

 

[escuro19 via AccessMonster.com]

Thankyou Daryl S

Your example works great! big weight off my shoulders now. thanks for taking
time to reply