This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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"));

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].[

"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"))

DATEADD(d,7-DATEPART(w,BDA

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.

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.

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;

your code example throws many errors I assume with the CASE or CASE WHEN part.

Ican't run a query on it.

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

SELECT Payments.ACCO,

SUM(IIF(((Payments.BSTA)="

SUM(IIF(((Payments.BSTA)="

FROM Payments

GROUP BY Payments.ACCO;

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

SELECT Payments.ACCO, Sum(IIf(((Payments.BSTA)="

FROM Payments

GROUP BY Payments.ACCO, Payments.BDATE

HAVING (((Payments.BDATE)="10/03/

Thank you so much!

Can you also tell me what this means :

"Sum(IIf(((Payments.BSTA)=

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

iif ( condition, value_if_true, value_if_false )

The first part : Sum(IIf(((Payments.BSTA)="

The other sum : Sum(IIf(((Payments.BSTA)="

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Here is the new select statement

SELECT Payments.ACCO,

SUM(IIF(((Payments.BSTA)="

SUM(IIF(((Payments.BSTA)="

FROM Payments

GROUP BY Payments.ACCO;