Quarters by Non Calender Year 1

[GSINC]

I am not a VB Coder but am rather familar with the query abilities of Access built in Query Designer.

In my contacts table I have how much each company has spent per month. Each of these companies have renewal dates throughout the year.

I am tring to determine how much each of the companies have spent in a quarter and populate these fields:
Quarter1 - Quarter2 - Quarter3 - Quarter4

This is not a a standard January though March quarter (or Calander Quarters)rather the quarter depends on when the renewal date is for that company.

So company ABC has a renewal date of July 1 2004
FIRST QUARTER July 1 2003 to October 1 2003
SECOND QUARTER October 1 2003 to December 31 2003
THIRD QUARTER January 1 2004 to March 31 2004
FOURTH QUARTER April 1 2004 to June 31 2004


Any Thoughts or suggestions? What would the query or queries look like to get the total spent per month into the the proper quarters.

Thanks for you considerations.
Greg

 

[r937]

you have two tables, right? one where there's one row per company, including the renewal date, and another where there's multiple rows per company, one per month, right?

can you give the layouts, i.e. relevant columns in each table that apply to this question, including join columns

and please confirm the renewal date is a date/time datatype

rudy

http://r937.com/

SQL Consulting

 

[GSINC]

Actually, I have compiled this contact table from other Oracle tables and imported them into Access into one table.

The pertanant fields in the table are:
Company
Renewaldt properly formated date/time (1/1/2005 example)
slffundjan04
slffundfeb04
slffundmar04
slffundapr04
slffundmay04
slffundjun04
slffundjul04
slffundaug04
slffundsep04
slffundoct04
slffundnov04
slffunddec04
quarter1-Needs to be populated
quarter2-Needs to be populated
quarter3-Needs to be populated
quarter4-Needs to be populated

Our data collection began on 01/01/2004 so no prior data is present in this scenario we only have the current ability to review quarters 3 and 4 for the companies which is ok.

 

[r937]

ah, can't help you, sorry

was hoping to compute which months to add together based on date arithmetic, like, you know, QUARTER = FLOOR((MONTH(DATECOL)-1)/3)+1 or something similar, to be adjusted based on RenewDate

but your month values are hardcoded





rudy

http://r937.com/

SQL Consulting

 

[GSINC]

Actually they are not I am pulling them from a check register, I put them into the table in that manner Mistake on my part. The companies have mutiple entries by date in the oracle table. Can you explain the statement above?

 

[r937]

okay, say the month is 8

subtract 1 gives 7

divide by 3 and throw away the remainder, you get 2

add 1, you get 3

august is in the 3rd quarter

that's for a january - december year

let's say the year runs may - april

subtract 5-1 (may-january) = 4 from august

then (4-1)/3 + 1 gives 2

august is in the 2nd quarter of a may-april year

easy, eh?

rudy

http://r937.com/

SQL Consulting

 

[GSINC]

Okay, I think I understand the concept of the statment QUARTER = FLOOR((MONTH(DATECOL)-1)/3)+1 I am trying to apply it now.

I have changed how I am compiling the amount spent by company so it now looks like this.

Company amountspent checkdte renewaldt
ABC 2115.12 1/15/2004 5/01/2004
ABC 9522.85 1/31/2004 5/01/2004
ABC 6527.11 2/25/2004 5/01/2004
GHI 10021.85 1/15/2004 7/01/2004
GHI 9528.45 1/25/2004 7/01/2004
GHI 4528.77 2/05/2004 7/01/2004
GHI 2125.85 2/10/2004 7/01/2004
JKL 9584.25 1/15/2004 8/01/2004
JKL 7456.00 1/25/2004 8/01/2004

But Access say it doesn't understand the FLOOR

 

[PHV]

Try to replace this:
FLOOR((MONTH(DATECOL)-1)/3)+1
By this:
(MONTH(DATECOL)-1)\3)+1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

Since the companies have different renewal dates, you need to add one more step to frameshift the checkdate into a standard calendar year. For example, if the renewal date is 5/1 (4 months beyond the standard calendar year) and the checkdate is 7/1, that is really the 3rd month (7-4) of their annual period. So when calculating the Quarter ...

Int((MONTH(DateAdd("m",1-Month(renewalDate),checkdate))-1)/3)+1

or

Format(DateAdd("m",1-Month(renewalDate),checkdate),"q")


Be careful if your checkdates span multiple renewal periods.

 

[r937]

thanks for jumping in, guys

"frameshift" -- excellent word!

rudy

http://r937.com/

SQL Consulting

 

[OhioSteve]

I have worked on this question for awhile.

First, you need to change your table structure. The current structure is not normalized. That means that it does not follow the text book rules for relational databases. You need a structure similar to this~

ClientTable
companyID (PK)
companyName
companyRenewalDate

SaleTable
amount
date
saleID (PK)
companyID (FK)

PK=primary key
FK=foreign key

I have also considered your question. Given the table structure above, the obvious starting point is this~

SELECT
clients.companyID,
clients.companyName,
clients.renewalDate,
sales.date,
sales.amount
FROM sales
RIGHT JOIN clients ON sales.companyID = clients.companyID;

I have worked with companies that did not have standard quarters. It is pretty easy to accomodate nonstandard quarters. Your situation is different. You want EVERY CLIENT to have a different set of quarters. That is a chaotic situation.

Is it possible to change that part of the task? If not, we can probably build a query for you. But it will be big, ugly, and not very elegant.

 

[OhioSteve]

Let's assume that you change renewal date slightly. Call it activationDate. It is the date that the client company began its relationship with you.

Given that change, you can construct this query. It almost calculates the number of months between the activation date and the sale date:

SELECT
clients.companyID,
clients.companyName,
clients.activationDate,
sales.date, Int(([date]-[activationDate])/30)+1 AS relativeMonth,
sales.amount
FROM sales
RIGHT JOIN clients ON sales.companyID = clients.companyID;

The reason I say "almost" is because I must set the number of days in a month. In the real world, not all months have the same number of days.

You could use this logic to answer a question such as "How fast do our sales with a client grow, after they start using our services?" Obviously you would put other queries on top of my query. This is only a starting point.

 

[GSINC]

CHECK this code out. Using a month lookup table and this code its working. Some changes need to be made for 2005 and on but WOW!

Option Compare Database
Dim cn As ADODB.Connection
Dim rs, rs1, rs2, rsmnth, rsgen As Recordset
Dim Mnth, Yr, Counter, calender As Integer
Dim q1, q2, q3, q4, temp As String



Private Sub cmdcalc_Click()
Set cn = CurrentProject.Connection
Set rs = cn.Execute("SELECT [Group Number] FROM Contacts ORDER BY [Group Number]")
Do While rs.EOF = False
Set rs1 = cn.Execute("select [Renewal Date] from contacts where [group number] = " & rs.Fields(0) & " ")
Mnth = Month(rs1.Fields(0))
Yr = Year(rs1.Fields(0))
Yr = Yr - 1
calender = 1
q1 = 0
q2 = 0
q3 = 0
q4 = 0
temp = 0
Do While calender < 13
If Yr = 2004 Then
Set rsmnth = cn.Execute("Select Mnth from LookUPmONTH Where ivalue = " & Mnth & " ")
Set rsgen = cn.Execute("Select [" & rsmnth.Fields(0) & "] from contacts where [group number] = " & rs.Fields(0) & " ")
If IsNull(rsgen.Fields(0)) = True Then
temp = 0
Else
temp = rsgen.Fields(0)
End If
End If
If calender < 4 Then
q1 = q1 + temp
temp = 0
ElseIf calender > 3 And calender < 7 Then
q2 = q2 + temp
temp = 0
ElseIf calender > 6 And calender < 10 Then
q3 = q3 + temp
temp = 0
ElseIf calender > 9 And calender < 13 Then
q4 = q4 + temp
temp = 0
End If
If Mnth = 12 Then
Mnth = 1
Yr = Yr + 1
Else
Mnth = Mnth + 1
End If
calender = calender + 1
Loop
Set rs2 = cn.Execute(" UPDATE CONTACT SET [QUARTER 1TH] = " & q1 & ", [QUARTER 2TH] = " & q2 & ",[QUARTER 3TH] = " & q3 & ", [QUARTER 4TH] = " & q4 & " WHERE GRPNO =" & rs.Fields(0) & " ")
rs.MoveNext
Loop
MsgBox " Finished updating quarter values"
End Sub

 

[GSINC]

The above code is for the original format that I layed in the beginning of the thread. Here is the refence table.

ivalue Mnth
1 Actual Self Fund jan
2 Actual Self Fund feb
3 Actual Self Fund mar
4 Actual Self Fund apr
5 Actual Self Fund may
6 Actual Self Fund jun
7 Actual Self Fund jul
8 Actual Self Fund aug
9 Actual Self Fund sep
10 Actual Self Fund oct
11 Actual Self Fund nov
12 Actual Self Fund dec