SQL Statement Join and Where issue
Posted on 2008-10-15
I need to pull a weekly percentage data set from a MS Access database.
Currently I can easily pull the cumulative total, but I need to break it down by week and I cannot seem to do it.
I am currently using two tables (Payments and Roster) and two queries from the (Payments) table and one from (Roster).
The structure is:
Columns: ACCO, INVOICE, DISC, DISCOUNT, Gross, Net, PaymentNet, Payment, BDATE, BSTA
There are two different queries I use to select INVOICES from the Payments table and they are based on the BSTA (status code)
The query "PaymentTotal" selects all records and the query "PaymentUsed" selects all records that have the status code "PAID"
I used both queries so I can get the percentage of PAID invoices to the overall total for each Company.
"PaymentTotal" gives me a return of ALL invoice Gross, Net, PaymentNet, Payment.
"PaymentUsed" gives me a return of only PAID invoice Gross, Net, PaymentNet, Payment.
I use both queries to calculate the percentage of paid to overall.
I can easily get the cumulative results from the SQL below..
what I need is a breakdown by specific week ending date (BDATE)
The specific BDATE I will need will be supplied as a variable to the SQL stament
Here is the current working cumulative SQL Statement:
Select PaymentTotal.ACCO as ACCT, roster.COMPANY as Company, [PaymentTotal].[Sum Of GROSS] as [Payments Total], [PaymentTotal].[Sum Of Payments] as [Eligible Savings], [PaymentUsed].[Sum Of Payments] as [Actual Saved], [PaymentUsed].[Sum Of Payments]/[PaymentTotal].[Sum Of Payments] as [Percent], [PaymentTotal.Sum Of Payments]-[PaymentUsed.Sum Of Payments] as [Unused $$]" &_
"FROM roster INNER JOIN ([PaymentTotal] INNER JOIN [PaymentUsed] ON [PaymentTotal].ACCO = [PaymentUsed].ACCO) ON roster.ID = [PaymentTotal].ACCO
what can I do to filter out only a specific BDATE? Example if I were to give it a BDATE of '10/10/2008'
It is a WHERE clause, but I do not know what to do.. does it need to be in a new query or can I do something here?
My Current Queries:
SELECT DISTINCTROW Payments.ACCO, Sum(Payments.GROSS) AS [Sum Of GROSS], Sum(Payments.NET) AS [Sum Of NET], Sum(Payments.Payments) AS [Sum Of Payments], Sum(Payments.PaymentsNET) AS [Sum Of PaymentsNET]
GROUP BY Payments.ACCO;
SELECT DISTINCTROW Payments.ACCO, Sum(Payments.GROSS) AS [Sum Of GROSS], Sum(Payments.NET) AS [Sum Of NET], Sum(Payments.Payments) AS [Sum Of Payments], Sum(Payments.PaymentsNET) AS [Sum Of PaymentsNET], Count(*) AS [Count Of Payments], Payments.BSTA
GROUP BY Payments.ACCO, Payments.BSTA