Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33

    Unanswered: Data from 2 tables > report with INNER JOIN

    Hi all.

    I have a report in which I have a label, lblMondayMorning. I also have two tables, tblCustomers and tblBookings.

    tblCustomers fields: CustomerID, Name, Telephone
    tblBookings fields: CustomerID, EventDate, Session, EventTitle

    In lblMondayMorning, I would like to display 3 pieces of data:
    1st line: Name
    2nd line: EventTitle
    3rd line: Telephone

    I have another label, lblMondayDate, in which automatically calculates next monday's date.

    The SQL statement's criteria:
    EventDate = lblMondayDate.Caption
    Session = "Morning"

    I would like it to return these values and put them one under another in the label. If no bookings were found for this day/session, then it will say "No booking made" on the second line (centred).

    How can i do this through SQL code? I think it requires the use of INNER JOIN.

    Any ideas much appreciated

    Regards,
    Tom

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Per a request from Stibily, I've moved this posting/thread back into the forums at DBForums from the UseNet.

    -PatP

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It does indeed require an INNER JOIN. You may want to google on the fundamentals of SQL. If you don't understand INNER JOINS, right now would be an excellent time to take a few moments to familiarize yourself with the most fundamental of all database concepts.

    There is a tricky bit here as well. What determines the value that goes in the "monday label"?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Quote Originally Posted by Teddy
    What determines the value that goes in the "monday label"?
    Here's what I have used to calculate next Monday's date...
    Code:
     
    If (Weekday(Int(Now))) = 1 Then
        lblWeekDates.Caption = Int(Now) + 2 & " - " & Int(Now) + 8
        lblMondayDate.Caption = Int(Now) + 2
        lblTuesdayDate.Caption = Int(Now) + 3
        lblWednesdayDate.Caption = Int(Now) + 4
        lblThursdayDate.Caption = Int(Now) + 5
        lblFridayDate.Caption = Int(Now) + 6
        lblSaturdayDate.Caption = Int(Now) + 7
        lblSundayDate.Caption = Int(Now) + 8
    ElseIf (Weekday(Int(Now))) = 2 Then
        lblWeekDates.Caption = Int(Now) + 1 & " - " & Int(Now) + 7
        lblMondayDate.Caption = Int(Now) + 1
        lblTuesdayDate.Caption = Int(Now) + 2
        lblWednesdayDate.Caption = Int(Now) + 3
        lblThursdayDate.Caption = Int(Now) + 4
        lblFridayDate.Caption = Int(Now) + 5
        lblSaturdayDate.Caption = Int(Now) + 6
        lblSundayDate.Caption = Int(Now) + 7
    ElseIf (Weekday(Int(Now))) = 3 Then
        lblWeekDates.Caption = Int(Now) + 7 & " - " & Int(Now) + 13
        lblMondayDate.Caption = Int(Now) + 7
        lblTuesdayDate.Caption = Int(Now) + 8
        lblWednesdayDate.Caption = Int(Now) + 9
        lblThursdayDate.Caption = Int(Now) + 10
        lblFridayDate.Caption = Int(Now) + 11
        lblSaturdayDate.Caption = Int(Now) + 12
        lblSundayDate.Caption = Int(Now) + 13
    ElseIf (Weekday(Int(Now))) = 4 Then
        lblWeekDates.Caption = Int(Now) + 6 & " - " & Int(Now) + 12
        lblMondayDate.Caption = Int(Now) + 6
        lblTuesdayDate.Caption = Int(Now) + 7
        lblWednesdayDate.Caption = Int(Now) + 8
        lblThursdayDate.Caption = Int(Now) + 9
        lblFridayDate.Caption = Int(Now) + 10
        lblSaturdayDate.Caption = Int(Now) + 11
        lblSundayDate.Caption = Int(Now) + 12
    ElseIf (Weekday(Int(Now))) = 5 Then
        lblWeekDates.Caption = Int(Now) + 5 & " - " & Int(Now) + 11
        lblMondayDate.Caption = Int(Now) + 5
        lblTuesdayDate.Caption = Int(Now) + 6
        lblWednesdayDate.Caption = Int(Now) + 7
        lblThursdayDate.Caption = Int(Now) + 8
        lblFridayDate.Caption = Int(Now) + 9
        lblSaturdayDate.Caption = Int(Now) + 10
        lblSundayDate.Caption = Int(Now) + 11
    ElseIf (Weekday(Int(Now))) = 6 Then
        lblWeekDates.Caption = Int(Now) + 4 & " - " & Int(Now) + 10
        lblMondayDate.Caption = Int(Now) + 4
        lblTuesdayDate.Caption = Int(Now) + 5
        lblWednesdayDate.Caption = Int(Now) + 6
        lblThursdayDate.Caption = Int(Now) + 7
        lblFridayDate.Caption = Int(Now) + 8
        lblSaturdayDate.Caption = Int(Now) + 9
        lblSundayDate.Caption = Int(Now) + 10
    ElseIf (Weekday(Int(Now))) = 7 Then
        lblWeekDates.Caption = Int(Now) + 3 & " - " & Int(Now) + 9
        lblMondayDate.Caption = Int(Now) + 3
        lblTuesdayDate.Caption = Int(Now) + 4
        lblWednesdayDate.Caption = Int(Now) + 5
        lblThursdayDate.Caption = Int(Now) + 6
        lblFridayDate.Caption = Int(Now) + 7
        lblSaturdayDate.Caption = Int(Now) + 8
        lblSundayDate.Caption = Int(Now) + 9
    End If
    Regards,
    Tom

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    Tom

    Could that code not be replaced with this?
    Code:
       lblWeekDates.Caption = Date + Weekday(Date) - 2 & " - " & Date + Weekday(Date) + 4
        
        lblMondayDate.Caption = Date + Weekday(Date) - 2
        lblTuesdayDate.Caption = Date + Weekday(Date) - 1
        lblWednesdayDate.Caption = Date + Weekday(Date)
        lblThursdayDate.Caption = Date + Weekday(Date) + 1
        lblFridayDate.Caption = Date + Weekday(Date) + 2
        lblSaturdayDate.Caption = Date + Weekday(Date) + 3
        lblSundayDate.Caption = Date + Weekday(Date) + 4

  6. #6
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Hi norie.

    I just tested my original code and I just noticed that it doesn't work. Today's date is 13/3/06 and my code is supposed to calculate next week's dates. Atm, it's displaying 14/3/06 - 20/3/06, infact, it should be 20/3/06 - 26/3/06. Is this a problem with first day of week? If so, how can I set it to Monday in my code - do I use Format$(...)? To confirm that this may be the problem, I changed my system date to tomorrow's date (14/3/06) and it displayed 21/3/06 - 27/3/06 with my code. With yours, 15/3/06 - 21/3/06.

    I also tested your code and that displays 13/3/06 - 19/3/06.

    Something weird is happening and I can't spot it

    Any ideas?

    Regards,
    Tom

  7. #7
    Join Date
    Mar 2006
    Posts
    163
    Tom

    So you want to return the dates for Monday-Sunday of the following week?

    If so you should be able to just add 7 to my code.
    Code:
    lblWeekDates.Caption = Date + Weekday(Date) - 5 & " - " & Date + Weekday(Date) + 11
        
        lblMondayDate.Caption = Date + Weekday(Date) - 5
        lblTuesdayDate.Caption = Date + Weekday(Date) - 6
        lblWednesdayDate.Caption = Date + Weekday(Date) + 7
        lblThursdayDate.Caption = Date + Weekday(Date) + 8
        lblFridayDate.Caption = Date + Weekday(Date) + 9
        lblSaturdayDate.Caption = Date + Weekday(Date) + 10
        lblSundayDate.Caption = Date + Weekday(Date) + 11

  8. #8
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Ok, I know have the right dates set. Norie, your second set of code was close, just had to change it to...
    Code:
     
    lblWeekDates.Caption = Date + Weekday(Date) + 5 & " - " & Date + Weekday(Date) + 11
     
        lblMondayDate.Caption = Date + Weekday(Date) + 5
        lblTuesdayDate.Caption = Date + Weekday(Date) + 6
        lblWednesdayDate.Caption = Date + Weekday(Date) + 7
        lblThursdayDate.Caption = Date + Weekday(Date) + 8
        lblFridayDate.Caption = Date + Weekday(Date) + 9
        lblSaturdayDate.Caption = Date + Weekday(Date) + 10
        lblSundayDate.Caption = Date + Weekday(Date) + 11
    Thanks for simplifying it Works a treat!



    Now that I have the correct dates, how do I go about the Inner Join method? I had a crack at it myself, but couldnt get the correct result...

    Code:
    strMondayMorningEventTitle = "SELECT tblCustomers.Name, tblBookings.EventTitle, tblCustomers.Telephone FROM tblCustomers INNER JOIN tblBookings ON (((tblBookings.CustomerID) = (tblCustomers.CustomerID)) AND ((tblBookings.EventDate) = lblMondayDay.Caption) AND ((tblBookings.Session) = 'Morning'));"
    Set rstRecordset = CurrentDb.OpenRecordset(strMondayMorningEventTitle)
    lblMondayMorning.Caption = IIf(IsNull(rstRecordset!EventTitle), "", rstRecordset!EventTitle)
    I tried hardcoding the date #20/03/2006# replacing lblMondayDate.Caption and it retreives just the event title.

    Regards,
    Tom
    Last edited by stibily; 03-13-06 at 14:00.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the code looks way to complex to achieve the desired result --- ok, that mostly means i didn't read it, so i'l have a look now.

    meanwhile, A (like most programming on the planet) likes US format dates (...unlike the majority of people on the planet).

    in case it cracks the problem, try
    #03/20/2006#
    in place of your
    #20/03/2006#

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    actually, the code looks good - sorry to doubt it.

    it could probably be looped thru the label names, but it's hardly worth the effort.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Mar 2006
    Posts
    163
    Tom

    izy has a code point regarding the dates.

    See what happens when you type something like this into Access VBA.
    Code:
    x = #1 Nov 2006#
    For your query perhaps try reformatting the date to include the month name, and also enclose it in #'s to indicate it is a date value.

  12. #12
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    I entered #1 Nov 2006# into the SQL and because it found no matches (as there are no bookings for that date/session) it then went down to the IIf(IsNull.... line and gave an error message saying 'Type mismatch.' I have supplied a sample db, perhaps it will be easier for you to understand.

    Regards,
    Tom
    Attached Files Attached Files
    Last edited by stibily; 03-13-06 at 16:50.

  13. #13
    Join Date
    Mar 2006
    Posts
    163
    Tom

    I downloaded the sample and opened the report.

    I didn't receive any errors, but I don't actually know if it's displaying what it should.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ditto

    attached is a poss alternate route ...incomplete because unbooked days don't show. see Report1

    this is not my playing field so sorry i can't offer more.

    izy
    Attached Files Attached Files
    currently using SS 2008R2

  15. #15
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Norie - the sample I provided you retrieves the Event Title but does not return the Name of customer and their telephone no. You can see this in action in Monday Morning session.

    Izyrider - Thanks for the sample you've provided - it's a slightly different way that I intended (the use of a crosstab query), but I guess it doesn't matter too much. I'll have a play around and implement your ideas.

    Also, in the sample db i provided, under rptBookingsList, I used the following code for the Title caption...
    Code:
    dtmTitleMonth = DatePart("m", lblMondayDate.Caption)
    lblTitle.Caption = "Village Hall :: Hall Usage :: " & Format$(dtmTitleMonth, "MMMM YYYY", vbMonday)
    For some reason, it comes up with January 1900!!! Why does it not display February 2006.

    Much appreciated,
    Tom
    Last edited by stibily; 03-14-06 at 16:23.

Posting Permissions

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