selectquery from query1 and query2

[Gordon Youd]

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]

Re: selectquery from query1 and query2


"Gordon Youd" <gordon@gyoud.d emon.co.uk> wrote in message
news:e1825s$guh $1$8300dec7@new s.demon.co.uk.. .[color=blue]
>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.[/color]
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]

Re: selectquery from query1 and query2

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.Thi s.Address> wrote in message
news:e184a6$vi2 $1@lust.ihug.co .nz...[color=blue]
>
> "Gordon Youd" <gordon@gyoud.d emon.co.uk> wrote in message
> news:e1825s$guh $1$8300dec7@new s.demon.co.uk.. .[color=green]
>>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.[/color]
> 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
>
>[/color]

[Randy Harris]

Re: selectquery from query1 and query2

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

Gordon Youd wrote:[color=blue]
> 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"[/color]

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.
[color=blue]
>
> 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.[/color]

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 "relationsh ip" 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.
[color=blue]
>
> Regards, Gordon.
>
> ----------------------------------------------------------------------------------------------------
> "Jeff Smith" <NoSpam@Not.Thi s.Address> wrote in message
> news:e184a6$vi2 $1@lust.ihug.co .nz...[color=green]
>> "Gordon Youd" <gordon@gyoud.d emon.co.uk> wrote in message
>> news:e1825s$guh $1$8300dec7@new s.demon.co.uk.. .[color=darkred]
>>> 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.[/color]
>> 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
>>
>>[/color]
>
>[/color]

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.

[Gordon Youd]

Re: selectquery from query1 and query2

Sorry about the sloppy way I put the problem to the group, I'll start
afresh.

I have only one main table of "Bookings".
Each booking has an ID, CustomerID, Customername, Indate, Outdate.

From the "Bookings" table I want to extract all Customers matching Indate
and Outdate, which will be called by
wildcard date input.

From this I want to create a Report, showing Customers and Indate on the
left, Customer and Outdate on the right found by the wildcard date.



Hope that makes sense.

I appreciate the groups help.

The brain does not work too well when you get old. :-)

Regards, Gordon.

[Bob Quintal]

Re: selectquery from query1 and query2

"Gordon Youd" <gordon@gyoud.d emon.co.uk> wrote in
news:e194sv$fsl $1$8300dec7@new s.demon.co.uk:
[color=blue]
> Sorry about the sloppy way I put the problem to the group,
> I'll start afresh.
>
> I have only one main table of "Bookings".
> Each booking has an ID, CustomerID, Customername, Indate,
> Outdate.
>
> From the "Bookings" table I want to extract all Customers
> matching Indate and Outdate, which will be called by
> wildcard date input.
>
> From this I want to create a Report, showing Customers and
> Indate on the left, Customer and Outdate on the right found by
> the wildcard date.
>
>
>
> Hope that makes sense.
>[/color]

It doesn't. When you say "matching Indate and Outdate" do you
mean that indate is less than or equal to your wildcard and
outdate is greater than or equal to your wildcard, or do you
mean indate and outdate are equal, and also equal to the
wildcard?

if the first, you want a query that reads

SELECT * from Bookings
WHERE [input the date]
BETWEEN indate AND outdate.

if the second, make it

SELECT * from Bookings
WHERE [input the date] = outdate
AND [input the date] = outdate .

the third variant is if you want to match either the indate OR
the outdate

I think you can figure out which word to change in the second
query.

[color=blue]
> I appreciate the groups help.
>
> The brain does not work too well when you get old. :-)
>
> Regards, Gordon.
>
>
>
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

[Randy Harris]

Re: selectquery from query1 and query2

Gordon Youd wrote:[color=blue]
> Sorry about the sloppy way I put the problem to the group, I'll start
> afresh.
>
> I have only one main table of "Bookings".
> Each booking has an ID, CustomerID, Customername, Indate, Outdate.
>
> From the "Bookings" table I want to extract all Customers matching Indate
> and Outdate, which will be called by
> wildcard date input.[/color]

Bob's not the only one confused by this. I'll take another guess at
what you are trying to do.

You have one date that you input as a parameter. You are calling that a
"wildcard" date. You want to generate a report with a list of customers
whose Indate is equal to that date on the left side of the report. On
the same report, you want another list of customers, on the right side,
whose Outdate is equal to that same date. Hence, you could potentially
have customers who appear on both sides of the report.

Is this anywhere close to correct?

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Gordon Youd]

Re: selectquery from query1 and query2

Hi, Bob & Randy,

I want to find from the "Booking" table all "Customers" whose "Indate"
matches the "wildcard" input and all "Customers" whose "Outdate" matches the
same "Wildcard.

No "Customer" would ever have the same "Indate" and "Outdate".

I would have a list of customers coming "IN" and a list of customers going
"OUT on the same date.

I can then deal with both customers on the same day. The Report would show
the "INs" in the left hand column, the "OUTs" in the right hand column.

Does this help?

Regards, Gordon.
----------------------------------------------------------------------




"Randy Harris" <please@send.no .spam> wrote in message
news:X7YZf.6565 1$Jd.53096@news svr25.news.prod igy.net...[color=blue]
> Gordon Youd wrote:[color=green]
>> Sorry about the sloppy way I put the problem to the group, I'll start
>> afresh.
>>
>> I have only one main table of "Bookings".
>> Each booking has an ID, CustomerID, Customername, Indate, Outdate.
>>
>> From the "Bookings" table I want to extract all Customers matching Indate
>> and Outdate, which will be called by
>> wildcard date input.[/color]
>
> Bob's not the only one confused by this. I'll take another guess at what
> you are trying to do.
>
> You have one date that you input as a parameter. You are calling that a
> "wildcard" date. You want to generate a report with a list of customers
> whose Indate is equal to that date on the left side of the report. On the
> same report, you want another list of customers, on the right side, whose
> Outdate is equal to that same date. Hence, you could potentially have
> customers who appear on both sides of the report.
>
> Is this anywhere close to correct?
>
> --
> Randy Harris
> tech at promail dot com
> I'm pretty sure I know everything that I can remember.[/color]

[Jeff Smith]

Re: selectquery from query1 and query2

Now since this is a lot clearer you can do this with one query

SELECT * FROM Bookings
WHERE (Indate = [input the date]) OR (OutDate = [input the date]);


"Gordon Youd" <gordon@gyoud.d emon.co.uk> wrote in message
news:e19nj7$7sl $1$8300dec7@new s.demon.co.uk.. .[color=blue]
> Hi, Bob & Randy,
>
> I want to find from the "Booking" table all "Customers" whose "Indate"
> matches the "wildcard" input and all "Customers" whose "Outdate" matches
> the same "Wildcard.
>
> No "Customer" would ever have the same "Indate" and "Outdate".
>
> I would have a list of customers coming "IN" and a list of customers going
> "OUT on the same date.
>
> I can then deal with both customers on the same day. The Report would show
> the "INs" in the left hand column, the "OUTs" in the right hand column.
>
> Does this help?
>
> Regards, Gordon.
> ----------------------------------------------------------------------
>
>
>
>
> "Randy Harris" <please@send.no .spam> wrote in message
> news:X7YZf.6565 1$Jd.53096@news svr25.news.prod igy.net...[color=green]
>> Gordon Youd wrote:[color=darkred]
>>> Sorry about the sloppy way I put the problem to the group, I'll start
>>> afresh.
>>>
>>> I have only one main table of "Bookings".
>>> Each booking has an ID, CustomerID, Customername, Indate, Outdate.
>>>
>>> From the "Bookings" table I want to extract all Customers matching
>>> Indate and Outdate, which will be called by
>>> wildcard date input.[/color]
>>
>> Bob's not the only one confused by this. I'll take another guess at what
>> you are trying to do.
>>
>> You have one date that you input as a parameter. You are calling that a
>> "wildcard" date. You want to generate a report with a list of customers
>> whose Indate is equal to that date on the left side of the report. On
>> the same report, you want another list of customers, on the right side,
>> whose Outdate is equal to that same date. Hence, you could potentially
>> have customers who appear on both sides of the report.
>>
>> Is this anywhere close to correct?
>>
>> --
>> Randy Harris
>> tech at promail dot com
>> I'm pretty sure I know everything that I can remember.[/color]
>
>[/color]

[Bob Quintal]

Re: selectquery from query1 and query2

"Gordon Youd" <gordon@gyoud.d emon.co.uk> wrote in
news:e19nj7$7sl $1$8300dec7@new s.demon.co.uk:
[color=blue]
> Hi, Bob & Randy,
>
> I want to find from the "Booking" table all "Customers" whose
> "Indate" matches the "wildcard" input and all "Customers"
> whose "Outdate" matches the same "Wildcard.
>
> No "Customer" would ever have the same "Indate" and "Outdate".
>
> I would have a list of customers coming "IN" and a list of
> customers going "OUT on the same date.
>
> I can then deal with both customers on the same day. The
> Report would show the "INs" in the left hand column, the
> "OUTs" in the right hand column.
>
> Does this help?[/color]

the third version of the SQL I posted will work. You, just need
to add some grouping on the report to get the column to advance
columms on the switch of date field. the easiest way is to add a
boolean expression to the query,

SELECT *, iif([input the date] = indate, 1, 2) AS sortkey
FROM Bookings
WHERE [input the date] = indate
OR [input the date] = outdate
ORDER BY iif([input the date] = indate, 1, 2); .

Go to the Page Setup menu item, make the detail section of the
report 1/2 the width of the header, set the order to "Down, then
Across"

Open the Sorting and Grouping dialog, group on sortkey. set a
header for the group,
Set the group header property New Row or Column to "before" and
voila, its done.


--
Bob Quintal

PA is y I've altered my email address.

[Gordon Youd]

Re: selectquery from query1 and query2

Many thanks to all the help, I can now untie the rope from the rafters and
put away the stool ;-).

Kind regards, Gordon.

---------------------------------------------------------
"Jeff Smith" <NoSpam@Not.Thi s.Address> wrote in message
news:e19sh6$ais $1@lust.ihug.co .nz...[color=blue]
> Now since this is a lot clearer you can do this with one query
>
> SELECT * FROM Bookings
> WHERE (Indate = [input the date]) OR (OutDate = [input the date]);
>
>
> "Gordon Youd" <gordon@gyoud.d emon.co.uk> wrote in message
> news:e19nj7$7sl $1$8300dec7@new s.demon.co.uk.. .[color=green]
>> Hi, Bob & Randy,
>>
>> I want to find from the "Booking" table all "Customers" whose "Indate"
>> matches the "wildcard" input and all "Customers" whose "Outdate" matches
>> the same "Wildcard.
>>
>> No "Customer" would ever have the same "Indate" and "Outdate".
>>
>> I would have a list of customers coming "IN" and a list of customers
>> going "OUT on the same date.
>>
>> I can then deal with both customers on the same day. The Report would
>> show the "INs" in the left hand column, the "OUTs" in the right hand
>> column.
>>
>> Does this help?
>>
>> Regards, Gordon.
>> ----------------------------------------------------------------------
>>
>>
>>
>>
>> "Randy Harris" <please@send.no .spam> wrote in message
>> news:X7YZf.6565 1$Jd.53096@news svr25.news.prod igy.net...[color=darkred]
>>> Gordon Youd wrote:
>>>> Sorry about the sloppy way I put the problem to the group, I'll start
>>>> afresh.
>>>>
>>>> I have only one main table of "Bookings".
>>>> Each booking has an ID, CustomerID, Customername, Indate, Outdate.
>>>>
>>>> From the "Bookings" table I want to extract all Customers matching
>>>> Indate and Outdate, which will be called by
>>>> wildcard date input.
>>>
>>> Bob's not the only one confused by this. I'll take another guess at
>>> what you are trying to do.
>>>
>>> You have one date that you input as a parameter. You are calling that a
>>> "wildcard" date. You want to generate a report with a list of customers
>>> whose Indate is equal to that date on the left side of the report. On
>>> the same report, you want another list of customers, on the right side,
>>> whose Outdate is equal to that same date. Hence, you could potentially
>>> have customers who appear on both sides of the report.
>>>
>>> Is this anywhere close to correct?
>>>
>>> --
>>> Randy Harris
>>> tech at promail dot com
>>> I'm pretty sure I know everything that I can remember.[/color]
>>
>>[/color]
>
>[/color]