Results 1 to 10 of 10

Queried My Brains Out and Still Am Not Getting It! Please help moi!

  1. #1
    SarahMascara is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    4

    Exclamation Queried My Brains Out and Still Am Not Getting It! Please help moi!

    Hi there! I hope that you guys can please help me...

    I have a raw data set that is provided daily and consists of 20,000+ records of loans. I need to use Access in order to determine the number of records that are considered “open inventory,” and I need to be able to look at the total count of open inventory over a time period (i.e. every day for the last two months). I’d like to run a query in order to be able to accomplish this. The challenge is that the data can have multiple records for each loan; I will call these “steps.”

    For example, let’s say that I have 3 records all for loan# ABC, consisting of step 1, step 2, and step 3. They each have due dates and completed dates. I need to be able to run a query that tells me that on 12/31/11 there was 0 open inventory, then 1/1, 1/2, 1/3, 1/4, 1/5, and 1/6 there was 1 open inventory, and finally on 1/7/12, there was 0 open inventory. It is important to note that the record wouldn’t be considered open unless the due date is equal to or before the date you are trying to account for, I will call this the “report date.” Also, within my actual data file, there are many records that have a completed date that is blank (null), and these would be considered open as long as the due date is before or equal to the report date.

    Loan Number Step Due Date Completed Date
    ABC Step 1 1/1/2012 1/5/2012
    ABC Step 2 1/3/2012 1/5/2012
    ABC Step 3 1/6/2012 1/6/2012


    Ideally, I’d like to be able to see the results in a chart format that shows the report dates across the top and then the employees that are assigned to each loan going down the side. The results would simply show the total open inventory count for each employee for each report date and not identify the specific loan numbers. This sounds easy enough when you see it for 1 loan, but when the data is 20,000+ records, it becomes slightly overwhelming. I need to be able to provide this report data on a daily basis when I receive the new raw data file.

    Your expertise and assistance is greatly appreciated. I have not been able to figure out how to do this, so I turn to you with hope and desperation.

    Thank you,
    S.

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,614
    This will be very difficult and doubt can be accomplished with queries alone. Will need VBA code and probably a temporary table to save the results into.

    Each step of each loan is considered an 'item' that must be counted? The criteria must take into consideration not only if the due date is <= report date but also if the item has a completed date <= report date? Each item will have one employee associated?

    Assuming code could be written to generate this count, there is the issue of 'transposing' the data to the structure you describe. That is essentially a denormalization and can be difficult on its own, especially if a conventional CROSSTAB will not accomplish. Review this http://www.accessforums.net/showthre...lias-Variables
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    SarahMascara is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    4
    Thank you June7! With respect to your questions, each step that is "open" as of a certain report date must be counted only once per loan number. Therefore, even if there are 3 open records for a particular loan, it would still just be counted as 1. The criteria that you listed for the due date is < = to the report date and completed date is < = to the report date is correct.

    Thank you for enlightening me that VBA code will be needed. While I have a decent background with Excel; my experience with Access is very limited. I started this new job, and they are expecting me to solve this. I basically have no results to show for my hours that I have put into this. Doh! I will start looking into the link that you provided.

    I sincerely appreciate your help!

    Best regards,
    Sarah

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,614
    Still confused about the steps. Do they have to be completed in sequence, i.e., step3 cannot be completed if step1 is not? If the 3 steps in your example all had no completion date, would that be a count of 3 open on 1/7?

    The link I provided will not offer guidance for the code to perform the calculations - it deals with transposing data. I do have ideas about the calculations and might have chance to come up with something this week.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    622
    Just to understand things better:


    Let us say you have data :


    Code:
    Loan    Step    DueDate    CompletedDate
    ABC    Step1    1/1/2012    1/5/2012
    ABC    Step2    1/3/2012    1/5/2012
    ABC    Step3    1/6/2012    1/6/2012
    DEF    Step1    1/2/2012    1/5/2012
    DEF    Step2    1/4/2012    1/5/2012
    DEF    Step3    1/5/2012
    Now you want the query result from 12/31/2011 to 1/10/2012 :
    Should it be something like below:

    Code:
    Report Date     Loan Inventory Count
    12/31/2011    0
    1/1/2012    1
    1/2/2012    2
    1/3/2012    3
    1/4/2012    4
    1/5/2012    5
    1/6/2012    2
    1/7/2012    1
    1/8/2012    1
    1/9/2012    1
    1/10/2012    1
    Thanks

  6. #6
    SarahMascara is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    4
    Quote Originally Posted by June7 View Post
    Still confused about the steps. Do they have to be completed in sequence, i.e., step3 cannot be completed if step1 is not? If the 3 steps in your example all had no completion date, would that be a count of 3 open on 1/7?

    The link I provided will not offer guidance for the code to perform the calculations - it deals with transposing data. I do have ideas about the calculations and might have chance to come up with something this week.
    The steps will be completed in order, but they can also be opened at the same time. If the 3 steps in my example had no completion date, they would count as simply 1 open inventory (loan ABC) and not 3. We are looking for distinct loan numbers.

    Thank you so much for your willing to take a look at this. I sincerely appreciate it.

  7. #7
    SarahMascara is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    4
    Hi Recyan:

    Imagine that your data had an additional column that included a person assigned to the particular task. The query result that I am looking for would then have the person assigned going down (where you have the dates) and the report dates going across the top. The count as of each report date would then be listed under each date, similar to how you have it but just multiple columns. I am trying to look at the open inventory over a period of time for each assigned person.

    12/31/2011 1/1/2012 1/2/2012 1/3/2012
    Amber 0
    Tina
    Matt

  8. #8
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    622
    Based on your feed back, if I have understood correctly, the Data table is something like :
    Code:
    EmployeeAssigned    Loan    Step    DueDate    CompletedDate
    A    ABC    Step1    1/1/2012    1/5/2012
    B    ABC    Step2    1/3/2012    1/5/2012
    A    ABC    Step3    1/4/2012    1/6/2012
    C    DEF    Step1    1/2/2012    1/5/2012
    B    DEF    Step2    1/4/2012    1/5/2012
    D    DEF    Step3    1/5/2012

    Just check out if below gives some guidelines :

    The Data table :
    tblLoans with Fields
    EmployeeAssigned
    Loan
    Step
    DueDate
    CompletedDate
    __________________________________________________ __
    Make a table manually containing the Number of Days needed for report (If you need 60, add numbers from 0 - 65)
    tblDaysForReport with Field
    Num - PK - Type Number (Contains numbers from 0 to 65)
    __________________________________________________ __
    Design a Make Table Query which generates the Report Dates

    This is the 1st Query that has to be run after making the above table
    qryGenerateReportDates
    Code:
    SELECT 
        tblDaysForReport.Num, 
        DateAdd("d",[Num],[Enter Start Date]) AS ReportDates 
        INTO 
        tblDates
    FROM 
        tblDaysForReport
    WHERE 
        (((tblDaysForReport.Num)<=[Enter Number of Days]))
    ORDER BY 
        tblDaysForReport.Num;
    __________________________________________________ __
    Design a Query to get the Max Report Date
    qryMaxReportDate
    Code:
    SELECT 
        Max(tblDates.ReportDates) AS MaxOfReportDates
    FROM 
        tblDates;
    __________________________________________________ __
    Design a Query which incorporates the Max Report Date. The purpose of this Query is to generate the Completed Date as Max Report Date for those Loans which do not have Completed Date.
    qryDueDatesCompletedDatesMaxOfReportDates
    Code:
    SELECT 
        tblLoans.EmployeeAssigned, 
        tblLoans.Loan, 
        tblLoans.Step, 
        tblLoans.DueDate, 
        tblLoans.CompletedDate, 
        qryMaxReportDate.MaxOfReportDates, 
        Nz([CompletedDate],[MaxOfReportDates]) AS NewCompletedDate
    FROM 
        tblLoans, qryMaxReportDate;
    __________________________________________________ __
    Design a Query to return the Employee Assigned, the Loan & the Step for each day from Start Date to Completed Date
    qryDueCompletedDates_1
    Code:
    SELECT 
        tblDates.ReportDates, 
        qryDueDatesCompletedDatesMaxOfReportDates.EmployeeAssigned, 
        qryDueDatesCompletedDatesMaxOfReportDates.Loan, 
        qryDueDatesCompletedDatesMaxOfReportDates.Step, 
        qryDueDatesCompletedDatesMaxOfReportDates.DueDate, 
        qryDueDatesCompletedDatesMaxOfReportDates.CompletedDate, 
        qryDueDatesCompletedDatesMaxOfReportDates.NewCompletedDate
    FROM 
        tblDates 
        LEFT JOIN 
        qryDueDatesCompletedDatesMaxOfReportDates 
        ON 
        (tblDates.ReportDates>=qryDueDatesCompletedDatesMaxOfReportDates.DueDate) 
        AND 
        (tblDates.ReportDates<=qryDueDatesCompletedDatesMaxOfReportDates.NewCompletedDate);
    __________________________________________________ __
    Design a Query to Get the Count of Loans for each Employee
    qryDueCompletedDatesFinal_1
    Code:
    SELECT 
        qryDueCompletedDates_1.ReportDates, 
        qryDueCompletedDates_1.EmployeeAssigned, 
        Count(qryDueCompletedDates_1.Loan) AS CountOfLoan
    FROM 
        qryDueCompletedDates_1
    GROUP BY 
        qryDueCompletedDates_1.ReportDates, 
        qryDueCompletedDates_1.EmployeeAssigned
    ORDER BY 
        qryDueCompletedDates_1.ReportDates, 
        qryDueCompletedDates_1.EmployeeAssigned;
    __________________________________________________ __
    Design a Query which gives you the results of the above query the way you want it (Cross Tab)

    This is the 2nd Query that is to be run finally.

    qryDueCompletedDatesFinal_1_Crosstab_1
    Code:
    TRANSFORM Sum(qryDueCompletedDatesFinal_1.CountOfLoan) AS SumOfCountOfLoan
    SELECT 
        qryDueCompletedDatesFinal_1.EmployeeAssigned
    FROM 
        qryDueCompletedDatesFinal_1
    GROUP BY 
        qryDueCompletedDatesFinal_1.EmployeeAssigned
    ORDER BY 
        qryDueCompletedDatesFinal_1.EmployeeAssigned, qryDueCompletedDatesFinal_1.ReportDates
    PIVOT qryDueCompletedDatesFinal_1.ReportDates;
    __________________________________________________ __

    Note : Only these 2 Queries have to be run. The rest are sub-queries & should only be designed & ready in your db.

    qryGenerateReportDates
    qryDueCompletedDatesFinal_1_Crosstab_1
    __________________________________________________ ___
    Have deliberately kept the process longish, so that you can pinpoint any flaw in the logic & correct it.
    If you find it helpful, I am sure with a bit of effort, you should be able to trim it to at least half the size or find an alternative shorter method.

    Thanks

  9. #9
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,614
    Terrific work recyan. Eliminates most code. I was working on procedure that would output data in the form of qryDueCompletedDatesFinal_1. Now VBA procedure to produce output could include execution of the two queries.

    Sarah, now you can work with the conventional crosstab that recyan shows but the issue with crosstabs is that they are dynamic in that field names will change so using them as record source for a report is cumbersome - have to continually edit the report to bind textboxes to the new fields. Writing VBA code to do the report mods would be another challenge. Creating a stable crosstab is not easy, especially when pivoting on date values. That's where my example code for 'transposing' data can benefit.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  10. #10
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    622
    Thanks a lot June. recyan is new learner reborn & keeping in touch with access & you guys / gals.
    I just can't think VBA, as I am totally out of touch with it.

    As a simple thought,

    A Form
    that supplies the End Date ( I am reversing the logic I have given earlier) & No off Days needed for the report & a submit button.
    The End Date can be used to generate the Report Dates Table with a little bit of twisting in the reverse direction of the Make Report Dates table query.
    Also This End Date can perhaps be used as the value in the other Query qryDueCompletedDates_1 & perhaps we can eliminate qryDueDatesCompletedDatesMaxOfReportDates.

    Instead of the End Date, we can continue with the Start Date which is being currently used.

    The submit button of the Form can execute a Macro (I know we should avoid it, Remember that lesson from you, we can later convert that macro in to VBA),
    which in turn executes the Make Report Dates Table Query first & then the final Cross Tab Query or the Report which in turn could be based on the Cross tab Query.

    Edit : I don't know whether you are facing this issue, sometimes, when I try to paste something, it pastes at the start of the reply.

    Thanks

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

Similar Threads

  1. Need a function to change queried text
    By rjohnson in forum Queries
    Replies: 2
    Last Post: 02-20-2012, 08:28 AM
  2. Add selection to queried combo box
    By davefrag in forum Forms
    Replies: 1
    Last Post: 01-18-2012, 02:26 PM
  3. Replies: 1
    Last Post: 07-15-2011, 10:00 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
  •  
Tech Forums: Microsoft Office Forums