 |
|
|
 |
New Member
|
|
Nov 29, 2007, 07:28 AM
|
|
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.
|
|
 |
Computer Expert and Renaissance Man
|
|
Nov 29, 2007, 07:38 AM
|
|
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.
|
|
 |
New Member
|
|
Nov 29, 2007, 08:02 AM
|
|
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.
|
|
 |
Computer Expert and Renaissance Man
|
|
Nov 29, 2007, 08:21 AM
|
|
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.
|
|
 |
New Member
|
|
Nov 29, 2007, 01:55 PM
|
|
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?
|
|
 |
Computer Expert and Renaissance Man
|
|
Nov 29, 2007, 04:11 PM
|
|
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.
|
|
 |
New Member
|
|
Nov 30, 2007, 07:40 AM
|
|
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.
|
|
 |
New Member
|
|
Nov 30, 2007, 07:42 AM
|
|
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?
|
|
 |
Computer Expert and Renaissance Man
|
|
Nov 30, 2007, 08:31 AM
|
|
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.
|
|
 |
New Member
|
|
Nov 30, 2007, 09:55 AM
|
|
Nice!
Thanks!
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Where's the Date of last activity on my Equifax report?
[ 1 Answers ]
Just got my Equifax report & It's broken up into a bunch of sections.
The Credit Information section contains a date of last activity field, along with the following explanation in the header:
The Collection Accounts section does not have a date of last activity field, but has the...
To-date total based on current date
[ 1 Answers ]
:confused: I am trying to create a forumula that will give a cumulative or
"to-date" total that will exclude future months from the total.
i.e. ithe point in time is August and I need to show a to-date number
Through August only even though September and October are included in
The...
View more questions
Search
|