This type of problem is something that a spreadsheet does much more
easily than a database. If it's a one-time problem, you might consider
moving it to a spreadsheet, making the calculations, and then moving
the results back to the database.
If it's an on-going problem, here is a method to consider. I have used
it successfully several times. However it RELIES on the condition that
the ID numbers are sequential.
The concept is that you can make a query with two copies of your
table, that are related on the ID field. Once you have two instances
of the tables in the query, you can refer to the fields from both
instances, and do the necessary arithmetic. The trick is to get one of
the table instances to be (say) the record where ID =1, and the second
instance to be where ID=2. Here's how I do it:
Using the query grid, drag one of the tables into the query.
Right-click on the table in the query and choose Properties. Change
the Alias of the query. I typically use a name like "First"
Drag the second table onto the query. Change its Alias to "Second".
Using Aliases is not required, but it does make it easier for you to
keep track of which table is which.
Drag the ID field from "First" to "Second".
Add the other necessary fields from both tables. Add a calculated
field to do the required arithmetic.
You cannot complete the next step from the query grid. You must open
the query in SQL view. Look through the SQL statement to find the
joining clause. It will look like this:
.... ON First.ID = Second.ID ...
Change it to read:
.... ON First.ID+1 = Second.ID ...
Run the query.
When I use this technique, I generally set up all the necessary fields
in the query grid, and then use the "edit the SQL" trick at the last
moment. You can edit the join clause BACK to its original form so you
can manipulate the query using the query grid. Or if you are
comfortable with SQL, you can modify the query directly in the SQL
view.
I have a table contain of 3 field like this (All data is number)
ID Ind Adj
1 I1 A1
2 I2 A2
3 I3 A3
4 I4 A4
In fact Ihave n record and I want to get the result like this
IDnew Dev
1 I2 - A1
2 I3 - A2
3 I4 - A3
Dev = Difference value between ind and adj, but it is not in a same record
How can i get it . Help me please, It is a big problem for me
**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
