Joining or combining two tables (1 Viewer)

[escuro19]

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)

+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

 

[Galaxiom]

I don't really understand but it appears Table2 should not even be a table. The derived values are simple mathematical relationships. I don't see any relationsip between the intday and the second table.

 

[escuro19]

Thankyou for reply

Yes you are right, theres no connection in terms of relationship between table1 and table2.

I really just want Table1 with the data range/scale from table2.. and table2 doesnt have to be a table. Is there another way i could achieve the output above?

 

[MSAccessRookie]

In order to do what you want, you will need to link the Records from Table1 with the Records from Table2. Since there is no obvious relationship between Table 1 and Table 2, it is difficult to determine whether or not this can be done. I suspect that it cannot be done with the current Table structures.

NOTE: I missed your comment to GalaxiomAtHome. Since there is "no connection in terms of relationship between table1 and table2", I suspect that it cannot be done with the current Table structures.

 

[Galaxiom]

It would appear that the scheme is based on the lowest intday having the lowest +day and -day values. (Not good field names BTW, would be hard to read in an expression that used arithmetic operators.)

Add a simple numbered field to Table2 with records counting up from zero.

Create a query to return the Min intday Grouped By Person.
Create a query joining Table1 to this query on Person and derive a field:
intday - [Min of intday]

Join this query to Table2 on this new field and the simple numbered field in Table2.
Also looks like you could omit the -day since it is simply:
[+day] - 15 (see what I mean about the names?)

Later you can condense it all into subqueries but this will get you going in the designer.

 

[escuro19]

Thankyou for your replies,

Thanks for the tips GalaxiomAtHome, i managed to get it working from ur guide.