如何在Access中创建时间序列并可以在查询中使用它 [英] How to create a time series in Access and can use it in a query

问题描述

事实:

我使用Access 2013，但是它也必须在Access 2007中运行.

I use Access 2013, but it must be run in Access 2007 aswell.

我有以下3张桌子:

订单:

Id      DateFrom        DateTo  
1       2014-12-01      2015-03-01
2       2014-01-02      2015-03-01
3       2015-01-03      2015-03-01

库存:

Id      Label       Amount
1       Product1    20
2       Product2    10 

OrderStock:

OrderStock:

Id      OrderId     StockId     Amount
1       1           1           10
2       2           1           5
3       2           2           5
4       3           2           5

用户输入:

• DateFrom:2015-01-01
• DateTo:2015年1月3日
• StockIds:1和2

问题:

是否可以为日期为X到Y的子查询创建临时"表?在用户输入中，如下所示:

It's possible to create a 'temporary' table for a sub-query with dates from X to Y? From the user input, which would look like this:

dates
2015-01-01
2015-01-02
2015-01-03

我希望得到以下结果:

Date        StockLabel  AmountInUse     AmountAvailable
2015-01-01  Product1    10              10
2015-01-01  Product2    0               10
2015-01-02  Product1    15              5
2015-01-02  Product2    5               5
2015-01-03  Product1    15              5
2015-01-03  Product2    10              0

如果在没有VBA的Access中甚至可以进行查询，查询的外观如何?

How would the query look like, if it is even possible in Access without VBA?

推荐答案

是.创建这样的查询:

SELECT DISTINCT 
    [Tens]+[Ones] AS Factor, 
    10*Abs([Deca].[id] Mod 10) AS Tens, 
    Abs([Uno].[id] Mod 10) AS Ones
FROM 
    msysobjects AS Uno, 
    msysobjects AS Deca;

将其另存为qdyFactor. 然后创建此查询:

Save it as qdyFactor. Then create this query:

SELECT DISTINCT 
    DateAdd("d",[Factor],[DateFrom]) AS Dates
FROM 
    qdyFactor
WHERE 
    qdyFactor.Factor Between 0 And DateDiff("d",[DateFrom],[DateTo]);

这将创建日期列表. 最后，使用它来过滤和汇总其他表.

This will create the list of dates. Finally, use this to filter and sum from your other tables.

这篇关于如何在Access中创建时间序列并可以在查询中使用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！