Solved

SQL Statement Join and Where issue

Posted on 2008-10-15
11
298 Views
Last Modified: 2012-05-05
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:
Table1:  Payments
Columns: ACCO, INVOICE, DISC, DISCOUNT, Gross, Net, PaymentNet, Payment, BDATE, BSTA

Table: Roster
ID, Company

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:

"PaymentTotal"
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]
FROM Payments
GROUP BY Payments.ACCO;

"PaymentUsed"
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
FROM Payments
GROUP BY Payments.ACCO, Payments.BSTA
HAVING (((Payments.BSTA)="PAID"));
0
Comment
Question by:EGormly
  • 6
  • 5
11 Comments
 
LVL 5

Expert Comment

by:jfmador
ID: 22721385
You can use datepart function and get the last day of the week and then group on this field

DATEADD(d,7-DATEPART(w,BDATE),BDATE)

for example 2008-10-13, 2008-10-14, 2008-10-15  will become 2008-10-18

DATEPART(w,BDATE) will return you the week day as 1 for sunday, 2 for monday, etc
if you add (7 minus the datepart) it will give you the saturday of the week for each date then you can group on this.
0
 

Author Comment

by:EGormly
ID: 22721437
jfmador:
although I really appreciate the effort, that in not the issue.
The BDATE is already in the table, the variable will be supplied to match, so there is no need to  "get" the last day of the week.

Every BDATE is a "last day of week"

I am asking HOW to group on it using the two queries and return results for s supplied BATE variable example: BDATE supplied is 10/10/2008.  There are thousands of invoices that have the BDATE of 10/10/2008 and some are paid, I need to know what perentage are paid to each account on that BDATE.

0
 
LVL 5

Expert Comment

by:jfmador
ID: 22721594
Sorry for the misunderstood,

You can use only one query using CASE expression, it act like an if and should be supported in Access, I gave you an exemple for net and number of invoice paid. you can add the other field if you want. The sum using the case will give you only amount for paid invoice and 0 for unpaid one.

SELECT DISTINCTROW Payments.ACCO,
SUM(CASE WHEN ((Payments.BSTA)="PAID") THEN Payments.Net ELSE 0 END) / SUM(Payments.Net) as PourcentageNet,
SUM(CASE WHEN ((Payments.BSTA)="PAID") THEN 1 ELSE 0 END) / COUNT(*) as NumberPaid
FROM Payments
GROUP BY Payments.ACCO;
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:EGormly
ID: 22722019
I know what you doing there, and it looks very promising, but I just can't translate it to MS Access..
your code example throws many errors I assume with the CASE or CASE WHEN part.

Ican't run a query on it.
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22722197
Sorry I tried the CASE statement in ACCESS 2007 and it don't seem to work,

Then I wrote the same statement using IIF instead of CASE and it worked,

SELECT Payments.ACCO,
SUM(IIF(((Payments.BSTA)="PAID"), Payments.Net,0)) / SUM(Payments.Net) as PourcentageNet,
SUM(IIF(((Payments.BSTA)="PAID"), 1,0)) / count(*) as PourcentagePaid,
FROM Payments
GROUP BY Payments.ACCO;
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22722225
Sorry I missed the BDATE you can add it as a group or a where statement as you want

The way I wrote you will have only one query instead of two and trying to compute it on programming side
0
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22722266
Sorry I made a mistake in the code that i posted, there is no coma after PourcentagePaid

Here is the new select statement

SELECT Payments.ACCO,
SUM(IIF(((Payments.BSTA)="PAID"), Payments.Net,0)) / SUM(Payments.Net) as PourcentageNet,
SUM(IIF(((Payments.BSTA)="PAID"), 1,0)) / count(*) as PourcentagePaid
FROM Payments
GROUP BY Payments.ACCO;
0
 

Author Comment

by:EGormly
ID: 22722450
This worked:

SELECT Payments.ACCO, Sum(IIf(((Payments.BSTA)="PAID"),Payments.Net,0))/Sum(Payments.Net) AS PourcentageNet, Sum(IIf(((Payments.BSTA)="PAID"),1,0))/Count(*) AS PourcentagePaid, Payments.BDATE
FROM Payments
GROUP BY Payments.ACCO, Payments.BDATE
HAVING (((Payments.BDATE)="10/03/08"));

Thank you so much!

Can you also tell me what this means :
"Sum(IIf(((Payments.BSTA)="PAID"),"

(specifically the ",1,0)" part?
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22722533
The IIF part is a condition evaluation

iif ( condition, value_if_true, value_if_false )

The first part : Sum(IIf(((Payments.BSTA)="PAID"),Payments.Net,0)) will give you the sum of the Payments.Net where BSTA is PAID, then when you divide it by SUM(Payments.Net) you'll get a pourcentage
The other sum : Sum(IIf(((Payments.BSTA)="PAID"),1,0))  will act like a count becase it will give 1 when BSTA is PAID and 0 if not, then the sum of 1 or 0 will give you the number of invoice paid
0
 

Author Comment

by:EGormly
ID: 22722789
Thanks for the explanation

I have it working now.
0
 

Author Closing Comment

by:EGormly
ID: 31506304
perfect answer!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now