G
grrr223
Note: I'm using Access 2003 to create an Access Data Project using SQ
Server 2000 as the back end.
I am working on the record source for a report to produce monthl
customer statements. Thanks to everyone here, I have been able t
overcome many, many hurdles I have encountered. I just have one las
issue I need to get resolved (famous last words, I know).
At the heart of my record source are two entities:
1. *AROPNFIL* - A table of all Accounts Receivable items (invoices
payments, credits, etc.)
2. *fnBalance(@StartDate)* - A table valued function that gives me th
starting balance for a customer on a particular date by summing the ne
Amounts of all items in the AROPNFIL that occured before th
@startdate. (i.e. Invoices have a positive amount, payments have
negative amount, so they net out so that only unpaid invoices remain.)
These are joined on customer_number. The query also selects only th
rows in the AROPNFIL table that are between @StartDate and @EndDate fo
the month.
Everything was going great until...I realized that no statement wa
being created for a customer if they didn't have any activity in th
current month, even though they had a starting balance.
So I tried an outer join, telling the query to select all rows from th
fnBalance table. But that still wasn't returning the rows I wanted
After several hours of cursing and feeling the need for a drink,
realized why that wasn't working. Because the customer in question
let's call it "Coast01" had rows in the AROPNFIL table before th
@StartDate, it was seeing that as having completed the join and henc
no reason to return a row for that starting balance.
So, this is what I need, a way to write what I am going to try t
interpret as the following.
Code
-------------------
Select *
From (AROPNFIL WHERE Doc_date BETWEEN @StartDate AND @EndDate) RIGHT OUTER JOIN fnBalance(@StartDate)
-------------------
Does that make sense? I need the join with fnBalance to take plac
after the query has selected only the rows between @startdate an
@enddate so that the row for the balance for "Coast01" will appear eve
though there is no activity in the current month.
But I am at a loss for how to write that in the FROM and WHERE clauses
I guess one way would be to create a query selecting rows from AROPNFI
WHERE doc_date BETWEEN @StartDate AND @EndDate and then create anothe
query performing the outer join between that query and fnBalance, but
don't really want to do that because the rest of my record source quer
is actually a lot more complicated than what I've explained here, an
I'd rather not have to create additional queries if I don't have to
But if you guys tell me there is no other way, I'll believe you.
Thank you all
Server 2000 as the back end.
I am working on the record source for a report to produce monthl
customer statements. Thanks to everyone here, I have been able t
overcome many, many hurdles I have encountered. I just have one las
issue I need to get resolved (famous last words, I know).
At the heart of my record source are two entities:
1. *AROPNFIL* - A table of all Accounts Receivable items (invoices
payments, credits, etc.)
2. *fnBalance(@StartDate)* - A table valued function that gives me th
starting balance for a customer on a particular date by summing the ne
Amounts of all items in the AROPNFIL that occured before th
@startdate. (i.e. Invoices have a positive amount, payments have
negative amount, so they net out so that only unpaid invoices remain.)
These are joined on customer_number. The query also selects only th
rows in the AROPNFIL table that are between @StartDate and @EndDate fo
the month.
Everything was going great until...I realized that no statement wa
being created for a customer if they didn't have any activity in th
current month, even though they had a starting balance.
So I tried an outer join, telling the query to select all rows from th
fnBalance table. But that still wasn't returning the rows I wanted
After several hours of cursing and feeling the need for a drink,
realized why that wasn't working. Because the customer in question
let's call it "Coast01" had rows in the AROPNFIL table before th
@StartDate, it was seeing that as having completed the join and henc
no reason to return a row for that starting balance.
So, this is what I need, a way to write what I am going to try t
interpret as the following.
Code
-------------------
Select *
From (AROPNFIL WHERE Doc_date BETWEEN @StartDate AND @EndDate) RIGHT OUTER JOIN fnBalance(@StartDate)
-------------------
Does that make sense? I need the join with fnBalance to take plac
after the query has selected only the rows between @startdate an
@enddate so that the row for the balance for "Coast01" will appear eve
though there is no activity in the current month.
But I am at a loss for how to write that in the FROM and WHERE clauses
I guess one way would be to create a query selecting rows from AROPNFI
WHERE doc_date BETWEEN @StartDate AND @EndDate and then create anothe
query performing the outer join between that query and fnBalance, but
don't really want to do that because the rest of my record source quer
is actually a lot more complicated than what I've explained here, an
I'd rather not have to create additional queries if I don't have to
But if you guys tell me there is no other way, I'll believe you.
Thank you all