Quarterly Amounts

[Brian62]

I am trying to create quarterly sums for my 1st Qtr. I want all the sums from "amt paid" to pull from the column "Month" for October, November and December to give me the total for each company.

Here is what I have so far for the third column.
1st Qtr: Sum(CCur([amt paid]))



I tried this but it didn't work and other variations. I also tried where statements but failed as well.

1st Qtr: Sum(CCur([amt paid] and [Month.October])))

Here are my table columns including:

Company Month amt paid 1st Qtr
ABC October $1200.00
plumbing November $1500.00
Plumbing December $1600.00

I want my query to pull to look like this:

Company 1st Qtr
ABC $1200.00
Plumbing $3100.00


Any ideas?

[Brian62]

I can get the first quarter by doing this:

SELECT [Billing Input].Company, Sum(CCur([amt paid])) AS [1st Qtr (Oct-Dec)]
FROM [Billing Input]
WHERE ((([Billing Input].Month)="October" Or ([Billing Input].Month)="November" Or ([Billing Input].Month)="December"))
GROUP BY [Billing Input].Company;

I want to be able to create all 4 qtrs. That means I have to find a way to combind each Qtr and months in the same statement.

No idea how to get this work in one query with one table set.

[Brian62]

Here is a copy of the DB so you have an idea what am I am trying to do.

[dcrake]

In your query is you bring down the relevant date and display it as the quarter

Qtr:Format([<YourDate>>],"q")

Then group by company by quarter and sum the amount.

David

[Brian62]

I'm not sure how to do that. The DB is attached. If you can look at it and show me how to write the script I wouold appreciate it. Thx

[dcrake]

No db attached? also can you post it pre 2007 to widen the audience of people who can view it please.

David

[Brian62]

I am attaching an earlier version (2003) as requested. Thx

[dcrake]

You seem to have some serious issues with your mdb.

Firstly don't use names like Month, Year as field names. These are Access reserved words and can be interpreted incorrectly.

Secondly you cannot expect to group on a quarter if you do not have an actual date to base your calculation on.

Storing month and years in different fields is a total waste of space as this can be gleened from a date, such as Month([AnyDate]) or Year([AnyDate])

Also having diffeent queries for different periods is only going to clog up your system in time to come.

Do some research on the following

Normalisation
Naming Conventions
Reserved words

David

[Brian62]

I got it to show by qtr but I need to show it broken down by quarter and year so I can create reports based on quarters by fiscal year. Here is what I have so far.

SELECT [Billing Input].Company, "Qtr " & DatePart("q",[Date]) AS Qtr, Sum(CCur([amt paid])) AS Total
FROM [Billing Input]
GROUP BY [Billing Input].Company, "Qtr " & DatePart("q",[Date]);

Attached is the updated DB. Thx!

[Brian62]

I figured it by using crosstabs but went with months instead. Here is the script.

TRANSFORM Sum(CCur([amt paid])) AS Total
SELECT [Billing Input].Company, Year([Date]) AS [Year]
FROM [Billing Input]
GROUP BY [Billing Input].Company, Year([Date])
ORDER BY Year([Date]), Format([Date],"mmmm")
PIVOT Format([Date],"mmmm");