不知道从哪里开始 [英] Dont know where to start
问题描述
好的,我想做的是:
我有一个查询,可以输出这样的信息:
(1)(2) (3)(4)(5)
1 1 Cooper Street NS Johnson& Dale
2 2 Cooper Street NS Johnson& Dale
3 3 Cooper Street NS Johnson& Dale
4 4 Cooper Street NS Johnson&戴尔
5 5 Cooper Street NS Johnson&戴尔
6 1 Cooper Street SS Bail& Bob
7 2 Cooper Street SS Bail& Bob
10 3 Cooper Street SS Bail& Bob
10A 4 Cooper Street SS Bail& Bob
好的,所以我的信息看起来像这样。我是什么
试图做的是excel打开数据库并显示信息
像这样:
1 - 5 Cooper Street NS Johnson &安培; Dale
6 - 10A Cooper Street SS Bail& Bob
有没有可以让excel这样做?
Ok i what i am trying to do is:
I have a query that pumps out information like this:
(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob
Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:
1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob
Is it possible to have excel do this?
推荐答案
6月14日,上午10:06,stephenruss ... @ gmail.com写道:
On Jun 14, 10:06 am, stephenruss...@gmail.com wrote:
好吧我想做的是:
我有一个查询,提供如下信息:
(1)(2)(3)(4)(5)
1 1 Cooper Street NS Johnson& Dale
2 2 Cooper Street NS Johnson& Dale
3 3 Cooper Street NS Johnson& Dale
4 4 Cooper Street NS Johnson&戴尔
5 5 Cooper Street NS Johnson&戴尔
6 1 Cooper Street SS Bail& Bob
7 2 Cooper Street SS Bail& Bob
10 3 Cooper Street SS Bail& Bob
10A 4 Cooper Street SS Bail&鲍勃
好的,所以我的信息看起来像这样。我是什么
试图做的是excel打开数据库并显示信息
像这样:
1 - 5 Cooper Street NS Johnson& Dale
6 - 10A Cooper Street SS Bail&鲍勃
有可能有excel这样做吗?
Ok i what i am trying to do is:
I have a query that pumps out information like this:
(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob
Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:
1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob
Is it possible to have excel do this?
是否可以在报告中创建我想要的内容?
Is it possible to have access create what i want in a report?
好,我几乎完全使用派生表来完成这项工作。但是,
显然,当你在另一个
派生表中有一个派生表时,Access会窒息。因此,您需要一个存储的查询作为子查询使用。
假设您的表名为MyTable,下面的字段1-5被称为
Field1,Field2等,首先,创建一个名为Query1的查询,并在SQL后面给它
:
SELECT MyTable.Field4,Min(MyTable.Field2)AS Min2,Max(MyTable.Field2)AS
Max2
来自MyTable
GROUP BY MyTable.Field4
(您可以更改字段和talbe名称以适合您的结构;但不要
更改别名 - AS Max2等等。
保存后,创建第二个查询,并为其提供以下SQL:
SELECT [a]。[Min1]& " - &安培; [b]。[Max1] AS Expr1,c.Field3,c.Field4,
c.Field5
FROM(SELECT MyTable.Field4,MyTable.Field1 AS Max1
来自Query1
INNER JOIN MyTable ON(Query1.Max2 = MyTable.Field2)AND(Query1.Field4 =
MyTable.Field4)
)AS b INNER JOIN((选择MyTable.Field4,MyTable.Field1 AS Min1
来自Query1 INNER JOIN MyTable ON(Query1.Field4 = MyTable.Field4)AND
(Query1.Min2 = MyTable.Field2)
)作为内部联接(选择MyTable.Field3,MyTable.Field4,MyTable.Field5
来自MyTable
GROUP BY MyTable.Field3,MyTable.Field4,MyTable.Field5
)AS c ON a.Field4 = c.Field4)ON b.Field4 = c.Field4
再次,您可以相应地重命名表和字段。该查询应该
为您提供以下结果。
保存第二个查询后,您可以直接打开它,也可以基于
报告第二个查询并在报告中显示结果。
Neil
< st ********* ***@gmail.com在留言中写道
news:11 ********************** @ o11g2000prd.googlegr psps.com。 ..
Well, I almost got this to work entirely with derived tables. But,
apparently, Access chokes when you have a derived table within another
derived table. So you''ll need one stored query to use as a subquery.
Assuming your table is called "MyTable," and the fields 1-5 below are called
Field1, Field2, etc., first, create a query called "Query1", and give it the
following SQL:
SELECT MyTable.Field4, Min(MyTable.Field2) AS Min2, Max(MyTable.Field2) AS
Max2
FROM MyTable
GROUP BY MyTable.Field4
(You can change the field and talbe names to fit your structure; but don''t
change the aliases -- "AS Max2," etc.)
Once that''s saved, create a second query, and give it the following SQL:
SELECT [a].[Min1] & " - " & [b].[Max1] AS Expr1, c.Field3, c.Field4,
c.Field5
FROM (SELECT MyTable.Field4, MyTable.Field1 AS Max1
FROM Query1
INNER JOIN MyTable ON (Query1.Max2 = MyTable.Field2) AND (Query1.Field4 =
MyTable.Field4)
) AS b INNER JOIN ((SELECT MyTable.Field4, MyTable.Field1 AS Min1
FROM Query1 INNER JOIN MyTable ON (Query1.Field4 = MyTable.Field4) AND
(Query1.Min2 = MyTable.Field2)
) AS a INNER JOIN (SELECT MyTable.Field3, MyTable.Field4, MyTable.Field5
FROM MyTable
GROUP BY MyTable.Field3, MyTable.Field4, MyTable.Field5
) AS c ON a.Field4 = c.Field4) ON b.Field4 = c.Field4
Again, you can rename the table and fields accordingly. That query should
give you the results below.
Once this second query is saved, you can either open it directly, or base a
report on the second query and display the results in a report.
Neil
<st************@gmail.comwrote in message
news:11**********************@o11g2000prd.googlegr oups.com...
6月14日上午10点06分,stephenruss ... @ gmail.com写道:
On Jun 14, 10:06 am, stephenruss...@gmail.com wrote:
>好的,我想做的是:
我有一个查询,提供如下信息:
(1)(2)(3) )(4)(5)
1 1 Cooper Street NS Johnson& Dale
2 2 Cooper Street NS Johnson& Dale
3 3 Cooper Street NS Johnson& Dale
4 4 Cooper Street NS Johnson& Dale
5 5 Cooper Street NS Johnson&戴尔
6 1 Cooper Street SS Bail& Bob
7 2 Cooper Street SS Bail& Bob
10 3 Cooper Street SS Bail& Bob
10A 4 Cooper Street SS Bail& Bob
好的,所以我的信息看起来像这样。我想要做的就是excel打开数据库并显示如下信息:
1 - 5 Cooper Street NS Johnson& Dale
6 - 10A Cooper Street SS Bail&鲍勃
有可能有excel这样做吗?
>Ok i what i am trying to do is:
I have a query that pumps out information like this:
(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob
Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:
1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob
Is it possible to have excel do this?
是否可以在报告中创建我想要的内容?
Is it possible to have access create what i want in a report?
6月14日上午10点06分,stephenruss ... @ gmail.com写道:
On Jun 14, 10:06 am, stephenruss...@gmail.com wrote:
好吧我想做的是:
我有一个查询,提供如下信息:
(1)(2)(3)(4)(5)
1 1 Cooper Street NS Johnson& Dale
2 2 Cooper Street NS Johnson& Dale
3 3 Cooper Street NS Johnson& Dale
4 4 Cooper Street NS Johnson&戴尔
5 5 Cooper Street NS Johnson&戴尔
6 1 Cooper Street SS Bail& Bob
7 2 Cooper Street SS Bail& Bob
10 3 Cooper Street SS Bail& Bob
10A 4 Cooper Street SS Bail&鲍勃
好的,所以我的信息看起来像这样。我是什么
试图做的是excel打开数据库并显示信息
像这样:
1 - 5 Cooper Street NS Johnson& Dale
6 - 10A Cooper Street SS Bail&鲍勃
有可能有excel这样做吗?
Ok i what i am trying to do is:
I have a query that pumps out information like this:
(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob
Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:
1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob
Is it possible to have excel do this?
SELECT Min([Field1])& " - &安培; Max([Filed1])AS Range,Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2,Table1。 Field3;
根据需要添加字段
SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;
Add fields as necessary
这篇关于不知道从哪里开始的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!