MS Access database support - selectquery from query1 and query2

Author

selectquery from query1 and query2

Gordon Youd

2006-04-08, 7:27 am

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.

Jeff Smith

2006-04-08, 7:27 am

"Gordon Youd" <gordon@gyoud.demon.co.uk> wrote in message
news:e1825s$guh$1$83
00dec7@news.demon.co.uk...
>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.
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

2006-04-08, 9:27 am

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" <NoSpam@Not.This.Address> wrote in message
news:e184a6$vi2$1@lu
st.ihug.co.nz...
>
> "Gordon Youd" <gordon@gyoud.demon.co.uk> wrote in message
> news:e1825s$guh$1$83
00dec7@news.demon.co.uk...
> 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
>
>

Randy Harris

2006-04-08, 1:28 pm

Gordon, you haven't made entirely clear, just what you need here.
See comments below -

Gordon Youd wrote:
> 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" <NoSpam@Not.This.Address> wrote in message
> news:e184a6$vi2$1@lu
st.ihug.co.nz...
>
>

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.

Sponsored Links