Whats wrong with my query

[steenman2]

I have a query that is giving me an error. "Join expression not
supported."

I originally got the format for the query from someone on the board.
What it is supposed to do is take a list of value (column) and subtract
a value from the one above it. It then returns that value in a field
called results right next to the value field in the same table.
(example below). Each Value is associated with a unique ID and are in
order (ie no gaps).

Value(s) Result(s)
-70
-70 0
-68.98 1.02
-67.89 1.09
-71 2.11
-69.29 .71
-67.77 1.52

Notice I am returning the result on the second row of the two rows I am
subrtacting. I am also returning the absolute value. (thats not a
problem).

Here is the code I am using, and below is the code that someone on here
gave me. I am sure thiers is right I just didn't implement it right.

[System Dewpoints].[Dewpoint],[System Dewpoints].Dewpoints - [System
Dewpoints].Dewpoint AS Result
FROM YourTable as [System Dewpoints] LEFT JOIN YourTable as T1
ON [System Dewpoints].ID = ([System Dewpoints].ID -1 )

T.Value, T.Value - T1.Value as Result
FROM YourTable as T LEFT JOIN YourTable as T1
ON T.ID = (T1.ID -1 )


Thanks for any help.

 

[Gary Walter]

"steenman2" <wrote:

I have a query that is giving me an error. "Join expression not
supported."

I originally got the format for the query from someone on the board.
What it is supposed to do is take a list of value (column) and subtract
a value from the one above it. It then returns that value in a field
called results right next to the value field in the same table.
(example below). Each Value is associated with a unique ID and are in
order (ie no gaps).

Value(s) Result(s)
-70
-70 0
-68.98 1.02
-67.89 1.09
-71 2.11
-69.29 .71
-67.77 1.52

Notice I am returning the result on the second row of the two rows I am
subrtacting. I am also returning the absolute value. (thats not a
problem).

Here is the code I am using, and below is the code that someone on here
gave me. I am sure thiers is right I just didn't implement it right.

[System Dewpoints].[Dewpoint],[System Dewpoints].Dewpoints - [System
Dewpoints].Dewpoint AS Result
FROM YourTable as [System Dewpoints] LEFT JOIN YourTable as T1
ON [System Dewpoints].ID = ([System Dewpoints].ID -1 )

T.Value, T.Value - T1.Value as Result
FROM YourTable as T LEFT JOIN YourTable as T1
ON T.ID = (T1.ID -1 )

I think they expected you to replace
"YourTable" with actual name of
your table "[Systems DewPoints]"

but keep the "T" and "T1" aliases

T.Value, T.Value - T1.Value as Result
FROM [System Dewpoints] as T
LEFT JOIN
[System Dewpoints] as T1
ON T.ID = (T1.ID -1 )

 

[Gary Walter]

forgot to swap out "Value" ...sorry

SELECT
T.DewPoint,
Abs(T.DewPoint - T1.DewPoint) as Result
FROM [System Dewpoints] as T
LEFT JOIN
[System Dewpoints] as T1
ON T.ID = (T1.ID -1 )

if the above no work,
list out names of fields
in your table

 

[[MVP] S.Clark]

This is not a valid SQL statement. There's no SELECT, and there are two
FROMs.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[steenman2]

Do you have any ideas of what I might try?

Any help would be appreciated.

 

[[MVP] S.Clark]

Not without knowing your table and field names, and how they relate.

 

[steenman2]

What it is supposed to do is take a list of value (column) and subtract
a value from the one above it. It then returns that value in a field
called results right next to the value field in the same table.
(example below). Each Value is associated with a unique ID and are in
order (ie no gaps).

ID Value(s) Result(s) UCL LCL
Average
1 -70 2
0 1
2 -70 0 2
0 1
3 -68.98 1.02 2 0
1
4 -67.89 1.09 2 0
1
5 -71 2.11 2 0
1
6 -69.29 .71 2 0
1
7 -67.77 1.52 2 0
1

Notice I am returning the result on the second row of the two rows I am
subrtacting. I am also returning the absolute value. (thats not a
problem).

There is no calculation being done with the UCL, LCL, and Average. I am
returning them because I am using the query to generate a table.

This DB only has one table called [Points].
So my fields are [Points].ID, [Points].Value, [Points].Results,
[Points].UCL, [Points].LCL, [Points].Average

 

[John Spencer]

Assuming NO gaps in the ID numbering then the following might be what you
want.

SELECT P.*, P.Value - P1.Value as Result
FROM Points as P LEFT JOIN Points as P1
ON P.ID = P1.ID+1

You can only do this type of SQL join in the SQL view, not in the query
grid. You can set up the query in the grid and then modify it in the SQL
view.

Add your table to the query grid two times (one will be named Points and
the second will be named Points_1)
Join the tables on the ID field (drag from one to the other)
Click on the join line and choose all Records from Points and only ... from
Points_1
Select View: SQL View
In the text find the text that looks like
On Points.ID = Points_1.ID and change it to
On Points.ID = Points_1.ID + 1

This DB only has one table called [Points].
So my fields are [Points].ID, [Points].Value, [Points].Results,
[Points].UCL, [Points].LCL, [Points].Average

What it is supposed to do is take a list of value (column) and subtract

a value from the one above it. It then returns that value in a field
called results right next to the value field in the same table.
(example below). Each Value is associated with a unique ID and are in
order (ie no gaps).

ID Value(s) Result(s) UCL LCL
Average
1 -70 2
0 1
2 -70 0 2
0 1
3 -68.98 1.02 2 0
1
4 -67.89 1.09 2 0
1
5 -71 2.11 2 0
1
6 -69.29 .71 2 0
1
7 -67.77 1.52 2 0
1

Notice I am returning the result on the second row of the two rows I am
subrtacting. I am also returning the absolute value. (thats not a
problem).

There is no calculation being done with the UCL, LCL, and Average. I am
returning them because I am using the query to generate a table.

 

[steenman2]

Thanks it works like a gem!!!