Need Help with queries (views) in Microsoft Access

Question

I have an application that records weight of a bin holding items in inventory into a Microsoft Access Table Like so:

Id(AutoNumber)  Timestamp       Weight
-------------------------------------
1                Jan 1           1
2                Jan 2           1

3                Jan 3           2
4                Jan 4           2
5                Jan 5           2

6                Jan 6           3
7                Jan 7           3
8                Jan 7           3

9                Jan 8           2
10               Jan 8           2

11               Jan 9           7

12               Jan 10          4
13               Jan 10          4

Note that weight may change from day to day. Sometimes the weight will not change for days. I want to output a report listing only the weight when it has changed and not repeat the row if the weight does not change, like so (ID field not necessary):

Timestamp       Weight
----------------------
  Jan 1           1

  Jan 3           2

  Jan 6           3

  Jan 8           2

  Jan 9           7

  Jan 10          4

I can do this using code either in VB or in an Access Module, writing it to a temp table etc. But is there a way to do this just using SQL queries (views), including subqueries? I am thinking I should be able to write one query using GROUP By clause to get the 'FIRST' item and another query to join with the first one, but have this feeling this cannot be done at all without writing some code. (This application will be on a web site - Access is not good with temporary tables for multiple users)

Answer 1

select o.dt, o.value from table1   o
where not exists (select top 1 t.dt, t.value from table1 t where t.dt < o.dt )
union 
select o.dt, o.value from table1   o
where exists (select top 1 t.dt, t.value from table1 t where t.dt = (o.dt - 1) and 
t.value <> o.value)

Note: I am assuming dt as the datetime field, value as the weight field.
You can base your query off this.

Hope this helps.

Answer 2

Not enough space in comments so I am giving the results here.

Thank you for the quick response but does not work as expected, some data is missing,

This is the data

id          timestamp         netwt

---

7975    07/15/2009 1:17:52 PM 1.193
78936   07/17/2009 10:37:48 AM 0.44  ---> missing from query results
78959   07/17/2009 10:40:48 AM 0.44  ---> missing from query results
78987   07/17/2009 10:41:48 AM 0.44
79005   07/17/2009 10:41:48 AM 0.439
79022   07/17/2009 10:42:48 AM 0.443
79042   07/17/2009 10:43:47 AM 0.446
79073   07/17/2009 10:44:47 AM 0.448
79094   07/17/2009 10:45:47 AM 0.448
79128   07/17/2009 10:46:47 AM 0.45
79157   07/17/2009 10:47:47 AM 0.45
79177   07/17/2009 10:48:47 AM 0.45
79208   07/17/2009 10:49:47 AM 0.448
79229   07/17/2009 10:50:47 AM 0.449
79248   07/17/2009 10:51:47 AM 0.447
79277   07/17/2009 10:52:47 AM 0.447
79302   07/17/2009 10:53:47 AM 0.445
79304   07/17/2009 10:53:47 AM 0.446
79331   07/17/2009 10:54:47 AM 0.448
79352   07/17/2009 10:55:48 AM 0.448
79366   07/17/2009 10:55:48 AM 0.448
79393   07/17/2009 10:56:48 AM 0.447
79415   07/17/2009 10:57:48 AM 0.443
79449   07/17/2009 10:58:48 AM 0.443
79476   07/17/2009 10:59:48 AM 0.442
79488   07/17/2009 10:59:48 AM 0.442
79509   07/17/2009 11:00:48 AM 0.438
79540   07/17/2009 11:01:48 AM 0.439
79559   07/17/2009 11:02:48 AM 0.439
79582   07/17/2009 11:03:48 AM 0.439
...
...

This is the result of running the query

timestamp               netwt
-------------------------------
07/15/2009 1:17:52 PM   1.193
07/18/2009 10:43:47 AM  0.448
07/18/2009 10:44:47 AM  0.45
07/18/2009 10:45:47 AM  0.446
07/18/2009 10:48:47 AM  0.447
07/18/2009 10:49:47 AM  0.451
07/18/2009 10:52:47 AM  0.45
07/18/2009 10:53:47 AM  0.453
07/18/2009 10:54:47 AM  0.453
07/18/2009 10:55:48 AM  0.452
07/18/2009 10:56:48 AM  0.448
07/18/2009 11:15:48 AM  0.454
07/18/2009 11:16:48 AM  0.451
...
...

I simply cut and pasted your query, changed the field names and ran it..

Answer 3

I don't think you can use your timestamp column for ordering because as far as I can tell it doesn't provide a key. Given multiple netwt values for the same timestamp e.g.

80001   '2009-07-22 09:28:23'   0.55
80002   '2009-07-22 09:28:23'   0.22
80003   '2009-07-22 09:28:23'   0.99

you can't know the order without the id column.

As it stands the id column seems to show the order but this is possibly an unsafe assumption (e.g. it is possible to INSERT an explicit id value which could be out of sequence).

[BTW TIMESTAMP is a reserved word for the Access database enging, SQL Standards SQL-92, ODBC, SQL Server Future, etc and should therefore be avoided as a data element name.]

The following uses the id column for odering to determine whether the 'next' netwt value is different:

SELECT T3.id, T3.[timestamp], T3.netwt
  FROM table1 AS T3
 WHERE EXISTS (
               SELECT MIN(T2.id)
                 FROM table1 AS T1, table1 AS T2
                WHERE T1.id < T2.id
                      AND T1.netwt <> T2.netwt
                GROUP 
                   BY T1.id
               HAVING MIN(T2.id) = T3.id
              );