Reformatting data using Access + Subquery

Question

I'm trying to reformat data in MS Access as the number of rows exceeds what Excel can do. However, this is my first attempt with subqueries, and could do with some assistance in what I should be doing.

I have data that looks like the following:

t, id, x
1, 10, 1
1, 20, 5
1, 30, 10
2, 10, 2
2, 20, 7
2, 30, 14

and I'm trying to mung it into this order:

id, t1, t2, x1, x2
10, 1,  2,  1,  2
20, 1,  2,  5,  7
30, 1,  2,  10, 14
... 
10, 70, 71, 66, 68

etc.

The data presented are distances and time values -- so I want to reformat the above to then calculate speed through (x2 - x1) / (t2 - t1).

Is this something that Access can assist with? I'm not too fussed about having to use multiple tables / queries to get to the end result... Just so long as I get there!!!

So far, mostly logic!! Here are my initial thoughts: (1.) Sort the data by id, then t (2.) If the id value at t+1 does not equal the id value at t, this should give the values for t2 and x2 (3.) If the id value at t-1 does not equal the id value at t, this should give the values for t1 and x1 Does my logic stand up to scrutiny? — James, Dec 13 '11 at 14:39
Are they always odd and even e.g. t = 1 paired with t = 2.. t = 69 with t = 70? — Tony Hopkinson, Dec 13 '11 at 22:13
@TonyHopkinson The pairings are not always odd and even, but will not equal one another. E.g. on rows 1 through 5, the t value could be 1, but on rows 6 through 12 the t value could be 7. Make sense? — James, Dec 14 '11 at 12:08
Some more data please Join t=1 to t = t + 1 is doable, but that would do t = 2 to t = 3 as well, if they exist? — Tony Hopkinson, Dec 14 '11 at 18:00

score 1 · Answer 1 · answered Feb 06 '13 at 18:06

1

create a crosstab query from your original data... then use another query to select from the crosstab query into another table.

answered Feb 06 '13 at 18:06

sqlagent

11
1

score 0 · Answer 2 · answered Dec 14 '11 at 21:45

You need to JOIN the data to itself and get t1 and x1 from the left of the join and t2 and x2 from the right, the trick is in the join.

First the sample data doesn't really cover enough cases to see what's really required, so I'm inferring and guessing...

at time t you record the progress x for some ids, and you want to work out dx/dt for each interval
t increments each time, but not necessarily by one
t is going to be unique for each id
x will come as it comes.

First I need the rows ranked sequentially

SELECT 
    (
        SELECT Count(*) 
        FROM Table1
        WHERE [t] < [t1].[t]+1 AND id = t1.id;
    ) AS Rank
    , *
FROM
    Table1 AS t1;

Call this Query1 and

SELECT 
      a.id AS id
    , a.t AS t1
    , b.t AS t2
    , a.x AS x1
    , b.x AS x2
    , (b.x - a.x) / (b.t - a.t) as Speed
FROM
    Query1 as a
        INNER JOIN
    Query1 as b
        ON
                a.id = b.id 
            AND (a.rank + 1) = b.rank

and bob's your uncle

score 0 · Answer 3 · edited Jan 03 '12 at 17:33

0

Can you use sql server? Or are you just trying to do this with just using Access/Excel? You can possibly do this with TextPad macro as well if you want to get real simple and creative... especially if the rows are consistently in that order.

edited Jan 03 '12 at 17:33

Devin Burke

13,642
12
55
82

answered Jan 03 '12 at 17:30

sam yi

4,806
1
29
40

Reformatting data using Access + Subquery

3 Answers3