Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
grrr223
Starting Member
30 Posts |
 Posted - 2004-02-22 : 02:56:50
|
I am creating customer statements in an ADP using Access 2003 and SQL Server 2000. The line items on the monthly statements come from a table listing all accounts receivable activity. There is a line for every invoice, check, credit, etc. The Doc No is the invoice number for invoices (Doc Type = I) and the check number for payments (Doc Type = P). The Apply-to-no is the Doc No of the invoice that a payment gets applied to for payments, or just the Doc No for invoices. They appear here in Doc Date order. Doc No Doc Type Apply-To-No Amount Doc Date1 I 1 $1,000 1/20/042 I 2 $500 2/1/04101 P 1 $1,000 2/5/04102 P 2 $500 2/9/04 3 I 3 $750 2/10/04 On the statements, we like to show the payments against last month's balance, and then the current month's activity. So I want to put them in order by the date of the Apply-To which I have added below as the Apply-To-Date. The February statement would have the below data displayed on it. Doc No Doc Type Amount Doc Date Apply-to-DatePREVIOUS BALANCE $1,000101 P $1,000 2/5/04 1/20/04 OPENING BALANCE $02 I $500 2/1/04 2/1/04102 P $500 2/9/04 2/1/043 I $750 2/10/04 2/10/04 AMOUNT DUE $750 I've actually figured out how to do most of the above, this is like the 3rd time I've completely edited this post :). However, I still have a few questions:1.How can I group the records so that I have two groups, records with the Apply-To-Date before @StartDate (2/1/04 in this case) and those after @StartDate? (@StartDate is a parameter that I pass to the recordsource)2. How can I cange the text in the group footer so that it says "OPENING BALANCE" at the bottom of the previous activity group and "AMOUNT DUE" at the bottom of the current month activity group. Is there an easy way to do this in Access, or could you please provide some guidance on how to do it with VB? (i.e. Crystal Reports lets you create multiple versions of each section (header, footer, detail, etc.) and turn them on and off using conditional formulas.)3. Similar to the previous question, what is the easiest way to get a field in the group header to appear only on the first page of a group so that it doesn't appear if there is more than one page. I hope this all makes sense. Thank you. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-23 : 05:40:26
|
| Lateral approach here....is there any benefit to you in establishing a 'month-end' process which calculates a month-end total which can be used for the next months "opening-balance".This might help your performance issues, highlighted elsewhere.After all, what's going to happen in December...are you going to re-calculate jan, feb,mar,apr, etc just to get the opening position for December? |
 |
|
|
grrr223
Starting Member
30 Posts |
Posted - 2004-02-23 : 11:16:07
|
My questions are actually more about the grouping and conditional formatting now as I have figured out a fast and efficient way of calculating the opening balance for the month.I created a view that simply calculates the open balance at a given date for all customers. I then do a join on this table in my record source for my statements. Since it only has to do the calculation once per customer, it is very fast.SELECT TOP 100 PERCENT Customer_Number, SUM(Amount) AS BalanceFROM AROPNFIL_SQLWHERE (Doc_Date < @BalanceDate)GROUP BY Customer_NumberORDER BY Customer_Number Question: Is there any positive or negative effect of having the TOP 100 PERCENT and ORDER BY clauses in this query? Personally I don't care how they're ordered in this opening balance query because the record set that it is joined to is already ordered by customer number. I wasn't sure if having them ordered speeded up the join or something like that. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-23 : 12:19:35
|
| However the results of your view (valuable business information) is discarded straight after the view is used.....Say you have 10 records per month per customer....In Jan...this means you sum 10 records, in Feb 20, in Mar 30, in Apr 40.....etc....per customeryet....a known result at the end of jan is being discarded....only to be recalculated for each of the succeeding months.A non-problem in Jan....may be a very serious problem come July....especially if you have any serious amount of customers.... |
|
|
|
grrr223
Starting Member
30 Posts |
Posted - 2004-02-23 : 13:05:47
|
Yes, you are correct, but there is really no reason to save that information.It is trivial to run that view on even the 100,000 records in our largest company. It actually works out quite nicely. I even added a closing balance to my record set for the statements so that I have a number to check the totals I'm calculating on the report against to make sure I'm not missing or double counting anything. Right now my recordset for the report looks something like this:SELECT AROPNFIL.cus_no, AROPNFIL.Doc_Date, Doc_no., apply_to_no, AROPNFIL_SQL_1.doc_dt AS apply_to_dt, doc_type, Amount, StartBalance.Balance AS StartBalance FROM DATA_09.dbo.AROPNFIL_SQL AROPNFIL_SQL LEFT OUTER JOIN MJC.fnBalance_Data_09(@StartDate) StartBalance ON AROPNFIL_SQL.cus_no = StartBalance.cus_no LEFT OUTER JOIN DATA_09.dbo.AROPNFIL_SQL AROPNFIL_SQL_1 ON AROPNFIL_SQL.apply_to_no = AROPNFIL_SQL_1.doc_noGROUP BY AROPNFIL_SQL.cus_noWHERE AROPNFIL_SQL.doc_dt BETWEEN @StartDate AND @EndDateORDER BY AROPNFIL_SQL.cus_no, AROPNFIL_SQL_1.doc_dt, AROPNFIL_SQL.doc_no The only real question here is that right now I am adding the customer address and this balance information to each record in the view and then grouping by customer number. Would there be any advantage to using a list of customers as my record source and having the line items be a subreport? |
|
|
|
|
|
|
|
|
Grouping&Conditional Formatting in Access Reports