如何在MS Access查询中重复模式

I have a MS Access database and in it are two tables called [Pattern] and [Element].

我有一个MS Access数据库,其中有两个名为[Pattern]和[Element]的表。

The following example's show the tables and their respective datatype.

以下示例显示了表及其各自的数据类型。

Table 1: [Pattern]

表1:[模式]

patternID - key
pStart - short date
pEnd - short date

Table 2: [Element]

表2:[元素]

elementID - key
patternID
text -text(2)

I want to create a query where it will repeat the pattern contained within the text field of the element table. For example

我想创建一个查询,它将重复元素表的文本字段中包含的模式。例如

for patternID = 1 there are 4 elementID entries with the text values 1,2,3,4

对于patternID = 1,有4个elementID条目,文本值为1,2,3,4

How do I get a query to repeat 1,2,3,4,1,2,3,4 for as long as the difference between the two dates, pStart and pEnd in the pattern table?

如果模式表中两个日期pStart和pEnd之间的差异,我如何获得重复1,2,3,4,1,2,3,4的查询?

Hopefully this makes sense, thanks in advance. I usually work in Excel so Access is new to me.

希望这是有道理的,谢谢你提前。我通常在Excel工作,所以Access对我来说是新的。

2 个解决方案

#1

You will need a number or factor table with one field with integers from 0 to at least the maximum day count you will have.

你需要一个数字或因子表,其中一个字段的整数从0到至少你将拥有的最大日数。

Then you can create the first Cartesian query:

然后,您可以创建第一个笛卡尔查询:

Select 
    patternID
From 
    Pattern,
    Factors
Where
    DateAdd("d", [Factor], [pStart]) <= pEnd

Save this as, say, Patterns and create a second Cartesian query:

将其保存为,例如,模式并创建第二个笛卡尔查询:

Select
    Element.patternID,
    Element.elementID,
    Element.Text
From
    Patterns,
    Element
Where
    Patterns.patternID = Element.patternID
Order By
    Element.patternID,
    Element.elementID,
    Element.Text

2 个解决方案

#1

更多相关文章

随机推荐