Results 1 to 11 of 11
  1. #1
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59

    reporting

    I have a database that has a main table with 4-5 other tables related to the main table. Each of these tables is a particular class (Math, science, english etc) In each of these tables there are a number of fields that denote a lesson. As the student completes these the person checks a check box and puts a date of completion. NOTE: They completed lessons will NOT go in order...they could do lesson 1, and lesson 9.

    Example: say these are the fields selected in the tables

    Math
    Lesson1 checkbox
    Lesson1 Date
    Lesson2 checkbox


    Lesson2 date
    Lesson5 checkbox
    Lesson5 date
    etc

    Science
    Lesson3 checkbox
    Lesson3 Date
    Lesson6 checkbox
    Lesson6 date
    etc

    What i need is a report that:
    1. will allow the user to input a date (start date) and then using Now() get a date range. ONLY include field data that falls within the date range.
    2. In th is singlereport I need sections that represent each table/class (math, science etc.) BUT i dont want to return all the fields with their dates. ONLY the fields that checkbox "Is Not Null" and the date is within the date range specified. i dont want the report to report back the fields that do not meet this criteria.

    So something like this

    Report for 6/1/2011 to current:
    Student info
    Address
    other student info

    Math:
    Lesson1 2-2-2012
    Lesson2 2-1-2012
    Lesson5 2-1-2012

    Science
    Lesson3 2-2-2012
    Lesson6 2-1-2012

    on the form I will have to start date and the Student ID number. So the query will have to refrence these...then find only the fields (lessons) that have the checkbox checked and the date completed that falls within the date range.

    Do I create individual querys and then put those inside a main report? The problem is when I place the queries inside the main report I cannot specifiy breaks...I dont know how many records will be returned and they will vary for each student. so the spacing has to be dynamic. so the inserted queries dont overlap.

    Should I do this in code with VBA with SQL queries?

    Any thoughts on where to start would be greatly appreciated.

    Thanks

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't know at what stage in the development of the database you are, but it would help you greatly if the data were normalized instead of the way it is specified above. Each lesson would be on a separate table and then the tables would already look like the report and creating any forms, queries or reports against the data would be a simple task. As it is, you would need to create a temporary table to hold the data in the way you want to report on it - either using VBA or a bunch of Append queries.

  3. #3
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    First off thank you for your responce...

    Lets Start with the tables..I am at the very beginning of this...If I am reading you corrently....if I made a table for each lesson I would end up with 500-600 tables....there are 50-100 lessons for math, 50-100 for Science, 50-100 Lessons for mapping etc...
    Right now I have a table for each Math, Science, Mapping with the 50-100 fields each refering to each lesson for that Main topic (math, science, mapping). Is this what you are refering too?

    I dont know how I would make one table with each student as they could have taken different lessons so the fields would be different or many fields inclouded that did not have values for some of the students...these blank fields would then be probigated to the report...

    So the tables...lets start there...you refering to creating a table for each topis and then fields for each lesson for that topic (math topics will be different than mapping lessons, the numbe rof lessons will also vary between topics.)

    THanks

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Table 1 : Students
    -StudentID, student info
    Table 2 : Math
    -MathID, Lesson Number, Description
    Table 3 : Science
    -etc
    Table 4 : Details
    - StudentID, MathID, ScienceID, Date

    The first three tables are your "parent" or "master" tables and the 4th one will have one record for every time a student takes a class. If you go back to the report structure above, the query would pull in all the data from these tables and you would have to include an extra sort key for "Math", etc. Do you know the different join types you can use in queries? So when linking from the detail table to the Math table (for example) you would include all records from the detail table and only those from the Math table if they exist.

    I'm kinda doing this from the top of my head, hope it makes sense. Set it up if you can and then attach the database if you need further assistance.

  5. #5
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    OK I think I am starting to understand a bit but have a few more questions.
    I attached a copy of the database in a basic format. Main Table with 3 Class Tables and a Details table. I think this is the format you were refering to.

    I am having a hard time seeing the larger picture.
    What I want is a form that the user can select a Student (say from a drop down) then tab between forms for Math, Reading and Science...in each of these forms select the presented and completed lessons and specifiy a date for presentned and completed.

    Finally print a report for a single student that shows their info and then 3 seperate sections shoing the Lessons presented and completed for each....as seen in my report layout above.

    So I think I have the tables correct....just need a little kick to get it layed out correctly...ie my next step.....

    Thank you very much for your help...its greatly appreciated.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I am a programmer and I may have made this more complicated than you can deal with. Sorry about that, all I can do is this. As you become more comfortable with Access you can maybe find easier/less complex ways of doing it.

    Notes:
    1)Your "master" tables cannot contain transaction/changing data, so the dates had to be removed. They are static tables and will basically never change - unless your curriculum changes.
    2) Once you have designed your tables, always go into the Relationships next and join them together. I gives you a good visual effect of your table design and one often finds that there are holes in the design.
    3) There is a form with a subform for entering data.
    4) There is a query joining it all together. You can use something like this to create your reports (use the wizard).
    5) I have not made it pretty!
    6) I'm still not sure about the design - it is strange to me that you enter all three on the same day at the same time and some can be blank. But as you get further with this project you may find a different solution.

  7. #7
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Thanks for your help...Your confusion where some can be blank....this is a classroom that does not follow conventional teaching...the children in the class room do different lessons and activities at different times...they are not structured in levels of difficulty...so lesson 9 is not harder than lesson 2...its simply a different lesson...the children can choose their lesson...as such each child will complete the lessons at differnt times...that make sense?

    Just to get your forms right in my head....
    1. I assume the Main form is just looking at the Studentinfo table with a combobox (set in the Row Source) , OnClick it filters the form to that student
    2. How was the Subform created....based off of the details table? How is this subform being filtered to the single student from the dropdown

    I thank you for your help here...just trying to see the larger picture of what you have going on...your help is very appreciated.

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1 - see the record source for the form or subform to understand where the data is coming from
    2 - see (1).
    The subform is linked to the main form thru the form properties called "Link...", that is how they are tied together. Click on the subform (once, so that just the outlines of the subform are highlighted) and look at the properties.

    I will not be offended if what I have given you is not right - feel free to pick it apart and say it doesn't work the way you want it to. I was just showing you one way of doing it, it may not be the right way, however.

  9. #9
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Ahh yea that makes sense....I was looking right at the Record Source...I think I needed someone to hit me with a bat...
    The linked Sub Form to form makes sense as well....I was looking for it but was looking for it on the properties of the subform alone....not in the subform within gthe main form.....again someone hit me with a bat....

    No this is great...It makes sense...I see a few things that I have change...I need to create a Sub form for each section (math, reading, science) that will just be creating a couple more forms and hiding them and showing them (maybe tabs)...but this is a great start...I have the report workign fine....

    Created a report for the lessons and the main Student info...Simply dragged teh Lesson report into the Student info report...
    Got the Lesson info to appear like this...

    SELECT "Math" AS Expr1, Query1.StudentID, Query1.StudentInfo1, Query1.MathID, Query1.MathLessonNumber, Query1.MathDescription, "Date P: " AS Expr2, Query1.DatePresented, "Date C: " AS Expr3, Query1.DateCompleted
    FROM Query1
    WHERE (((Query1.StudentID) Like [Forms]![EnterDetailInfo_frm]![Text18]) AND ((Query1.MathID) Is Not Null) AND ((Query1.DatePresented)>Now()-30))
    UNION
    SELECT "Reading" AS Expr1, Query1.StudentID, Query1.StudentInfo1, Query1.ReadingID, Query1.ReadLessonNumber, Query1.ReadDescription, "Date P: " AS Expr2, Query1.DatePresented, "Date C: " AS Expr3, Query1.DateCompleted
    FROM Query1
    WHERE (((Query1.StudentID) Like [Forms]![EnterDetailInfo_frm]![Text18]) AND ((Query1.ReadingID) Is Not Null) AND ((Query1.DatePresented)>Now()-30))
    UNION SELECT "Science" AS Expr1, Query1.StudentID, Query1.StudentInfo1, Query1.ScienceID, Query1.ScienceLessonNumber, Query1.ScienceDescription, "Date P: " AS Expr2, Query1.DatePresented, "Date C: " AS Expr3, Query1.DateCompleted
    FROM Query1
    WHERE (((Query1.StudentID) Like [Forms]![EnterDetailInfo_frm]![Text18]) AND ((Query1.ScienceID) Is Not Null) AND ((Query1.DatePresented)>Now()-30))
    ORDER BY Query1.tbl_Details.StudentID;


    MY LAST QUESTION....I have been messing around with the date field for Presented....eventually I want the user to be able to specify a date range and then apply that to the report....so they can get the lessons from the first 6 months of 2011 if they choose....I can do the above and grab a specifc number of days prior to todays date...but having a hard time with syntax to grab a value from the form itself.
    Say I have to textboxes that have dates...the second defaulting to todays date...
    How could I replace this with the two values on the Form holding the user specified date???

    ((Query1.DatePresented)>Now()-30))

    I can get Something like this to work but how do I do a date range between two textbox date values?
    WHERE (((Query1.StudentID) Like [Forms]![EnterDetailInfo_frm]![Text18]) AND ((Query1.MathID) Is Not Null) AND ((Query1.DatePresented)>[Forms]![EnterDetailInfo_frm]![TxtDate] ))

    Is there a between syntax for dates?

    AND ((Query1.MathID) Is Not Null) AND ((Query1.DatePresented) Between [Forms]![EnterDetailInfo_frm]![StartDate] & [Forms]![EnterDetailInfo_frm]![EndDate] ))

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Almost perfect:
    ((Query1.DatePresented) Between [Forms]![EnterDetailInfo_frm]![StartDate] AND [Forms]![EnterDetailInfo_frm]![EndDate] ))
    The "&" is used for concatenating two values or variables to make them one, which is not what you are doing here.

    It bothers me that you are making separate subforms for each class type. In that case they should be different tables. That is "normalization". Like having a table for DetailsMaths, etc. But well done for getting as far as you have!

  11. #11
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Yea I hear you on the seperate tables....something that I might do....I have it working now in a very basic way..thanks to your help....Very appreciated....Tiem to rip it apart and start over and create multiple tables like you mentioned.

    Date range is working great...
    The report looks great...
    more than enough to get going on this project...and a great new way to look at Master tables and the details...Sort of like lookup tables....

    THANK YOU VERY MUCH....owe you a cold one.....Thanks

    I ATTACHED THE WHOLE DATABASE IF ANYONE WANTS IT.....

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

Similar Threads

  1. Complex reporting (well for me anyway)
    By Rubijon in forum Reports
    Replies: 4
    Last Post: 11-09-2011, 08:01 PM
  2. Query reporting help
    By kwooten in forum Queries
    Replies: 5
    Last Post: 06-10-2011, 07:03 AM
  3. Exception Reporting
    By shexe in forum Queries
    Replies: 16
    Last Post: 09-09-2010, 09:14 AM
  4. Question on Reporting
    By jbarrum in forum Access
    Replies: 20
    Last Post: 01-14-2010, 02:05 PM
  5. Dynamic reporting
    By pushpm in forum Programming
    Replies: 0
    Last Post: 04-22-2009, 12:45 PM

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 - Senior Forums