Results 1 to 5 of 5
  1. #1
    Rpschwar is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    29

    Form with crosstab query


    I've got a crosstab query that returns students and there attendance record over a 4 week period. I want to display the data in a form but can not bound the details to the query. I can only bound the ID field. The date fields do not show up when I try to change the control source to each date returned from the query. I can do this if I create a report because I can unbound the 4 date fields and fill them in the Detail Format section. In a form there is no detail format section. The crosstab query returns something like:
    Student ID Week1 Week2 Week3 Week4
    1 x x x
    2 x x x x
    3 x x
    How would I get that to display in a form?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Not sure what you mean by 'unbound the 4 date fields and fill them'. You are using code to set ControlSource property?

    Here is example of dynamic crosstab reporting monthly date pivot http://www.fmsinc.com/MicrosoftAcces...ort/index.html

    Possibly adaptable for your weekly requirement. How do you assign the week identifier?

    Why need to display in form?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Rpschwar is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    29
    I know how to get it to work in a report because you can use the detail_format sub to put in the column data but you don’t have that option on a form. I can only use bound fields from the student profile table and can’t use bound to the attendance table. The date range could change.

  4. #4
    Rpschwar is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    29
    So I can do something after it is displayed without having to move from form to report back to form

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a form based on the query. In the header part have the date range for the user to enter. In the AfterUpdate of both the From and To date textboxes, set the filter based on those fields. The details of the form will be based on the query - student name, week1, week2, week3, week4. Those field names will be based on what is in the query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-04-2017, 11:40 AM
  2. Replies: 15
    Last Post: 12-30-2016, 12:26 PM
  3. Replies: 1
    Last Post: 03-26-2016, 08:23 PM
  4. How to link a form to a crosstab query
    By ManuelLavesa in forum Access
    Replies: 12
    Last Post: 09-21-2015, 09:46 AM
  5. Replies: 1
    Last Post: 07-05-2012, 11:34 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