HELP! unwanted fields

[Gordon Youd]

Randy Harris and Bob Quintal have tried to help me with my data base report
problem, for which I thank them.

The suggestions they gave me still do not seem to work and I am still
getting false data showing.

Data base is of customers who book a car to pick them up on a certain date
to take them to an Airport.
They would also book a car for thier return on a date in the future.

I want to input a DATE to create a report showing down the left column,

CustomerID, OutwardTime, OutDate,

down the right column,

CustomerID, InwardTime, InDate.

So if I put in tomorrows DATE, I could see the list of customers going out
and customers coming in for that day.

What I am getting for each customer is the outgoing data and the future
incoming data.

I just want to see if the same car dropping off a customer could possibly
pickup another one arriving.

I have really tried to make the queries work properly but I still get double
data.

TIA,
Gordon.

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 777 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

[salad]

Gordon Youd wrote:

Randy Harris and Bob Quintal have tried to help me with my data base report
problem, for which I thank them.

The suggestions they gave me still do not seem to work and I am still
getting false data showing.

Data base is of customers who book a car to pick them up on a certain date
to take them to an Airport.
They would also book a car for thier return on a date in the future.

I want to input a DATE to create a report showing down the left column,

CustomerID, OutwardTime, OutDate,

down the right column,

CustomerID, InwardTime, InDate.

So if I put in tomorrows DATE, I could see the list of customers going out
and customers coming in for that day.

What I am getting for each customer is the outgoing data and the future
incoming data.

I just want to see if the same car dropping off a customer could possibly
pickup another one arriving.

I have really tried to make the queries work properly but I still get double
data.

TIA,
Gordon.

Since I don't know what advice was offered, I don't know if my advice
will help. But...

Why not create a report that lists the outbound customers; CustomerID,
OutwardTime, OutDate.

Now copy that report but use inbound date/time.

Now create a third report. Add both in/out bound reports as subreports.

When the report opens, set the filter for both reports to the date selected.

[Mark Reed]

Not sure if this is the sort of thing your after but.....

SELECT YourTable.CustomerID, YourTable.OutwardTime, YourTable.OutwardDate,
Null AS CustomerID2, Null AS InwardTime, Null AS InwardDate
FROM YourTable
WHERE (((YourTable.OutwardDate)=[Enter Date]))
union all
SELECT Null AS CustomerID, Null AS OuwardTime, Null AS OutwardDate,
YourTable.CustomerID, YourTable.InwardTime, YourTable.InwardDate
FROM YourTable
WHERE (((YourTable.InwardDate)=[Enter Date]));
Mark

"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message
news:e3*******************@news.demon.co.uk...

Randy Harris and Bob Quintal have tried to help me with my data base
report problem, for which I thank them.

The suggestions they gave me still do not seem to work and I am still
getting false data showing.

Data base is of customers who book a car to pick them up on a certain date
to take them to an Airport.
They would also book a car for thier return on a date in the future.

I want to input a DATE to create a report showing down the left column,

CustomerID, OutwardTime, OutDate,

down the right column,

CustomerID, InwardTime, InDate.

So if I put in tomorrows DATE, I could see the list of customers going out
and customers coming in for that day.

What I am getting for each customer is the outgoing data and the future
incoming data.

I just want to see if the same car dropping off a customer could possibly
pickup another one arriving.

I have really tried to make the queries work properly but I still get
double data.

TIA,
Gordon.
--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 777 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

['69 Camaro]

Hi, Gordon.

I want to input a DATE to create a report showing down the left column,

CustomerID, OutwardTime, OutDate,

down the right column,

CustomerID, InwardTime, InDate. .. . . I just want to see if the same car dropping off a customer could possibly
pickup another one arriving.
I'd approach this so that I had to as little work as possible. Instead of
listing all outbound customers on the left and all inbound customers on the
right for a given date, I'd want the computer to do all the work of
comparing which of those inbound cars were capable of picking up an outbound
customer. Otherwise, if there are a lot of cars, I'd have to do a lot of
visual comparison checks between the columns to see whether an inbound car
arrived in time to pick up an outbound customer. For example, if an inbound
car arrives tomorrow at 1 P.M., this won't help the outbound customer who
wants to depart at 9 A.M., so it's not going to be worth my time to even
consider that comparison.

So, I'd list each outbound customer on the left with the cars capable of
picking him up on the right. Therefore, I'd want the inbound times of
arriving cars to precede the outbound times of departures. Try this query
as the Record Source for the report:

SELECT TOut.CustomerID AS OutCust, TOut.OutwardTime AS OutboundTime,
TOut.OutDate AS OutboundDate, TIn.CustomerID AS InCust, TIn.InwardTime AS
InboundTime, TIn.InDate AS InboundDate
FROM tblSchedule AS TIn INNER JOIN tblSchedule AS TOut ON TIn.ID <> TOut.ID
WHERE ((TOut.OutDate = TIn.InDate) AND (TOut.OutwardTime > TIn.InwardTime)
AND TOut.OutDate = [Enter Date:]);

.. . . where tblSchedule is the name of the table, and ID is the primary key
of the table. If multiple cars could pick up an outbound customer, then
there will be multiple records showing all of the choices for that outbound
customer.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message
news:e3*******************@news.demon.co.uk... Randy Harris and Bob Quintal have tried to help me with my data base
report problem, for which I thank them.

The suggestions they gave me still do not seem to work and I am still
getting false data showing.

Data base is of customers who book a car to pick them up on a certain date
to take them to an Airport.
They would also book a car for thier return on a date in the future.

I want to input a DATE to create a report showing down the left column,

CustomerID, OutwardTime, OutDate,

down the right column,

CustomerID, InwardTime, InDate.

So if I put in tomorrows DATE, I could see the list of customers going out
and customers coming in for that day.

What I am getting for each customer is the outgoing data and the future
incoming data.

I just want to see if the same car dropping off a customer could possibly
pickup another one arriving.

I have really tried to make the queries work properly but I still get
double data.

TIA,
Gordon.
--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 777 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

[Gordon Youd]

Hi, Gunney, The left hand column "Outbound customer is the one I work on.
Pick up a customer A and take to an airport, it is handy to see if there is
a customer B coming in around the time of inwardTime and Indate.

The left hand column is the main one, if no customers are coming inward,
then the driver won't wait.
Thanks for the info I shall play about with it.
Many thanks also to Salad and Mark Reed, still working with their
suggestions.

------------------------------------------------------------------------------------------------------------
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:d-******************************@adelphia.com...

Hi, Gordon.

I want to input a DATE to create a report showing down the left column,

CustomerID, OutwardTime, OutDate,

down the right column,

CustomerID, InwardTime, InDate.

. . .

I just want to see if the same car dropping off a customer could possibly
pickup another one arriving.

I'd approach this so that I had to as little work as possible. Instead of
listing all outbound customers on the left and all inbound customers on
the right for a given date, I'd want the computer to do all the work of
comparing which of those inbound cars were capable of picking up an
outbound customer. Otherwise, if there are a lot of cars, I'd have to do
a lot of visual comparison checks between the columns to see whether an
inbound car arrived in time to pick up an outbound customer. For example,
if an inbound car arrives tomorrow at 1 P.M., this won't help the outbound
customer who wants to depart at 9 A.M., so it's not going to be worth my
time to even consider that comparison.

So, I'd list each outbound customer on the left with the cars capable of
picking him up on the right. Therefore, I'd want the inbound times of
arriving cars to precede the outbound times of departures. Try this query
as the Record Source for the report:

SELECT TOut.CustomerID AS OutCust, TOut.OutwardTime AS OutboundTime,
TOut.OutDate AS OutboundDate, TIn.CustomerID AS InCust, TIn.InwardTime AS
InboundTime, TIn.InDate AS InboundDate
FROM tblSchedule AS TIn INNER JOIN tblSchedule AS TOut ON TIn.ID <>
TOut.ID
WHERE ((TOut.OutDate = TIn.InDate) AND (TOut.OutwardTime > TIn.InwardTime)
AND TOut.OutDate = [Enter Date:]);

. . . where tblSchedule is the name of the table, and ID is the primary
key of the table. If multiple cars could pick up an outbound customer,
then there will be multiple records showing all of the choices for that
outbound customer.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message
news:e3*******************@news.demon.co.uk...

Randy Harris and Bob Quintal have tried to help me with my data base
report problem, for which I thank them.

The suggestions they gave me still do not seem to work and I am still
getting false data showing.

Data base is of customers who book a car to pick them up on a certain
date to take them to an Airport.
They would also book a car for thier return on a date in the future.

I want to input a DATE to create a report showing down the left column,

CustomerID, OutwardTime, OutDate,

down the right column,

CustomerID, InwardTime, InDate.

So if I put in tomorrows DATE, I could see the list of customers going
out and customers coming in for that day.

What I am getting for each customer is the outgoing data and the future
incoming data.

I just want to see if the same car dropping off a customer could possibly
pickup another one arriving.

I have really tried to make the queries work properly but I still get
double data.

TIA,
Gordon.
--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 777 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 789 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

[Gordon Youd]

Many thanks to all the help received.

The answer was staring me in the face all the time, SUBREPORTS.

Thanks Salad for showing me the way.

It all works correctly.

"it's the age that gets in the way of thinking."

Regards, Gordon.
------------------------------------------------------------------------------
"salad" <oi*@vinegar.com> wrote in message
news:cV****************@newsread4.news.pas.earthli nk.net...

Gordon Youd wrote:

Randy Harris and Bob Quintal have tried to help me with my data base
report problem, for which I thank them.

The suggestions they gave me still do not seem to work and I am still
getting false data showing.

Data base is of customers who book a car to pick them up on a certain
date to take them to an Airport.
They would also book a car for thier return on a date in the future.

I want to input a DATE to create a report showing down the left column,

CustomerID, OutwardTime, OutDate,

down the right column,

CustomerID, InwardTime, InDate.

So if I put in tomorrows DATE, I could see the list of customers going
out and customers coming in for that day.

What I am getting for each customer is the outgoing data and the future
incoming data.

I just want to see if the same car dropping off a customer could possibly
pickup another one arriving.

I have really tried to make the queries work properly but I still get
double data.

TIA,
Gordon.

Since I don't know what advice was offered, I don't know if my advice will
help. But...

Why not create a report that lists the outbound customers; CustomerID,
OutwardTime, OutDate.

Now copy that report but use inbound date/time.

Now create a third report. Add both in/out bound reports as subreports.

When the report opens, set the filter for both reports to the date
selected.

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 815 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!