Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Access (https://www.askmehelpdesk.com/forumdisplay.php?f=441)
-   -   Date input parameters on a report (https://www.askmehelpdesk.com/showthread.php?t=157240)

  • Nov 29, 2007, 07:28 AM
    IowaGuy
    Date input parameters on a report
    I have used Crystal Reports 8.5 in the past. One of features I liked is when a report was ran, it prompted the used to specify a date range.
    Can Access 2003 do that? If so, can you point me into the right direction on how to make it happen.
  • Nov 29, 2007, 07:38 AM
    ScottGem
    Yes Access can easily do that. There are different ways to do it, but the method I use is to create a form to kick off the report. On the (unbound) form, I place two text controls named txtStart and txtEnd. Also on the form is a button to run the report. The report itself is bound to a query. In the query the criteria for the date column is set to:

    BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd

    (use the correct name of the form). When you press the button to run the report, the query will be filtered by the range of dates.
  • Nov 29, 2007, 08:02 AM
    IowaGuy
    Scott, you are everywhere! Mercy.

    Can you provide a link for this?
    If I am unable to do it from the link, I see that you take calls. I'd be willing to call.
  • Nov 29, 2007, 08:21 AM
    ScottGem
    1 Attachment(s)
    I'm not sure what you need a link for. I think the instructions I gave were pretty clear. I happened to have a sample of this so I'm attaching it. If you still need more help, let me know.
  • Nov 29, 2007, 01:55 PM
    IowaGuy
    Wow Scott.
    It totally clicked. I got several reports functional with this method. Thanks!

    Another question. Now, I am more of a graphics guy than a programmer. I took the basics in college, but nothing advanced. I have red some SQL books and I feel I understand the concepts until the advanced stuff. Ok..

    In my report, I have a sum of 3 columns grouped by date. This is what it shows.
    The total for each date. Perfect so far.

    PrintDate Apple EverythingElse Total
    ---------- ------ ----------------- ------
    11/4/2007... 100
    11/5/2007... 552

    I want it to do more. I want to know how many of the total were for Apple and how many were EverythingElse.

    Apple has a CustomerID of 1
    Grapes = 2
    and so on.

    In the report, I assume it's the report, maybe it's the VB code view.
    I created a text box for Apple (txt_Apple)and EverythingElse (txt_EverythingElse) and Total (txt_Total)

    I am trying to write something that stuffs the sum of txt_Total if customerid = 1
    for txt_Apple.
    Also to stuff the sum of txt_Total if customerid <>1 for txtEverythingelse.

    Am I going in the right direction and if so, what would be the correct syntax?
  • Nov 29, 2007, 04:11 PM
    ScottGem
    I'm not quite clear on your table structure here. Does it look like this:

    PrintDate
    CustomerID
    Amount

    If so, there are a few ways to do this. Probably a Union query would be easiest. First you create two group by queries. Both Grouping by Date and summing the amount. In one query you add a WHERE column to filter for CustomerDI =1 In the other CustomerID <> 1. Then Union those tow like this:

    SELECT PrintDate, SumOfAmount AS AppleTotal, 0 AS NonAppleTotal
    FROM qryApples

    UNION SELECT PrintDate, o AS AppleTotal, SumOfAmount AS NonAppleTotal
    FROM qryOthers;

    Finally, use the Union quey as the source of another query where you add the two total columns to get the overall total.
  • Nov 30, 2007, 07:40 AM
    IowaGuy
    Hi Scott,

    My job table looks like this:

    JobID (pk)
    CustomerID
    PrintDate
    Documents
    Addl_Pages
    Wasted_Paper

    There are more columns, but I didn't want make this more confusing.

    In my report. I want the sum of (Documents,Addl_Pages,Wasted_Paper)
    This will show the total amount of paper was used to print.

    I also want to show in the report how much of the paper was used for Apex(Apple) and how much was for everyone else.

    I wrote a SQL statement, in what I tried to base on your outline.

    SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Apex
    FROM Job
    WHERE CustomerID="1"
    GROUP BY Job.PrintDate, documents+addl_pages+wasted_paper
    ORDER BY job.printdate

    UNION

    SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Other
    FROM Job
    WHERE CustomerID <>"1"
    GROUP BY Job.PrintDate, documents+addl_pages+wasted_paper
    ORDER BY job.printdate


    It pulls back data, but I think there needs to be a way to differentiate between the 3 results I need.
  • Nov 30, 2007, 07:42 AM
    IowaGuy
    Another thought, and I am not sure if its possible or not, but in the report properties, where the Record Source is at, is it possible to look pull more than 1 query?
  • Nov 30, 2007, 08:31 AM
    ScottGem
    Ok, this is why table structure was a key. You really have two thing going on here and you are trying to do the two things at the same time which isn't going to work well. My question to you is do you have show the breakdown on a daily basis. If so, I think there is a different way to go.

    qryApex
    SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Apex
    FROM Job
    WHERE CustomerID="1"
    GROUP BY Job.PrintDate
    ORDER BY job.printdate

    qryOthers
    SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Other
    FROM Job
    WHERE CustomerID <>"1"
    GROUP BY Job.PrintDate
    ORDER BY job.printdate

    qryDailySummary
    SELECT qryApex.PrintDate, Total_Apex, Total_Others, Total_Apex+Total_others As Daily_Total
    INNER JOIN ON qryApex.PrintDate = qryOthers.PrintDate
    FROM qryApex, qryOthers
    ORDER BY PrintDate

    Then use qryDailySummary as the Recordsource of your report.

    Disclaimer: The SQL is off the top of my head and untested, but it should give you an idea, I suggest using the query builder to build each query.

    And no, you can't have more than one object as a Recordsource.
  • Nov 30, 2007, 09:55 AM
    IowaGuy
    Nice!

    Thanks!

  • All times are GMT -7. The time now is 10:47 AM.