Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45

    Crosstab Query within another query producing blank result

    Attempting to produce a form that displays Attendance record for a course that is six days long (day1 - day6)



    When I hard code the SerialID to 277 I get the desired result

    SELECT qryAttendance_Crosstab.StudentCourseID, Course.CourseID, Course.Course, Serial.SerialID, Serial.Serial, Student.StudentID, IIf(IsNull([Student]![ProperFirstName]),StrConv([Student]![FirstNames],3),[Student]![ProperFirstName]) & " " & IIf(IsNull([Student]![ProperLastName]),StrConv([Student]![LastName],3),[Student]![ProperLastName]) AS Name, qryAttendance_Crosstab.[1], qryAttendance_Crosstab.[2], qryAttendance_Crosstab.[3], qryAttendance_Crosstab.[4], qryAttendance_Crosstab.[5], qryAttendance_Crosstab.[6], qryAttendance_Crosstab.[Total Of Hours], StudentCourse.CourseStatus
    FROM Student INNER JOIN (Course INNER JOIN ((qryAttendance_Crosstab INNER JOIN StudentCourse ON qryAttendance_Crosstab.StudentCourseID = StudentCourse.StudentCourseID) INNER JOIN Serial ON StudentCourse.SerialID = Serial.SerialID) ON Course.CourseID = StudentCourse.CourseID) ON Student.StudentID = StudentCourse.StudentID
    WHERE (((Serial.SerialID)=277) AND ((StudentCourse.CourseStatus)="Registered" Or (StudentCourse.CourseStatus)="In Progress" Or (StudentCourse.CourseStatus)="Completed" Or (StudentCourse.CourseStatus)="Failed Exam" Or (StudentCourse.CourseStatus)="Failed Course"))
    ORDER BY Student.LastName, Student.FirstNames;

    But when I attempt to bring this value from a drop down menu at the top of the page I get a blank form - (I even added the Parameters section)

    PARAMETERS [Forms]![frmAttendance]![cboSerial] Long;
    SELECT qryAttendance_Crosstab.StudentCourseID, Course.CourseID, Course.Course, Serial.SerialID, Serial.Serial, Student.StudentID, IIf(IsNull([Student]![ProperFirstName]),StrConv([Student]![FirstNames],3),[Student]![ProperFirstName]) & " " & IIf(IsNull([Student]![ProperLastName]),StrConv([Student]![LastName],3),[Student]![ProperLastName]) AS Name, qryAttendance_Crosstab.[1], qryAttendance_Crosstab.[2], qryAttendance_Crosstab.[3], qryAttendance_Crosstab.[4], qryAttendance_Crosstab.[5], qryAttendance_Crosstab.[6], qryAttendance_Crosstab.[Total Of Hours], StudentCourse.CourseStatus
    FROM Student INNER JOIN (Course INNER JOIN ((qryAttendance_Crosstab INNER JOIN StudentCourse ON qryAttendance_Crosstab.StudentCourseID = StudentCourse.StudentCourseID) INNER JOIN Serial ON StudentCourse.SerialID = Serial.SerialID) ON Course.CourseID = StudentCourse.CourseID) ON Student.StudentID = StudentCourse.StudentID
    WHERE (((Serial.SerialID)=[Forms]![frmAttendance]![cboSerial]) AND ((StudentCourse.CourseStatus)="Registered" Or (StudentCourse.CourseStatus)="In Progress" Or (StudentCourse.CourseStatus)="Completed" Or (StudentCourse.CourseStatus)="Failed Exam" Or (StudentCourse.CourseStatus)="Failed Course"))
    ORDER BY Student.LastName, Student.FirstNames;

    Any ideas would be most generously received as I have struggled with this for quite some time now

    Rick

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    it could be the issue is with your cboSerial control.

    What is the rowsource?
    What is the bound column?

  3. #3
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    Here is the SQL statement from cboSerial

    SELECT Serial.SerialID, Serial.Year, Serial.Serial, Course.Course, IIf(([StartDate]-Date())+13>=0,(100000-([StartDate]-Date())-13),(10000-([StartDate]-Date())-13)) AS Daycalc
    FROM Course INNER JOIN Serial ON Course.CourseID = Serial.CourseID
    WHERE (((Serial.Year)=[Forms]![frmAttendance]![cboYear]) AND ((Course.Course)=[Forms]![frmAttendance]![cboCourse]))
    ORDER BY IIf(([StartDate]-Date())+13>=0,(100000-([StartDate]-Date())-13),(10000-([StartDate]-Date())-13)) DESC;

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    and the bound column is?

    And just to confirm the data, if you open the rowsource as a query, can you see 277 in the first column?


    Also you seem to have tables with fields the same name as the table - it may or may not be the reason, but it will probably cause issues down the line if you don't change either the table or the field name. And Year is a reserved word which also will cause you problems at some point. If it is because of how you want form labels to be populated, use the field caption property, that is what it is for.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    you can also simplify your sql

    change this

    ((StudentCourse.CourseStatus)="Registered" Or (StudentCourse.CourseStatus)="In Progress" Or (StudentCourse.CourseStatus)="Completed" Or (StudentCourse.CourseStatus)="Failed Exam" Or (StudentCourse.CourseStatus)="Failed Course"))

    to

    ((StudentCourse.CourseStatus) IN ("Registered","In Progress" ,"Completed" ,"Failed Exam" ,"Failed Course"))

  6. #6
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    Thanks for the pointers - will make those changes

    Bound Column = 1

    Took screenshot of drop down menu with all columns displayed if that helps


    Click image for larger version. 

Name:	Untitled-1.jpg 
Views:	15 
Size:	16.1 KB 
ID:	29395

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    too small to read, but I'm guessing there are 5 columns and the row you have highlighted has 277 in the first column

    So next questions - where is the sql? in a query? the form recordsource? a subform recordsource?

    Are you just opening the query manually? or what VBA code are you using to refresh the query? And where is the code? the combo afterupdate event? a button click event? somewhere else?

    It's gone 2am here, so now signing off, do respond, someone else may take up the cause, Or I'll respond when I'm back on line

  8. #8
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    Thanks for everything this far as I am always learning

    Take a look and see if you can find the problem - It has been stripped down but is still too large to attach

    Start by opening frmMenuMSGTP and then the Attendance Button which will populate the combo boxes with the correct values - it is currently loaded with "277" in the query so that you can see what it should look like when it works
    When I put in [Forms]![frmAttendance]![cboSerial] I get the blank screen
    Last edited by Rickochezz; 07-13-2017 at 05:38 AM. Reason: link removed

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    Doesn't seem to be anything in there except for a compact errors table. You can't post a copy here even after compacting, then zipping a copy? Zipping is the only way to get the size small enough in about 95% of the cases here (OK, that's a guess).

    All I can see that is suspect is this:
    your query suggests the form to be referenced is [Forms]![frmAttendance]![cboYear], yet your last post suggests it is frmMenuMSGTP.
    So my conclusion is that either you are referencing the wrong form, or the correct form but it is not open, or I'm not understanding the clues.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    Try this zipped version and see if that helps
    Attached Files Attached Files

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    the problem is not with the query but your requery in cboSerial afterupdate event. Not sure why because it should rerun the query on which the form is based. It might have something to do with also having embedded macros in the form, but I don't see why this would be the case, but the only oddity I can see. If you have the attendance form open, select aa serial then open qryAttendance, it shows the right data - it just isn't being reflected in the form. As I say, I don't know why.

    Changing it to

    Me.RecordSource = Me.RecordSource

    works as a fix

    don't forget to put [Forms]![frmAttendance]![cboSerial] back into your criteria

    Or remove the criteria and in the cboSerial afterupdate event put
    Me.Filter = "SerialID=" & cboSerial
    Me.FilterOn = True

    which also works

  12. #12
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    AJax:

    I really appreciate when people take time out of their days to assist me and better yet when I can actually understand what is being asked and I can get the problem solved.

    I would also like to thank you for all the other pointers that you supplied me with.

    Cheers.

    Rick

  13. #13
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    Unfortunately - everything works as I was wanting

    The reason that I am saying unfortunately is that I am not able to edit any of the info. I'm thinking that it has to do with a crosstab query.
    Any ideas on how I can get around this. As always any advice is welcome.

    Thanks again all

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    ah - you didn't say about editing a crosstab.

    Crosstabs are not intended to be edited, but you can find a solution on this thread which may meet your needs

    https://www.accessforums.net/showthr...light=crosstab

    start around post#6 - read the lot before jumping in because the OP asked questions which needed clarifying.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    Quote Originally Posted by Rickochezz View Post
    Unfortunately - everything works as I was wanting
    The reason that I am saying unfortunately is that I am not able to edit any of the info. I'm thinking that it has to do with a crosstab query.
    Any ideas on how I can get around this. As always any advice is welcome.
    Thanks again all
    I don't get it. When I use the forms! reference in the attendance query, I get varying results based on the serial combo. Most produce a result, but a few don't. That would mean those serial values have no associated records. However, all the controls in the detail section have value lists for data - thus none of them are bound to the fields that you've specified as the record source for the form. If those controls have value lists as their data source, there's no way they're going to be updatable.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 01-11-2017, 03:27 PM
  2. Query Producing Blank Rows? Fault IIf Logic?
    By kestefon in forum Access
    Replies: 6
    Last Post: 12-18-2013, 06:13 PM
  3. CrossTab Query - Blank Values
    By bullwinkle55423 in forum Queries
    Replies: 4
    Last Post: 07-17-2013, 01:59 PM
  4. Replies: 15
    Last Post: 10-22-2012, 06:06 PM
  5. Replies: 8
    Last Post: 05-16-2012, 09:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums