在Microsoft Access中的查询(视图)方面需要帮助 [英] Need Help with queries (views) in Microsoft Access

问题描述

我有一个应用程序，它将存储库存中物品的垃圾箱的重量记录到Microsoft Access Table中，如下所示:

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

请注意，体重可能每天都会变化.有时重量几天都不会改变.我想输出一个仅列出重量变化的报告，如果重量不变，则不重复该行，就像这样(不需要ID字段):

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

我可以在VB或访问模块中使用代码来执行此操作，将其写入临时表等.但是有没有办法仅使用SQL查询(视图)(包括子查询)来执行此操作?我认为我应该能够使用GROUP BY子句编写一个查询，以获取"FIRST"项，并使用另一个查询与第一个查询联接，但是我有一种感觉，如果不编写一些代码，这是无法完成的. (此应用程序将在网站上-多个用户使用临时表的访问效果不佳)

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)

推荐答案

我认为您无法使用timestamp列进行订购，因为据我所知 它不提供密钥.给定相同timestamp的多个netwt值，例如

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

如果没有id列，您将无法知道订单.

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

按现状显示，id列似乎显示了顺序，但这可能是不安全的假设(例如，可能INSERT显式的id值可能不按顺序排列).

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是Access数据库工程，SQL标准SQL-92，ODBC，SQL Server Future等的保留字，因此应避免作为数据元素名称.]

[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.]

以下内容使用id列进行判断，以确定下一个" netwt值是否不同:

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
              );

这篇关于在Microsoft Access中的查询(视图)方面需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！