Outer Join with Where clause returns Unexpected Records


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Outer Join with Where clause returns Unexpected Records

  1. #1
    Rab Guest

    Outer Join with Where clause returns Unexpected Records

    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.





  2. #2
    marc Guest

    Re: Outer Join with Where clause returns Unexpected Records


    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.
    >
    >
    >
    >



  3. #3
    marc Guest

    Re: Outer Join with Where clause returns Unexpected Records


    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.
    >>
    >>
    >>
    >>

    >



  4. #4
    Rab Guest

    Re: Outer Join with Where clause returns Unexpected Records

    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.
    > >>
    > >>
    > >>
    > >>

    > >

    >




  5. #5
    marc Guest

    Re: Outer Join with Where clause returns Unexpected Records


    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.
    >> >>
    >> >>
    >> >>
    >> >>
    >> >

    >>

    >
    >



  6. #6
    Rab Guest

    Re: Outer Join with Where clause returns Unexpected Records

    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.
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >
    > >>

    > >
    > >

    >




  7. #7
    marc Guest

    Re: Outer Join with Where clause returns Unexpected Records


    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.
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  8. #8
    Rab Guest

    Re: Outer Join with Where clause returns Unexpected Records

    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.
    > >> >> >>
    > >> >> >>
    > >> >> >>
    > >> >> >>
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center