从query1和query2中选择查询 [英] selectquery from query1 and query2
问题描述
我有一个客户表，每个客户都有一个INDATE和一个OUTDATE，我是
试图创建一个报告，显示来自INDATE和所有
cutomers的所有客户来自OUTDATE，其中INDATE和OUTDATE = []。 （通配符输入日期）
我创建了INDATE作为query1，OUTDATE作为query2，我创建了第3个查询
结合两者。
我运行第三个查询，输入[] indate然后[] outdate，按回车键，我
获得双倍记录。
史密斯
史密斯
琼斯
琼斯
等
我是什么？做错了，？ SKS可以帮忙吗。
戈登。
I have a customer table and each customer has an INDATE and an OUTDATE, I am
trying to create a report that shows all customers from INDATE and all
cutomers from OUTDATE, where INDATEand OUTDATE =[]. (wildcard input date)
I have created INDATE as query1, OUTDATE as query2, I created a 3rd query
combining both.
I run the 3rd query, put in the [] indate then [] outdate, press enter and I
get double records.
Smith
Smith
Jones
Jones
etc.
What am I doing wrong,? can SKS help please.
Gordon.
推荐答案
" Gordon Youd <去**** @ gyoud.demon.co.uk>在消息中写道
新闻：e1 ******************* @ news.demon.co.uk ...
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message
news:e1*******************@news.demon.co.uk...
我有一个客户表，每个客户都有一个INDATE和一个OUTDATE，我正在尝试创建一个报告，显示来自INDATE的所有客户以及来自OUTDATE的所有
cutomer，其中INDATE和OUTDATE = [] 。 （通配符输入日期）
我创建了INDATE作为query1，OUTDATE作为query2，我创建了第3个查询
结合两者。
我运行第3个查询，放入[] indate然后[]过时，按回车键和
我得到双重记录。
史密斯
史密斯
琼斯
琼斯等等
我做错了什么？可以请SKS帮忙。
Gordon。
I have a customer table and each customer has an INDATE and an OUTDATE, I
am trying to create a report that shows all customers from INDATE and all
cutomers from OUTDATE, where INDATEand OUTDATE =[]. (wildcard input date)
I have created INDATE as query1, OUTDATE as query2, I created a 3rd query
combining both.
I run the 3rd query, put in the [] indate then [] outdate, press enter and
I get double records.
Smith
Smith
Jones
Jones
etc.
What am I doing wrong,? can SKS help please.
Gordon.
您是否尝试过在query3中加入query1和query2？两个查询都包含
CustomerID，在query3中单击并将CustomerID从query1
拖到query2中的CustomerID。运行query3以查看是否获得了正确的
结果。如果没有，请发布所有3个查询的SQL，我们将看看
出了什么问题。
Jeff
Have you tried joining query1 and query2 inside query3? Both queries contain
the CustomerID, from within query3 click and drag the CustomerID from query1
to the CustomerID in query2. Run the query3 to see if you get the correct
results. If not, post the SQL for all 3 queries and we''ll have a look at
what''s going wrong.
Jeff
杰夫，
这是你的建议之前的SQL。
SELECT [IN] .ID，[IN] .Prefix，[IN]。姓，[IN] .InwardDate，OUT.ID，
OUT.Prefix，OUT.Surname，OUT.OutwardDate
FROM [IN] ，OUT;
这导致显示IN的两条记录。结果和两个记录
OUT
结果对于我输入的任何日期都是正确的。
这是建议后的SQL。
SELECT [IN] .ID，[IN] .Prefix，[IN]。姓，[IN] .InwardDate，OUT.ID，
OUT.Prefix，OUT.Surname，OUT.OutwardDate
FROM [IN] INNER JOIN OUT ON [IN] .ID = OUT.ID;
我只收到列标题，没有结果。
问候，戈登。
------- -------------------------------------------------- -------------------------------------------
杰夫史密斯 <否**** @ Not.This.Address>在消息中写道
新闻：e1 ********** @ lust.ihug.co.nz ...
Hi, Jeff,
This is the SQL before your suggestion.
SELECT [IN].ID, [IN].Prefix, [IN].Surname, [IN].InwardDate, OUT.ID,
OUT.Prefix, OUT.Surname, OUT.OutwardDate
FROM [IN], OUT;
This results in two records showing for the "IN" result and two records for
the "OUT"
The results are correct for any date I enter.
This is the SQL after your suggestion.
SELECT [IN].ID, [IN].Prefix, [IN].Surname, [IN].InwardDate, OUT.ID,
OUT.Prefix, OUT.Surname, OUT.OutwardDate
FROM [IN] INNER JOIN OUT ON [IN].ID = OUT.ID;
I am only getting the column headers, no results.
Regards, Gordon.
----------------------------------------------------------------------------------------------------
"Jeff Smith" <No****@Not.This.Address> wrote in message
news:e1**********@lust.ihug.co.nz...
戈登你几乎" <去**** @ gyoud.demon.co.uk>在消息中写道
新闻：e1 ******************* @ news.demon.co.uk ...
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message
news:e1*******************@news.demon.co.uk...
我有客户表和每个客户都有一个INDATE和一个OUTDATE，我正在尝试创建一个报告，显示来自INDATE的所有客户以及来自OUTDATE的所有客户，其中INDATE和OUTDATE = []。 （通配符输入日期）
我创建了INDATE作为query1，OUTDATE作为query2，我创建了第3个查询
结合两者。
我运行第3个查询，放入[] indate然后[]过时，按回车
我得到双重记录。
史密斯
史密斯
琼斯
琼斯等等
我做错了什么？可以请SKS帮忙。
Gordon。
I have a customer table and each customer has an INDATE and an OUTDATE, I
am trying to create a report that shows all customers from INDATE and all
cutomers from OUTDATE, where INDATEand OUTDATE =[]. (wildcard input date)
I have created INDATE as query1, OUTDATE as query2, I created a 3rd query
combining both.
I run the 3rd query, put in the [] indate then [] outdate, press enter
and I get double records.
Smith
Smith
Jones
Jones
etc.
What am I doing wrong,? can SKS help please.
Gordon.
您是否尝试过在query3中加入query1和query2？两个查询都包含CustomerID，在query3中单击并将CustomerID从query1拖到query2中的CustomerID。运行query3以查看是否获得了正确的结果。如果没有，请发布所有3个查询的SQL，我们将看看出了什么问题。
Jeff
Have you tried joining query1 and query2 inside query3? Both queries
contain the CustomerID, from within query3 click and drag the CustomerID
from query1 to the CustomerID in query2. Run the query3 to see if you get
the correct results. If not, post the SQL for all 3 queries and we''ll have
a look at what''s going wrong.
Jeff
戈登，你还没有完全清楚，只是你需要的东西。
见下面的评论 -
Gordon Youd写道：
Gordon, you haven''t made entirely clear, just what you need here.
See comments below -
Gordon Youd wrote:
杰夫，
这是你的建议之前的SQL。
SELECT [IN] .ID，[IN] .Prefix，[IN ]。姓，[IN] .InwardDate，OUT.ID，
OUT.Prefix，OUT.Surname，OUT.OutwardDate
FROM [IN]，OUT;
这个结果在显示IN的两个记录中结果和两个记录
OUT
这是创建笛卡尔积的结果。您将从另一个输入查询中获得一个条目
。每个查询中有两条记录，
4结果。每个输入查询中有三条记录，9条结果记录。
四条会给你16条等等。
结果对于我输入的任何日期都是正确的。
<这是建议之后的SQL。
SELECT [IN] .ID，[IN] .Prefix，[IN]。姓，[IN] .InwardDate，OUT.ID，
OUT。前缀，OUT.Surname，OUT.OutwardDate
FROM [IN] INNER JOIN OUT ON [IN] .ID = OUT.ID;
我只获取列标题，没有结果。
通常情况下，或者更好的表达方式，通常情况下，如果您希望从多个输入表或查询获得
结果，则使用连接到
建立关系在两个表之间。
你还没有表明一个表中的ID
与另一个表中的ID之间是否存在某种联系。
此致，戈登。
----------------------------- -------------------------------------------------- ---------------------
杰夫史密斯 <否**** @ Not.This.Address>在消息中写道
新闻：e1 ********** @ lust.ihug.co.nz ...
Hi, Jeff,
This is the SQL before your suggestion.
SELECT [IN].ID, [IN].Prefix, [IN].Surname, [IN].InwardDate, OUT.ID,
OUT.Prefix, OUT.Surname, OUT.OutwardDate
FROM [IN], OUT;
This results in two records showing for the "IN" result and two records for
the "OUT"
This is creating a Cartesian Product result. You will get one entry
from each input query for each in the other. Two records in each query,
4 results. Three records in each input query, 9 resulting records.
Four would give you 16, etc.
The results are correct for any date I enter.
This is the SQL after your suggestion.
SELECT [IN].ID, [IN].Prefix, [IN].Surname, [IN].InwardDate, OUT.ID,
OUT.Prefix, OUT.Surname, OUT.OutwardDate
FROM [IN] INNER JOIN OUT ON [IN].ID = OUT.ID;
I am only getting the column headers, no results.
Normally, or perhaps a better expression, commonly, when you want a
result from more than one input table or query you use a join to
establish the "relationship" between the two tables.
You haven''t indicated if there is some sort of connection between the ID
in one table and the ID in the other.
Regards, Gordon.
----------------------------------------------------------------------------------------------------
"Jeff Smith" <No****@Not.This.Address> wrote in message
news:e1**********@lust.ihug.co.nz...
" Gordon Youd" <去**** @ gyoud.demon.co.uk>在消息中写道
新闻：e1 ******************* @ news.demon.co.uk ...
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message
news:e1*******************@news.demon.co.uk...
我有客户表和每个客户都有一个INDATE和一个OUTDATE，我正在尝试创建一个报告，显示来自INDATE的所有客户以及来自OUTDATE的所有客户，其中INDATE和OUTDATE = []。 （通配符输入日期）
我创建了INDATE作为query1，OUTDATE作为query2，我创建了第3个查询
结合两者。
我运行第3个查询，放入[] indate然后[]过时，按回车
我得到双重记录。
史密斯
史密斯
琼斯
琼斯等等
我做错了什么？可以请SKS帮忙。
Gordon。
I have a customer table and each customer has an INDATE and an OUTDATE, I
am trying to create a report that shows all customers from INDATE and all
cutomers from OUTDATE, where INDATEand OUTDATE =[]. (wildcard input date)
I have created INDATE as query1, OUTDATE as query2, I created a 3rd query
combining both.
I run the 3rd query, put in the [] indate then [] outdate, press enter
and I get double records.
Smith
Smith
Jones
Jones
etc.
What am I doing wrong,? can SKS help please.
Gordon.
您是否尝试过在query3中加入query1和query2？两个查询都包含CustomerID，在query3中单击并将CustomerID从query1拖到query2中的CustomerID。运行query3以查看是否获得了正确的结果。如果没有，请发布所有3个查询的SQL，我们将看看出了什么问题。
Jeff
Have you tried joining query1 and query2 inside query3? Both queries
contain the CustomerID, from within query3 click and drag the CustomerID
from query1 to the CustomerID in query2. Run the query3 to see if you get
the correct results. If not, post the SQL for all 3 queries and we''ll have
a look at what''s going wrong.
Jeff
我要猜一下，你想要的是两个输入中的每一个来自
的所有记录查询并且两个查询中的
ID之间没有关系。如果猜测是正确的，那么你需要的是一个UNION查询
。这样的事情：
SELECT ID，Prefix，Surname，InwardDate as DDate
FROM Query1
UNION
SELECT ID，Prefix，Surname，OutwardDate as DDate
来自Query2
看看这是否更接近你的需要。
-
Randy Harris
tech at promail dot com
我很确定我知道一切我记得。
I''m going to make a guess, that what you want is all of the records from
each of the two input queries and that there is no relation between the
IDs in the two queries. If that guess is correct, then what you need is
a UNION query. Something like this:
SELECT ID, Prefix, Surname, InwardDate as DDate
FROM Query1
UNION
SELECT ID, Prefix, Surname, OutwardDate as DDate
FROM Query2
See if this is any closer to what you need.
--
Randy Harris
tech at promail dot com
I''m pretty sure I know everything that I can remember.
这篇关于从query1和query2中选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
