Reformatting data using Access + Subquery

Question

I am trying to reformat the data in MS Access, as the number of rows exceeds what Excel can do. However, this is my first attempt with subqueries and can do with some help in what I should do.

I have data that looks like this:

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 translate 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 it above to then calculate the speed through (x2 - x1) / (t2 - t1).

Access can help with this? I was not too fussed about the need to use several tables / queries to achieve the final result ... Just as long as I get there.

Answer 1

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

Answer 2

You need JOIN data for yourself and get t1 and x1 to the left of the connection and t2 and x2 to the right, the trick is in the connection.

At first, the example data does not contain enough cases to see what is really required, so I go out and guess ...

• at time t, you record the progress x for some identifiers, and you want to work out dx / dt for each interval
• t increases each time, but not necessarily one
• t will be unique for each id
• x will come as soon 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 , at AS t1 , bt AS t2 , ax AS x1 , bx AS x2 , (bx - ax) / (bt - at) as Speed FROM Query1 as a INNER JOIN Query1 as b ON a.id = b.id AND (a.rank + 1) = b.rank 

and bob is your uncle

Answer 3

Can you use sql server? Or are you just trying to do this simply using Access / Excel? You can also do this with the TextPad macro if you want to get real simple and creative ... especially if the lines are sequential in that order.