Differance trend query

  • Thread starter Thread starter MoyAA
  • Start date Start date
M

MoyAA

I'm sure that I am re-asking and amateur question, but I still can't
find that answer to making a query that will show how my data changes
from one record to another. I have a simple table to record how many
units are needing corrective action at the beginning of each shift.
We are 24/7 with three shift and some shifts are better at clearing
the product than others, and I would like to be able to query it for
graphing purposes. I just can't figure how to subtract last shifts
counts from this shifts counts to see if it went up or down. The
table has these fields.

TYIA
Moy

ID Date Shift 1H414 1H418 1H214 1H218
1 13-Jan 1 0 4 0 1
2 13-Jan 2 6 5 0 2
3 13-Jan 3 0 0 0 1
4 14-Jan 1 0 1 0 2
5 14-Jan 2 0 5 0 3
6 14-Jan 3 0 0 0 0
7 15-Jan 1 0 3 2 0
8 15-Jan 2 5 3 1 0
9 15-Jan 3 0 1 0 0
10 16-Jan 1 0 7 0 0
11 16-Jan 2 3 7 0 1
12 16-Jan 3 0 0 0 0
13 17-Jan 1 5 1 0 1
14 17-Jan 2 11 0 0 1
15 17-Jan 3 0 0 0 0
16 18-Jan 1 1 0 0 0
17 18-Jan 2 5 0 0 0
18 18-Jan 3 0 0 0 0
19 19-Jan 1 4 1 0 0
20 19-Jan 2 12 5 3 0
21 19-Jan 3 0 1 0 0
22 20-Jan 1 0 0 0 0
23 20-Jan 2 12 0 0 0
24 20-Jan 3 0 0 0 0
25 21-Jan 1 15 1 0 0
26 21-Jan 2 11 7 1 0
27 21-Jan 3 0 0 0 0
28 22-Jan 1 0 0 3 1
29 22-Jan 2 0 1 4 0
30 22-Jan 3 0 0 0 0
31 23-Jan 1 12 0 3 1
32 23-Jan 2 7 0 1 0
33 23-Jan 3 0 0 0 0
34 24-Jan 1 0 0 0 0
35 24-Jan 2 2 4 3 0
36 24-Jan 3 0 0 0 0
37 25-Jan 1 0 0 0 0
38 25-Jan 2 0 4 0 0
39 25-Jan 3 0 0 0 0
40 26-Jan 1 7 2 2 0
41 26-Jan 2 19 4 3 0
42 26-Jan 3 2 2 0 0


sorry for the earlier BAD post
 
I'm sure that I am re-asking and amateur question, but I still can't
find that answer to making a query that will show how my data changes
from one record to another.
Click to expand...

That's because a Table *is not a spreadsheet*.

A Table should be viewed as an unsorted bucket of records. There IS no such
concept as "the next record" or "the previous record".
I have a simple table to record how many
units are needing corrective action at the beginning of each shift.
We are 24/7 with three shift and some shifts are better at clearing
the product than others, and I would like to be able to query it for
graphing purposes. I just can't figure how to subtract last shifts
counts from this shifts counts to see if it went up or down. The
table has these fields.

TYIA
Moy

ID Date Shift 1H414 1H418 1H214 1H218
Click to expand...

First off, you should change the name of the field Date. It's a reserved word
for the built in Date function, and Access WILL get confused.

You can create a Self Join query joining the table to itself. Your join clause
will be complex since you have nonunique dates; you can't really count on the
ID being strictly sequential if it is (as I assume) an Autonumber. Assuming
that every day and every shift is going to be present, try:

SELECT A.[Date], A.Shift, B.[Date], B.Shift, A.[1H414] - B.[1H414] AS Diff414,
A.[1H418] - B.[1H418] AS Diff418, <etc>
FROM table AS A INNER JOIN table AS B
ON B.Date = DateAdd("d", IIF(A.Shift] = 1, -1, 0), A.[Date])
AND B.Shift = IIf([A].[Shift] = 1, 3, [A].[Shift] - 1);
 
I'm sure that I am re-asking and amateur question, but I still can't
find that answer to making aquerythat will show how my data changes
from one record to another.
Click to expand...

That's because a Table *is not a spreadsheet*.

A Table should be viewed as an unsorted bucket of records. There IS no such
concept as "the next record" or "the previous record".
I have a simple table to record how many
units are needing corrective action at the beginning of each shift.
We are 24/7 with three shift and some shifts are better at clearing
the product than others, and I would like to be able toqueryit for
graphing purposes. �I just can't figure how to subtract last shifts
counts from this shifts counts to see if it went up or down. �The
table has these fields.

ID �Date � �Shift � 1H414 � 1H418� 1H214 � 1H218
Click to expand...

First off, you should change the name of the field Date. It's a reserved word
for the built in Date function, and Access WILL get confused.

You can create a Self Joinqueryjoining the table to itself. Your join clause
will be complex since you have nonunique dates; you can't really count on the
ID being strictly sequential if it is (as I assume) an Autonumber. Assuming
that every day and every shift is going to be present, try:

SELECT A.[Date], A.Shift, B.[Date], B.Shift, A.[1H414] - B.[1H414] AS Diff414,
A.[1H418] - B.[1H418] AS Diff418, <etc>
FROM table AS A INNER JOIN table AS B
ON B.Date = DateAdd("d", IIF(A.Shift] = 1, -1, 0), A.[Date])
AND B.Shift = IIf([A].[Shift] = 1, 3, [A].[Shift] - 1);
Click to expand...

I sorry but I can not make your query to work.

The ID is VERY sequential. All the records for the next year were
created with Excel and loaded into Access. The table is undated with
a form that EDITs exsisting records. All I need to know is how to
subtrack the '1H414' field of the records with ID 26 and 27 (for
example).

Moy
 
Back
Top