Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2010
    Posts
    13

    Unanswered: Loop through each day

    Hi peops,

    I've got a booking table, I need to loop through each person in that table and look at their first booking of the day for a given date range. So what I think I need is to loop through each day within the date range, then do another loop per person. However, Access is not my strong point and I'm unsure how to go about this. I'm thinking:

    select * from tblperson

    If query has rows, loop through table

    Select * from tbl where personid = record of tblperson, order by date

    If query has rows, loop through each date

    for each day, move to first date.

    move to next record.

    Don't even know if this is the correct way to go about it and if what would be the syntax to make it loop through each day? The date value in the table is datetime datatype and is formated as such: 2010-01-29 13:15:00.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Without worrying about HOW you are going to do it (i.e. forget about looping etc.) please can you explain WHAT you are trying to do? Just in natural English without getting technical.

    For example "I want to display all people and their first booking in a given date range on a form".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2010
    Posts
    13
    Quote Originally Posted by pootle flump View Post
    Hi

    Without worrying about HOW you are going to do it (i.e. forget about looping etc.) please can you explain WHAT you are trying to do? Just in natural English without getting technical.

    For example "I want to display all people and their first booking in a given date range on a form".
    Thanks for your reply. I've basically got a table with bookings that are assigned to people. What I want to do, is for each person, update the first booking of their day. This process needs to be repeated each day within a given date range. Does that make more sense?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes that does thank you.

    Unfortunately, Access renders any query containing any aggregate function not updatable so although this would best be accomplished in a set based manner it is not possible to do so (unless we start dropping and creating tables).

    I think a combination of set based and recordsets is best.


    First step is to create a query that selects the appropriate rows.
    Making the required changes to column names, does this query return the client booking dates you are interested in?
    Code:
    SELECT bookings.client_id, Format([booking_date],"yyyy-mm-dd") AS booking_day, Min(bookings.booking_date) AS first_booking
    FROM bookings
    GROUP BY bookings.client_id, Format([booking_date],"yyyy-mm-dd")
    HAVING Format([booking_date],"yyyy-mm-dd") Between #1/1/2010# And #2/28/2010#
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2010
    Posts
    13
    Quote Originally Posted by pootle flump View Post
    Yes that does thank you.

    Unfortunately, Access renders any query containing any aggregate function not updatable so although this would best be accomplished in a set based manner it is not possible to do so (unless we start dropping and creating tables).

    I think a combination of set based and recordsets is best.


    First step is to create a query that selects the appropriate rows.
    Making the required changes to column names, does this query return the client booking dates you are interested in?
    Code:
    SELECT bookings.client_id, Format([booking_date],"yyyy-mm-dd") AS booking_day, Min(bookings.booking_date) AS first_booking
    FROM bookings
    GROUP BY bookings.client_id, Format([booking_date],"yyyy-mm-dd")
    HAVING Format([booking_date],"yyyy-mm-dd") Between #1/1/2010# And #2/28/2010#
    Ahh see what you're trying to do!

    SELECT tblbooking.CarerID, Format([startdatetime],"yyyy-mm-dd") AS booking_day, Min(tblbooking.StartDateTime) AS first_booking
    FROM tblbooking
    GROUP BY tblbooking.CarerID, Format([tblbooking_startdatetime],"yyyy-mm-dd")
    HAVING (((Format([tblbooking.startdatetime],"yyyy-mm-dd")) Between #1/1/2010# And #1/28/2010#));

    Created it as a query but when I try and get the datasheet it comes up with: You tried to execute a query that does not include the specified expression 'Format([Startdatetime]), "yyyy-mm-dd") as part of an aggregate function .. so couldn't really test the outcome!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have a look again at the SQL.
    tblbooking.startdatetime <> tblbooking_startdatetime
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2010
    Posts
    13
    Quote Originally Posted by pootle flump View Post
    Have a look again at the SQL.
    tblbooking.startdatetime <> tblbooking_startdatetime
    Excellent! Only thing that doesn't work there is the date range, I think it's returning all records, but I can play with that until I get it right, at least now I'm getting the first booking of the day per person per day. Thanks!

  8. #8
    Join Date
    Jan 2010
    Posts
    13
    Quote Originally Posted by pootle flump View Post
    Have a look again at the SQL.
    tblbooking.startdatetime <> tblbooking_startdatetime
    sqltxt = "SELECT tmpMileagebookings.bookingID, tmpMileagebookings.carerid, Format([startdatetime],"yyyy-mm-dd") AS booking_day, Min(tmpMileagebookings.StartDateTime) AS first_booking FROM tmpMileagebookings GROUP BY tmpMileagebookings.CarerID, Format([startdatetime],"yyyy-mm-dd")"
    Set qrycheckbookings = db.OpenRecordset(sqltxt, dbOpenSnapshot)

    Ok, this is what I've put in the module code now, it's not liking the syntax, saying compile error: expected: end of statement, and it is highlighting the yyyy bit of the first Format([startdatetime],"yyyy-mm-dd").. any ideas?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't have one set of double quotes within a string. The compiler will see:
    Code:
    sqltxt = "SELECT tmpMileagebookings.bookingID, tmpMileagebookings.carerid, Format([startdatetime],"
    as the string assignment and is now wondering what all the rest is after that.
    To include double quotes in a string assignment you must escape them. Or use single quotes.
    Code:
    sqltxt = "SELECT tmpMileagebookings.bookingID, tmpMileagebookings.carerid, Format([startdatetime],""yyyy-mm-dd"") AS booking_day, Min(tmpMileagebookings.StartDateTime) AS first_booking FROM tmpMileagebookings GROUP BY tmpMileagebookings.CarerID, Format([startdatetime],""yyyy-mm-dd"")"
    or
    Code:
    sqltxt = "SELECT tmpMileagebookings.bookingID, tmpMileagebookings.carerid, Format([startdatetime],'yyyy-mm-dd') AS booking_day, Min(tmpMileagebookings.StartDateTime) AS first_booking FROM tmpMileagebookings GROUP BY tmpMileagebookings.CarerID, Format([startdatetime],'yyyy-mm-dd')"
    BTW - code tags are very useful when posting code because it results in a fixed width font. Without them, '' and " look very similar. With them,
    Code:
    ''
    and
    Code:
    "
    look different.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2010
    Posts
    13
    Quote Originally Posted by pootle flump View Post
    You can't have one set of double quotes within a string. The compiler will see:
    Code:
    sqltxt = "SELECT tmpMileagebookings.bookingID, tmpMileagebookings.carerid, Format([startdatetime],"
    as the string assignment and is now wondering what all the rest is after that.
    To include double quotes in a string assignment you must escape them. Or use single quotes.
    Code:
    sqltxt = "SELECT tmpMileagebookings.bookingID, tmpMileagebookings.carerid, Format([startdatetime],""yyyy-mm-dd"") AS booking_day, Min(tmpMileagebookings.StartDateTime) AS first_booking FROM tmpMileagebookings GROUP BY tmpMileagebookings.CarerID, Format([startdatetime],""yyyy-mm-dd"")"
    or
    Code:
    sqltxt = "SELECT tmpMileagebookings.bookingID, tmpMileagebookings.carerid, Format([startdatetime],'yyyy-mm-dd') AS booking_day, Min(tmpMileagebookings.StartDateTime) AS first_booking FROM tmpMileagebookings GROUP BY tmpMileagebookings.CarerID, Format([startdatetime],'yyyy-mm-dd')"
    BTW - code tags are very useful when posting code because it results in a fixed width font. Without them, '' and " look very similar. With them,
    Code:
    ''
    and
    Code:
    "
    look different.
    Cheers for that, sorted my syntax out but just double checked the query, it seems to be returning more than just the first booking of the day per person, it is returning several later bookings as well as the first booking of the day per person.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How come you put bookingid in the query? I suspect that is your issue.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2010
    Posts
    13
    You're right, it is, anyway around that? I need the bookingid to update each of these bookings.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not necessarily.

    Can a client make two bookings at exactly the same time on the same day? If not, what stops them doing so? If so, how would you know which row to update?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2010
    Posts
    13
    I've got this function that makes sure that no booking is made at the same time for the same carer, by checking that the carer doesn't already have a booking reserved for the time period of the new booking.

    Would it be possible to change that select statement to an update statement at all? Sorry about this, programming not my forte at all, only did one year of it at Uni, years ago!

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please have a nose at this article. It is the best whistle-stop tour of relational database design I know of:
    The Relational Data Model, Normalisation and effective Database Design
    In particular you should be interested in any mention of keys (technical or surrogate keys, natural or intelligent or business keys, candidate keys etc.)

    I have to go now - I will give a more complete answer later. In short though - no - it is not possible in Access (although there is nothing in the relational model or SQL that prevents it, this is just an idiosyncrasy of Access).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •