Outer Join with Where clause returns Unexpected Records

[Rab]

Hi there

I have created a query in Access using the Microsoft Knowledge Base Article
: Q124152.

This query works great for what I wish and I can call it thru Visual Basic
and it works fine.

However, I would like to use this as a SQL select statement and use it in my
code as apposed to calling it from the Access Database it belongs to.

The first query following the Article is -

SELECT Bookings.FirstName, Bookings.LastName,
Bookings.Day, Bookings.AppointmentDate,
Bookings.Treatment, Bookings.Stylist,
Bookings.Price, Bookings.AppointmentTime
FROM Bookings
WHERE (((Bookings.AppointmentDate)=Date()));
and named DailyAppointments

The second query based on the first query and another table is -

SELECT Bookings.FirstName, Bookings.LastName,
Bookings.Day, Bookings.AppointmentDate,
Bookings.Treatment, Bookings.Stylist,
Bookings.Price, Bookings.AppointmentTime
FROM Bookings
WHERE (((Bookings.AppointmentDate)=Date()));
I have tried using the WHERE EXISTS keywords but this will only work
providing one of the required times exist on a given date.
I would like it to display all of the times table even if there is not an
appointment created for that day.

This query works great in Access and Visual Basic the latter called as a
View in the DataEnvironment Designer.
Can someone please help me with the correct SQL.
Regards
Rab.

[marc]

SQL = "SELECT Bookings.FirstName, Bookings.LastName,
Bookings.Day, Bookings.AppointmentDate,
Bookings.Treatment, Bookings.Stylist,
Bookings.Price, Bookings.AppointmentTime
FROM Bookings
WHERE (((Bookings.AppointmentDate)=" & date & "));"

The Date() function does not use () in VB. Also the SQL string need to concatenated
to include the system date. One thing you may want to consider is that if
you code the SQL each time it is executed the database needs to create the
query, there is some performance loss. If it is possible to store the query
in Access that would probably be preferable.

Marc
"Rab" <robert-dobbie@lineone.net> wrote:
>Hi there
>
>I have created a query in Access using the Microsoft Knowledge Base Article
>: Q124152.
>
>This query works great for what I wish and I can call it thru Visual Basic
>and it works fine.
>
>However, I would like to use this as a SQL select statement and use it in
my
>code as apposed to calling it from the Access Database it belongs to.
>
>The first query following the Article is -
>
>SELECT Bookings.FirstName, Bookings.LastName,
> Bookings.Day, Bookings.AppointmentDate,
> Bookings.Treatment, Bookings.Stylist,
> Bookings.Price, Bookings.AppointmentTime
>FROM Bookings
>WHERE (((Bookings.AppointmentDate)=Date()));
>and named DailyAppointments
>
>The second query based on the first query and another table is -
>
>SELECT Bookings.FirstName, Bookings.LastName,
> Bookings.Day, Bookings.AppointmentDate,
> Bookings.Treatment, Bookings.Stylist,
> Bookings.Price, Bookings.AppointmentTime
>FROM Bookings
>WHERE (((Bookings.AppointmentDate)=Date()));
>I have tried using the WHERE EXISTS keywords but this will only work
>providing one of the required times exist on a given date.
>I would like it to display all of the times table even if there is not an
>appointment created for that day.
>
>This query works great in Access and Visual Basic the latter called as a
>View in the DataEnvironment Designer.
>Can someone please help me with the correct SQL.
>Regards
> Rab.
>
>
>
>

[marc]

Correction
"marc" <whale@ultranet.com> wrote:
>
>SQL = "SELECT Bookings.FirstName, Bookings.LastName,
> Bookings.Day, Bookings.AppointmentDate,
> Bookings.Treatment, Bookings.Stylist,
> Bookings.Price, Bookings.AppointmentTime
>FROM Bookings
>WHERE (((Bookings.AppointmentDate)=#" & date & "#));"
>
>The Date() function does not use () in VB. Also the SQL string need to
concatenated
>to include the system date. One thing you may want to consider is that
if
>you code the SQL each time it is executed the database needs to create the
>query, there is some performance loss. If it is possible to store the query
>in Access that would probably be preferable.
>
>Marc
>"Rab" <robert-dobbie@lineone.net> wrote:
>>Hi there
>>
>>I have created a query in Access using the Microsoft Knowledge Base Article
>>: Q124152.
>>
>>This query works great for what I wish and I can call it thru Visual Basic
>>and it works fine.
>>
>>However, I would like to use this as a SQL select statement and use it
in
>my
>>code as apposed to calling it from the Access Database it belongs to.
>>
>>The first query following the Article is -
>>
>>SELECT Bookings.FirstName, Bookings.LastName,
>> Bookings.Day, Bookings.AppointmentDate,
>> Bookings.Treatment, Bookings.Stylist,
>> Bookings.Price, Bookings.AppointmentTime
>>FROM Bookings
>>WHERE (((Bookings.AppointmentDate)=Date()));
>>and named DailyAppointments
>>
>>The second query based on the first query and another table is -
>>
>>SELECT Bookings.FirstName, Bookings.LastName,
>> Bookings.Day, Bookings.AppointmentDate,
>> Bookings.Treatment, Bookings.Stylist,
>> Bookings.Price, Bookings.AppointmentTime
>>FROM Bookings
>>WHERE (((Bookings.AppointmentDate)=Date()));
>>I have tried using the WHERE EXISTS keywords but this will only work
>>providing one of the required times exist on a given date.
>>I would like it to display all of the times table even if there is not
an
>>appointment created for that day.
>>
>>This query works great in Access and Visual Basic the latter called as
a
>>View in the DataEnvironment Designer.
>>Can someone please help me with the correct SQL.
>>Regards
>> Rab.
>>
>>
>>
>>
>

[Rab]

Hi Marc

Thanx for that - but it does not help me with the two sql statements being
brought together to produce one.

I realise that it is probably better to call this statement from Access
itself but I wished to know if it could be done in an SQL Select statement
as it can in Access using the aforementioned Microsoft Article.

Rab.


"marc" <whale@ultranet.com> wrote in message
news:3b5d6700$1@news.devx.com...
>
> Correction
> "marc" <whale@ultranet.com> wrote:
> >
> >SQL = "SELECT Bookings.FirstName, Bookings.LastName,
> > Bookings.Day, Bookings.AppointmentDate,
> > Bookings.Treatment, Bookings.Stylist,
> > Bookings.Price, Bookings.AppointmentTime
> >FROM Bookings
> >WHERE (((Bookings.AppointmentDate)=#" & date & "#));"
> >
> >The Date() function does not use () in VB. Also the SQL string need to
> concatenated
> >to include the system date. One thing you may want to consider is that
> if
> >you code the SQL each time it is executed the database needs to create
the
> >query, there is some performance loss. If it is possible to store the
query
> >in Access that would probably be preferable.
> >
> >Marc
> >"Rab" <robert-dobbie@lineone.net> wrote:
> >>Hi there
> >>
> >>I have created a query in Access using the Microsoft Knowledge Base
Article
> >>: Q124152.
> >>
> >>This query works great for what I wish and I can call it thru Visual
Basic
> >>and it works fine.
> >>
> >>However, I would like to use this as a SQL select statement and use it
> in
> >my
> >>code as apposed to calling it from the Access Database it belongs to.
> >>
> >>The first query following the Article is -
> >>
> >>SELECT Bookings.FirstName, Bookings.LastName,
> >> Bookings.Day, Bookings.AppointmentDate,
> >> Bookings.Treatment, Bookings.Stylist,
> >> Bookings.Price, Bookings.AppointmentTime
> >>FROM Bookings
> >>WHERE (((Bookings.AppointmentDate)=Date()));
> >>and named DailyAppointments
> >>
> >>The second query based on the first query and another table is -
> >>
> >>SELECT Bookings.FirstName, Bookings.LastName,
> >> Bookings.Day, Bookings.AppointmentDate,
> >> Bookings.Treatment, Bookings.Stylist,
> >> Bookings.Price, Bookings.AppointmentTime
> >>FROM Bookings
> >>WHERE (((Bookings.AppointmentDate)=Date()));
> >>I have tried using the WHERE EXISTS keywords but this will only work
> >>providing one of the required times exist on a given date.
> >>I would like it to display all of the times table even if there is not
> an
> >>appointment created for that day.
> >>
> >>This query works great in Access and Visual Basic the latter called as
> a
> >>View in the DataEnvironment Designer.
> >>Can someone please help me with the correct SQL.
> >>Regards
> >> Rab.
> >>
> >>
> >>
> >>
> >
>

[marc]

hello

I think you posted the same query twice in your original post. You might
want to repost the second query.

marc

"Rab" <robert-dobbie@lineone.net> wrote:
>Hi Marc
>
>Thanx for that - but it does not help me with the two sql statements being
>brought together to produce one.
>
>I realise that it is probably better to call this statement from Access
>itself but I wished to know if it could be done in an SQL Select statement
>as it can in Access using the aforementioned Microsoft Article.
>
>Rab.
>
>
>"marc" <whale@ultranet.com> wrote in message
>news:3b5d6700$1@news.devx.com...
>>
>> Correction
>> "marc" <whale@ultranet.com> wrote:
>> >
>> >SQL = "SELECT Bookings.FirstName, Bookings.LastName,
>> > Bookings.Day, Bookings.AppointmentDate,
>> > Bookings.Treatment, Bookings.Stylist,
>> > Bookings.Price, Bookings.AppointmentTime
>> >FROM Bookings
>> >WHERE (((Bookings.AppointmentDate)=#" & date & "#));"
>> >
>> >The Date() function does not use () in VB. Also the SQL string need
to
>> concatenated
>> >to include the system date. One thing you may want to consider is that
>> if
>> >you code the SQL each time it is executed the database needs to create
>the
>> >query, there is some performance loss. If it is possible to store the
>query
>> >in Access that would probably be preferable.
>> >
>> >Marc
>> >"Rab" <robert-dobbie@lineone.net> wrote:
>> >>Hi there
>> >>
>> >>I have created a query in Access using the Microsoft Knowledge Base
>Article
>> >>: Q124152.
>> >>
>> >>This query works great for what I wish and I can call it thru Visual
>Basic
>> >>and it works fine.
>> >>
>> >>However, I would like to use this as a SQL select statement and use
it
>> in
>> >my
>> >>code as apposed to calling it from the Access Database it belongs to.
>> >>
>> >>The first query following the Article is -
>> >>
>> >>SELECT Bookings.FirstName, Bookings.LastName,
>> >> Bookings.Day, Bookings.AppointmentDate,
>> >> Bookings.Treatment, Bookings.Stylist,
>> >> Bookings.Price, Bookings.AppointmentTime
>> >>FROM Bookings
>> >>WHERE (((Bookings.AppointmentDate)=Date()));
>> >>and named DailyAppointments
>> >>
>> >>The second query based on the first query and another table is -
>> >>
>> >>SELECT Bookings.FirstName, Bookings.LastName,
>> >> Bookings.Day, Bookings.AppointmentDate,
>> >> Bookings.Treatment, Bookings.Stylist,
>> >> Bookings.Price, Bookings.AppointmentTime
>> >>FROM Bookings
>> >>WHERE (((Bookings.AppointmentDate)=Date()));
>> >>I have tried using the WHERE EXISTS keywords but this will only work
>> >>providing one of the required times exist on a given date.
>> >>I would like it to display all of the times table even if there is not
>> an
>> >>appointment created for that day.
>> >>
>> >>This query works great in Access and Visual Basic the latter called
as
>> a
>> >>View in the DataEnvironment Designer.
>> >>Can someone please help me with the correct SQL.
>> >>Regards
>> >> Rab.
>> >>
>> >>
>> >>
>> >>
>> >
>>
>
>

[Rab]

Yeah your right I have posted the same query twice - what a blithering
idiot.

Anyway here goes with both queries again -

SELECT Bookings.FirstName, Bookings.LastName, Bookings.Day,
Bookings.AppointmentDate, Bookings.Treatment, Bookings.Stylist,
Bookings.Price, Bookings.AppointmentTime
FROM Bookings
WHERE (((Bookings.AppointmentDate)=date()));

SELECT Times.Time, [Firstname] & " " & [LastName] AS Customer,
DailyAppointments.Day, DailyAppointments.AppointmentDate AS [Date],
DailyAppointments.Treatment, DailyAppointments.Stylist,
DailyAppointments.Price
FROM Times LEFT JOIN DailyAppointments ON Times.Time =
DailyAppointments.AppointmentTime;

These are the queries created using the MS Article Q124152.

I would like to combine them into one select statement if possible that can
be called via VB code.

Cheers
Rab.

"marc" <whale@ultranet.com> wrote in message
news:3b5ddbca$1@news.devx.com...
>
> hello
>
> I think you posted the same query twice in your original post. You might
> want to repost the second query.
>
> marc
>
> "Rab" <robert-dobbie@lineone.net> wrote:
> >Hi Marc
> >
> >Thanx for that - but it does not help me with the two sql statements
being
> >brought together to produce one.
> >
> >I realise that it is probably better to call this statement from Access
> >itself but I wished to know if it could be done in an SQL Select
statement
> >as it can in Access using the aforementioned Microsoft Article.
> >
> >Rab.
> >
> >
> >"marc" <whale@ultranet.com> wrote in message
> >news:3b5d6700$1@news.devx.com...
> >>
> >> Correction
> >> "marc" <whale@ultranet.com> wrote:
> >> >
> >> >SQL = "SELECT Bookings.FirstName, Bookings.LastName,
> >> > Bookings.Day, Bookings.AppointmentDate,
> >> > Bookings.Treatment, Bookings.Stylist,
> >> > Bookings.Price, Bookings.AppointmentTime
> >> >FROM Bookings
> >> >WHERE (((Bookings.AppointmentDate)=#" & date & "#));"
> >> >
> >> >The Date() function does not use () in VB. Also the SQL string need
> to
> >> concatenated
> >> >to include the system date. One thing you may want to consider is
that
> >> if
> >> >you code the SQL each time it is executed the database needs to create
> >the
> >> >query, there is some performance loss. If it is possible to store the
> >query
> >> >in Access that would probably be preferable.
> >> >
> >> >Marc
> >> >"Rab" <robert-dobbie@lineone.net> wrote:
> >> >>Hi there
> >> >>
> >> >>I have created a query in Access using the Microsoft Knowledge Base
> >Article
> >> >>: Q124152.
> >> >>
> >> >>This query works great for what I wish and I can call it thru Visual
> >Basic
> >> >>and it works fine.
> >> >>
> >> >>However, I would like to use this as a SQL select statement and use
> it
> >> in
> >> >my
> >> >>code as apposed to calling it from the Access Database it belongs to.
> >> >>
> >> >>The first query following the Article is -
> >> >>
> >> >>SELECT Bookings.FirstName, Bookings.LastName,
> >> >> Bookings.Day, Bookings.AppointmentDate,
> >> >> Bookings.Treatment, Bookings.Stylist,
> >> >> Bookings.Price, Bookings.AppointmentTime
> >> >>FROM Bookings
> >> >>WHERE (((Bookings.AppointmentDate)=Date()));
> >> >>and named DailyAppointments
> >> >>
> >> >>The second query based on the first query and another table is -
> >> >>
> >> >>SELECT Bookings.FirstName, Bookings.LastName,
> >> >> Bookings.Day, Bookings.AppointmentDate,
> >> >> Bookings.Treatment, Bookings.Stylist,
> >> >> Bookings.Price, Bookings.AppointmentTime
> >> >>FROM Bookings
> >> >>WHERE (((Bookings.AppointmentDate)=Date()));
> >> >>I have tried using the WHERE EXISTS keywords but this will only work
> >> >>providing one of the required times exist on a given date.
> >> >>I would like it to display all of the times table even if there is
not
> >> an
> >> >>appointment created for that day.
> >> >>
> >> >>This query works great in Access and Visual Basic the latter called
> as
> >> a
> >> >>View in the DataEnvironment Designer.
> >> >>Can someone please help me with the correct SQL.
> >> >>Regards
> >> >> Rab.
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >>
> >
> >
>

[marc]

Now I see the problem. I have run into this many times myself and have found
the two queries way to be very effective. However, if you can not store
the first query in the database, it will not work. This query should provide
the same results and can be executed from VB

SELECT times.times, (SELECT [FirstName] & " " & [LastName]
FROM Bookings
WHERE (((Bookings.AppointmentDate)=date()) AND ((Bookings.AppointmentTime)=Times.Times)))
AS Customer, (SELECT Bookings.Day
FROM Bookings
WHERE (((Bookings.AppointmentDate)=date()) AND ((Bookings.AppointmentTime)=Times.Times)))
AS AppointmentDay, (SELECT Bookings.AppointmentDate
FROM Bookings
WHERE (((Bookings.AppointmentDate)=date()) AND ((Bookings.AppointmentTime)=Times.Times)))
AS AppointmentDate, (SELECT Bookings.Treatment
FROM Bookings
WHERE (((Bookings.AppointmentDate)=date()) AND ((Bookings.AppointmentTime)=Times.Times)))
AS Treatment, (SELECT Bookings.Stylist
FROM Bookings
WHERE (((Bookings.AppointmentDate)=date()) AND ((Bookings.AppointmentTime)=Times.Times)))
AS Stylist, (SELECT Bookings.Price
FROM Bookings
WHERE (((Bookings.AppointmentDate)=date()) AND ((Bookings.AppointmentTime)=Times.Times)))
AS Price
FROM times;


It is ugly but it should work. Why can't you use a query from the database?

Marc



"Rab" <robert-dobbie@lineone.net> wrote:
>Yeah your right I have posted the same query twice - what a blithering
>idiot.
>
>Anyway here goes with both queries again -
>
>SELECT Bookings.FirstName, Bookings.LastName, Bookings.Day,
>Bookings.AppointmentDate, Bookings.Treatment, Bookings.Stylist,
>Bookings.Price, Bookings.AppointmentTime
>FROM Bookings
>WHERE (((Bookings.AppointmentDate)=date()));
>
>SELECT Times.Time, [Firstname] & " " & [LastName] AS Customer,
>DailyAppointments.Day, DailyAppointments.AppointmentDate AS [Date],
>DailyAppointments.Treatment, DailyAppointments.Stylist,
>DailyAppointments.Price
>FROM Times LEFT JOIN DailyAppointments ON Times.Time =
>DailyAppointments.AppointmentTime;
>
>These are the queries created using the MS Article Q124152.
>
>I would like to combine them into one select statement if possible that
can
>be called via VB code.
>
>Cheers
> Rab.
>
>"marc" <whale@ultranet.com> wrote in message
>news:3b5ddbca$1@news.devx.com...
>>
>> hello
>>
>> I think you posted the same query twice in your original post. You might
>> want to repost the second query.
>>
>> marc
>>
>> "Rab" <robert-dobbie@lineone.net> wrote:
>> >Hi Marc
>> >
>> >Thanx for that - but it does not help me with the two sql statements
>being
>> >brought together to produce one.
>> >
>> >I realise that it is probably better to call this statement from Access
>> >itself but I wished to know if it could be done in an SQL Select
>statement
>> >as it can in Access using the aforementioned Microsoft Article.
>> >
>> >Rab.
>> >
>> >
>> >"marc" <whale@ultranet.com> wrote in message
>> >news:3b5d6700$1@news.devx.com...
>> >>
>> >> Correction
>> >> "marc" <whale@ultranet.com> wrote:
>> >> >
>> >> >SQL = "SELECT Bookings.FirstName, Bookings.LastName,
>> >> > Bookings.Day, Bookings.AppointmentDate,
>> >> > Bookings.Treatment, Bookings.Stylist,
>> >> > Bookings.Price, Bookings.AppointmentTime
>> >> >FROM Bookings
>> >> >WHERE (((Bookings.AppointmentDate)=#" & date & "#));"
>> >> >
>> >> >The Date() function does not use () in VB. Also the SQL string need
>> to
>> >> concatenated
>> >> >to include the system date. One thing you may want to consider is
>that
>> >> if
>> >> >you code the SQL each time it is executed the database needs to create
>> >the
>> >> >query, there is some performance loss. If it is possible to store
the
>> >query
>> >> >in Access that would probably be preferable.
>> >> >
>> >> >Marc
>> >> >"Rab" <robert-dobbie@lineone.net> wrote:
>> >> >>Hi there
>> >> >>
>> >> >>I have created a query in Access using the Microsoft Knowledge Base
>> >Article
>> >> >>: Q124152.
>> >> >>
>> >> >>This query works great for what I wish and I can call it thru Visual
>> >Basic
>> >> >>and it works fine.
>> >> >>
>> >> >>However, I would like to use this as a SQL select statement and use
>> it
>> >> in
>> >> >my
>> >> >>code as apposed to calling it from the Access Database it belongs
to.
>> >> >>
>> >> >>The first query following the Article is -
>> >> >>
>> >> >>SELECT Bookings.FirstName, Bookings.LastName,
>> >> >> Bookings.Day, Bookings.AppointmentDate,
>> >> >> Bookings.Treatment, Bookings.Stylist,
>> >> >> Bookings.Price, Bookings.AppointmentTime
>> >> >>FROM Bookings
>> >> >>WHERE (((Bookings.AppointmentDate)=Date()));
>> >> >>and named DailyAppointments
>> >> >>
>> >> >>The second query based on the first query and another table is -
>> >> >>
>> >> >>SELECT Bookings.FirstName, Bookings.LastName,
>> >> >> Bookings.Day, Bookings.AppointmentDate,
>> >> >> Bookings.Treatment, Bookings.Stylist,
>> >> >> Bookings.Price, Bookings.AppointmentTime
>> >> >>FROM Bookings
>> >> >>WHERE (((Bookings.AppointmentDate)=Date()));
>> >> >>I have tried using the WHERE EXISTS keywords but this will only work
>> >> >>providing one of the required times exist on a given date.
>> >> >>I would like it to display all of the times table even if there is
>not
>> >> an
>> >> >>appointment created for that day.
>> >> >>
>> >> >>This query works great in Access and Visual Basic the latter called
>> as
>> >> a
>> >> >>View in the DataEnvironment Designer.
>> >> >>Can someone please help me with the correct SQL.
>> >> >>Regards
>> >> >> Rab.
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >>
>> >
>> >
>>
>
>

[Rab]

Hi marc

Yeah your right it does look ugly but I will give it a try.

It isn't so much that I can't use the query from the database as more of
thinking it might be more efficient, then when realising that it might not
and being relatively new to SQL, wanting to know if it can be done. You
have been able to prove both points to me.

Thanx again
Rab.
"marc" <whale@ultranet.com> wrote in message
news:3b5ebb14$1@news.devx.com...
>
> Now I see the problem. I have run into this many times myself and have
found
> the two queries way to be very effective. However, if you can not store
> the first query in the database, it will not work. This query should
provide
> the same results and can be executed from VB
>
> SELECT times.times, (SELECT [FirstName] & " " & [LastName]
> FROM Bookings
> WHERE (((Bookings.AppointmentDate)=date()) AND
((Bookings.AppointmentTime)=Times.Times)))
> AS Customer, (SELECT Bookings.Day
> FROM Bookings
> WHERE (((Bookings.AppointmentDate)=date()) AND
((Bookings.AppointmentTime)=Times.Times)))
> AS AppointmentDay, (SELECT Bookings.AppointmentDate
> FROM Bookings
> WHERE (((Bookings.AppointmentDate)=date()) AND
((Bookings.AppointmentTime)=Times.Times)))
> AS AppointmentDate, (SELECT Bookings.Treatment
> FROM Bookings
> WHERE (((Bookings.AppointmentDate)=date()) AND
((Bookings.AppointmentTime)=Times.Times)))
> AS Treatment, (SELECT Bookings.Stylist
> FROM Bookings
> WHERE (((Bookings.AppointmentDate)=date()) AND
((Bookings.AppointmentTime)=Times.Times)))
> AS Stylist, (SELECT Bookings.Price
> FROM Bookings
> WHERE (((Bookings.AppointmentDate)=date()) AND
((Bookings.AppointmentTime)=Times.Times)))
> AS Price
> FROM times;
>
>
> It is ugly but it should work. Why can't you use a query from the
database?
>
> Marc
>
>
>
> "Rab" <robert-dobbie@lineone.net> wrote:
> >Yeah your right I have posted the same query twice - what a blithering
> >idiot.
> >
> >Anyway here goes with both queries again -
> >
> >SELECT Bookings.FirstName, Bookings.LastName, Bookings.Day,
> >Bookings.AppointmentDate, Bookings.Treatment, Bookings.Stylist,
> >Bookings.Price, Bookings.AppointmentTime
> >FROM Bookings
> >WHERE (((Bookings.AppointmentDate)=date()));
> >
> >SELECT Times.Time, [Firstname] & " " & [LastName] AS Customer,
> >DailyAppointments.Day, DailyAppointments.AppointmentDate AS [Date],
> >DailyAppointments.Treatment, DailyAppointments.Stylist,
> >DailyAppointments.Price
> >FROM Times LEFT JOIN DailyAppointments ON Times.Time =
> >DailyAppointments.AppointmentTime;
> >
> >These are the queries created using the MS Article Q124152.
> >
> >I would like to combine them into one select statement if possible that
> can
> >be called via VB code.
> >
> >Cheers
> > Rab.
> >
> >"marc" <whale@ultranet.com> wrote in message
> >news:3b5ddbca$1@news.devx.com...
> >>
> >> hello
> >>
> >> I think you posted the same query twice in your original post. You
might
> >> want to repost the second query.
> >>
> >> marc
> >>
> >> "Rab" <robert-dobbie@lineone.net> wrote:
> >> >Hi Marc
> >> >
> >> >Thanx for that - but it does not help me with the two sql statements
> >being
> >> >brought together to produce one.
> >> >
> >> >I realise that it is probably better to call this statement from
Access
> >> >itself but I wished to know if it could be done in an SQL Select
> >statement
> >> >as it can in Access using the aforementioned Microsoft Article.
> >> >
> >> >Rab.
> >> >
> >> >
> >> >"marc" <whale@ultranet.com> wrote in message
> >> >news:3b5d6700$1@news.devx.com...
> >> >>
> >> >> Correction
> >> >> "marc" <whale@ultranet.com> wrote:
> >> >> >
> >> >> >SQL = "SELECT Bookings.FirstName, Bookings.LastName,
> >> >> > Bookings.Day, Bookings.AppointmentDate,
> >> >> > Bookings.Treatment, Bookings.Stylist,
> >> >> > Bookings.Price, Bookings.AppointmentTime
> >> >> >FROM Bookings
> >> >> >WHERE (((Bookings.AppointmentDate)=#" & date & "#));"
> >> >> >
> >> >> >The Date() function does not use () in VB. Also the SQL string
need
> >> to
> >> >> concatenated
> >> >> >to include the system date. One thing you may want to consider is
> >that
> >> >> if
> >> >> >you code the SQL each time it is executed the database needs to
create
> >> >the
> >> >> >query, there is some performance loss. If it is possible to store
> the
> >> >query
> >> >> >in Access that would probably be preferable.
> >> >> >
> >> >> >Marc
> >> >> >"Rab" <robert-dobbie@lineone.net> wrote:
> >> >> >>Hi there
> >> >> >>
> >> >> >>I have created a query in Access using the Microsoft Knowledge
Base
> >> >Article
> >> >> >>: Q124152.
> >> >> >>
> >> >> >>This query works great for what I wish and I can call it thru
Visual
> >> >Basic
> >> >> >>and it works fine.
> >> >> >>
> >> >> >>However, I would like to use this as a SQL select statement and
use
> >> it
> >> >> in
> >> >> >my
> >> >> >>code as apposed to calling it from the Access Database it belongs
> to.
> >> >> >>
> >> >> >>The first query following the Article is -
> >> >> >>
> >> >> >>SELECT Bookings.FirstName, Bookings.LastName,
> >> >> >> Bookings.Day, Bookings.AppointmentDate,
> >> >> >> Bookings.Treatment, Bookings.Stylist,
> >> >> >> Bookings.Price, Bookings.AppointmentTime
> >> >> >>FROM Bookings
> >> >> >>WHERE (((Bookings.AppointmentDate)=Date()));
> >> >> >>and named DailyAppointments
> >> >> >>
> >> >> >>The second query based on the first query and another table is -
> >> >> >>
> >> >> >>SELECT Bookings.FirstName, Bookings.LastName,
> >> >> >> Bookings.Day, Bookings.AppointmentDate,
> >> >> >> Bookings.Treatment, Bookings.Stylist,
> >> >> >> Bookings.Price, Bookings.AppointmentTime
> >> >> >>FROM Bookings
> >> >> >>WHERE (((Bookings.AppointmentDate)=Date()));
> >> >> >>I have tried using the WHERE EXISTS keywords but this will only
work
> >> >> >>providing one of the required times exist on a given date.
> >> >> >>I would like it to display all of the times table even if there is
> >not
> >> >> an
> >> >> >>appointment created for that day.
> >> >> >>
> >> >> >>This query works great in Access and Visual Basic the latter
called
> >> as
> >> >> a
> >> >> >>View in the DataEnvironment Designer.
> >> >> >>Can someone please help me with the correct SQL.
> >> >> >>Regards
> >> >> >> Rab.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >
> >> >>
> >> >
> >> >
> >>
> >
> >
>