Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2014
    Posts
    20

    Unanswered: Simple novice question on getting my data from parameter query into a report

    I know this is most likely a very simple novice request, but Iíve banged my head on this for a few days and canít figure it out: I have an Access 2007 database table which lists the attendance for each class and on each date for a school. I have a working query to select the 1st most recent record by date (i.e. most recent class attendance) which requires an input parameter from the user (type in the class name). I want to run a report in which I list all the classes with their most recent attendance, and I attached the query and the class name from my table. However, the query I have written still requires the input box parameter instead of producing a list of all classes and the most recent attendances:

    Date Class Attendance
    1/10/2014 Albertson 28
    1/10/2014 Nomura 34
    1/10/2014 Reynolds 33
    1/10/2014 Maplebaum 25


    The query I have that works is:

    SELECT TOP 1 *
    FROM ClassData
    WHERE Class = ClassName
    ORDER BY Date DESC;

    The database is schooldata.mdb
    The table is ClassData
    The Class is in ClassData.Class
    The Date is in ClassData.Date
    The Attendance is in ClassData.Attendance
    (Each Class in ClassData has many other columns beyond Attendance and Date such as ClassData.Absences.)
    For any date and class there will be only one attendance number.

    An example of the table is below:

    Date Class Attendance
    1/7/2014 Albertson 22
    1/7/2014 Nomura 26
    1/7/2014 Reynolds 25
    1/7/2014 Maplebaum 32
    1/8/2014 Albertson 25
    1/8/2014 Nomura 22
    1/8/2014 Reynolds 33
    1/8/2014 Maplebaum 23
    1/9/2014 Albertson 30
    1/9/2014 Nomura 23
    1/9/2014 Reynolds 27
    1/9/2014 Maplebaum 28
    1/10/2014 Albertson 28
    1/10/2014 Nomura 34
    1/10/2014 Reynolds 33
    1/10/2014 Maplebaum 25

    I plan to use this report to produce charts, and to use this data for other calculations (% change in class size, for instance). As a secondary consideration (and significantly farther down the list), I'd like this report (and whatever code -- VBA or other) to be fast/efficient because eventually this table will have tens of thousands of entries as I expand to additional years and schools.

    Thank you for your help on this.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you have a table in your database that contains all the classes (let's call the table tbl_ClassDirectory and the column containing the names ClassName), you can use something like:
    Code:
    SELECT TOP 1 *
    FROM ClassData
    WHERE Class IN (SELECT DISTINCT ClassName FROM tbl_ClassDirectory)
    ORDER BY Date DESC;
    Alternatively, you could probably use the same table for the subquery:
    Code:
    SELECT TOP 1 *
    FROM ClassData
    WHERE Class IN (SELECT DISTINCT Class FROM ClassData)
    ORDER BY Date DESC;
    Have a nice day!

  3. #3
    Join Date
    Jan 2014
    Posts
    20
    Thank you for your reply.

    I do have another table in the database (Classes) exactly as you describe (as tbl_ClassDirectory in your code) which lists each class as a primary key (Classes.ClassName), the teacher's name, room, school, etc. The Classes.ClassName is a one-to-many relationship with the ClassData.Class field in the table ClassData. The ClassData table holds the daily information on the classes.

    I tried both versions of the code you provided (the first version especially as it references the primary key which I thought/think was better form) but it only seems to catch the top record from the table (i.e just 1/10/2014 Maplebaum 25, but nothing from the rest of the classes on the same date). However, I am trying to get a report of ALL classes and their attendance on the most recent date available, as below:

    Date Class Attendance
    1/10/2014 Albertson 28
    1/10/2014 Nomura 34
    1/10/2014 Reynolds 33
    1/10/2014 Maplebaum 25


    The reason I moved from Excel (where I am fairly proficient) to Access (where I am clearly a wreck) is that not all classes will have the exact same schedule (not all classes will meet every day) and thus "the most recent day" may be different for each class (only occasionally). However, I still require the most recent day's info for each-and-every class.

    Is it possible the " Top 1 * " is catching only the top Class from the most recent Date, and not the top date for each-and-every Class? Perhaps a query which selects each class and then finds the top date, and then a report which pulls in all the classes so each top date for each class is found and listed? As I thought your code did this via (SELECT DISTINCT ClassName FROM tbl_ClassDirectory) , I am unsure how to proceed.

    Thanks.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry, I read your question a little too quickly. Try:
    Code:
    SELECT ClassData.Date, ClassData.Class, ClassData.Attendance
    FROM ClassData
    WHERE ClassData.Date = ( SELECT MAX(ClassData.Date) FROM ClassData )
    ORDER BY ClassData.Class;
    Have a nice day!

  5. #5
    Join Date
    Jan 2014
    Posts
    20
    Unfortunately this, too, only returns the top class record rather than the top date for each class.

    Should I be thinking of somehow stepping through all the ClassName options and then calling this query?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When I open the last query I posted, here's what it returns:
    Code:
    Date	        Class	        Attendance
    10/01/2014	Albertson	28
    10/01/2014	Maplebaum	25
    10/01/2014	Nomura	        34
    10/01/2014	Reynolds	33
    Isn't it what you wanted?
    Have a nice day!

  7. #7
    Join Date
    Jan 2014
    Posts
    20
    I'm sorry -- your code worked perfectly, and gave the result you displayed. It somehow didn't work on my original database so I simply started from scratch and recreated the database: it works perfectly for me now. Thank you for your help.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No need to be sorry, you're welcome!
    Have a nice day!

  9. #9
    Join Date
    Jan 2014
    Posts
    20
    Ok, now that I've played around a bit with the newly-created schooldata2 database (I must have inadvertently created something incorrectly when building the first one which caused the above code not to work correctly) I have an issue with the output above: it perfectly collects all the class data for the most recent date (1/10/2014 in the example listed above). But ONLY for the most recent date even if all the classes didn't meet that day: if one of the classes doesn't meet during that day, it skips it rather than supplying the class data for the class from the last day IT met.

    To illustrate this issue I added the following data to the ClassData table:

    Date Class Attendance
    1/11/2014 Albertson 27
    1/11/2014 Reynolds 31
    1/11/2014 Maplebaum 25
    (no information for Normura's class because it didn't meet that day)

    When I run the above SQL code I get the following output:

    Date Class Attendance
    1/11/2014 Albertson 27
    1/11/2014 Reynolds 31
    1/11/2014 Maplebaum 25

    It just slected the most recent date and then all the classes that met that day.

    Same output when I tried using Top 1 * (which I would prefer because it would allow me to select the nth-most-recent record, as worked out successfully in http://www.dbforums.com/microsoft-ac...nt-record.html), my next task.

    The output I would like to see is:

    Date Class Attendance
    1/11/2014 Albertson 27
    1/10/2014 Nomura 34
    1/11/2014 Maplebaum 25
    1/11/2014 Reynolds 31

    Please note that even though Nomura's class didn't meet, the most recent Nomura attendance is displayed as well.

    Any suggestions as to what simple code I can use to receive this output?

  10. #10
    Join Date
    Jan 2014
    Posts
    20
    And while we are on the subject, I have a question about using TOP for this query: if I use " TOP 1 * ", " TOP 2 * " or " TOP 3 * " I still get the same output as above (just the most recent day's classes). But if I use " TOP 4 * " I get the TWO most recent day's classes (7 records). Am I doing something wrong?

  11. #11
    Join Date
    Jan 2014
    Posts
    20
    I should have been more clear: with " TOP 2 * " I would have expected to see the following output:

    Date Class Attendance
    1/11/2014 Albertson 27
    1/11/2014 Reynolds 31
    1/11/2014 Maplebaum 25
    1/10/2014 Albertson 28
    1/10/2014 Nomura 34
    1/10/2014 Reynolds 33
    1/10/2014 Maplebaum 25
    1/9/2014 Nomura 23

    (i.e. the top two days of records for each class). Instead I saw this:

    Date Class Attendance
    1/11/2014 Albertson 27
    1/11/2014 Reynolds 31
    1/11/2014 Maplebaum 25

    Even with " TOP 4 * " I only saw the most recent two days of classes (and not pulling up the 2nd most recent day of Normura's class on 1/9/2014).

    Thanks for your help.

  12. #12
    Join Date
    Jan 2014
    Posts
    20
    Ok, so I finally wrote some code that pulls up the last 3 days of each class, regardless of when the dates are. It's below, entitled "Last 3 Days Attendance":

    SELECT cDate, Class, Attendance
    FROM ClassData
    WHERE cDate IN
    (
    SELECT TOP 3 Dupe.cDate
    FROM ClassData AS Dupe
    WHERE Dupe.Class = ClassData.Class
    ORDER BY Dupe.Class, cDate DESC
    )
    ORDER BY Class, cDate;


    This worked but one issue that came up is when I put in 50 classes and two years of attendance the resulting query was slow (3 minutes to show results). This seems slow to me. When I put real data into the database (10 years and 250 classes) this could be a real issue.

    Also, how do I select the 3rd day only (not the first and second days)? I tried nesting in another query which called the "Last 3 Days Attendance" query and took the top date record for each class -- this worked, but was very slow. And when I put in 50 classes and two years of attendance the resulting query was very, very, very slow (it's been 20 minutes so far and still not complete). I obviously can't use this in this form.

    Is there another way to do this which may be much faster to query?

    Thanks.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Is the table indexed and is there an index on [Class] and on [cDate ]? If there is only one value for the couple [Class]/[CDate] you should consider making this couple the primary key of the table.
    2. Create [Dupe] as a permanent query and use it in the main query. It will be compiled which will improve the performances.
    Have a nice day!

  14. #14
    Join Date
    Jan 2014
    Posts
    20
    Ok, I managed to figure out how to make the [Class]/[cDate] the primary key pair in the ClassData table. (And please note I changed the field name "Date" to "cDate" so I won't/shouldn't run into issues later.) I also indexed both Class and cDate.

    When I run the [Last 3 Days Attendance] query again with the "medium-sized data set" (~22k records = 53 classes X two years of daily attendance) it now runs in roughly 20 seconds. Still not blazing, but definitely better, thanks.

    I still have the ID field in ClassData which was the (autonumber) primary key before you enlightened me as to making the pair the primary key. I am afraid to delete this in case I am about to make a royal mistake. However, if I delete this field, shouldnít the query run faster?

    As ridiculous as it must sound, I have no idea how to split off Dupe as a separate query: doesn't Dupe call ClassData, so how can I peel this out? Can I assume, per this suggestion, that the query would run even faster with this change?

    And, to be clear, to get the attendance on the third day I run a query [3rd Day Attendance] below which should take the attendance on the furthest day from each of the classes. I tried using a MIN version of the code you had written above Ė i.e. WHERE ClassData.Date = ( SELECT MAX(ClassData.Date) FROM ClassData ). This worked quickly, but seems to only select the classes on the furthest date, which doesnít get data from all the classes, just the ones which have missed a class in these three days. (The others which didnít miss a class during these three days simply didnít show up.) So then I tried to run essentially [Last 3 Days Attendance] as a different query (named [3rd Day Attendance] ) using the same code (below) but this query has never finished running (I escape after about 15 minutes) and thus I assume it doesnít work.

    Query [3rd Day Attendance]
    ----------------------------------------
    SELECT cDate, Class, Attendance
    FROM [Last 3 Days Attendance]
    WHERE cDate IN
    (
    SELECT TOP 3 Dupe2.cDate
    FROM [Last 3 Days Attendance] AS Dupe2
    WHERE Dupe2.Class = [Last 3 Days Attendance].Class
    ORDER BY Dupe2.Class, cDate DESC
    )
    ORDER BY Class, cDate;

    This had worked perfectly to select the last 3 days of attendance for every class, so I thought this could do the same for the 3rd most recent day of attendance. Also, I used Dupe2 instead of Dupe again because I was afraid this might call Dupe twice in the same statement. Was this misguided?

    This brings up a another point: in starting to use Access, I was under the impression that Access was much faster in manipulating large datasets than Excel. But in working with this simple Access database I find that Access seems slower than Excel in making many of these calculations. I am definitely a novice in Access and am likely (definitely) making many stylistic mistakes, though I would have assumed the power/speed of Access would have negated most of my mistakes with regards to speed. Excel can easily handle 55 X 400 cells of data with many calculations, but I find my Access is choking on my requests; this isnít that large a dataset, nor a complicated query. Did I overinflate Access in my mind with regards to speed? Or is my code really that inept? And if it is really that inept, where am I going wrong?

    Thanks.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do not delete the Autonumber column. Just create a UNIQUE index on it. It could come in handy one day or another.

    There is no simple answer to your question about Access performances. It can vary immensely depending on how the project is built, how the tables are defined, which indexes exist, the kind of data manupulated, etc. It takes time to acquire an experience on how to buid a fast application.

    Out of the blue, I would be tempted to:
    1. Add a column [cYear] INT NOT NULL (indexed, duplicates OK) to the table.
    2. Build the query that causes problem based to another query that will select the rows for the current year (a subset of the table keeping only rows WHERE [cYear] = Year(Now)). and see if it runs faster.

    You can also force the Jet Engine (the data engine of Access) to yield a kind of trace file that you can open (it's a text file) and analyse. This can sometimes give you a clue about what happens and possibly ideas on how to improve the process.

    You set the Jet Engine in debug mode by adding a key to the registry:
    Code:
    Windows Registry Editor Version 5.00
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]
    "JETSHOWPLAN"="ON"
    The file produced is named showplan.out and is written in the default directory defined for Access databases. You can switch the debug mode off by removing the registry key or by using:
    Code:
    Windows Registry Editor Version 5.00
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]
    "JETSHOWPLAN"="OFF"
    Correction: The file showplan.out is located in the same folder as the database that generated it.
    Last edited by Sinndho; 03-06-14 at 04:01. Reason: Location of the file showplan.out
    Have a nice day!

Posting Permissions

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