Payment calendar

Hello experts,
I am trying to put together on excel or access a calendar for  our sale agents
We have customers with different expiry date and billing cycle
For example a customer with service A has a monthly payment plan so the amount should be taken and repeated every month
While a customer with quarterly cycle I need to take the month of payment for example FEB place the amount once in FEB then May  then Aug (Feb +3)
A customer with annual term is basically once
The data I have is
Customer A 100 Monthly
Customer B 50 quarterly from  Feb
Customer C  Annually 500 June  
      Jan      Feb      March      April      May      June       July       Aug      Sep
Customer A      100      100      100      100      100      100      100      100      100
Customer B            50                  50                  50      
Customer C                                    500
dina78Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EirmanChief Operations ManagerCommented:
You say that you trying to put together a Calendar, but you seem to have what you need for excel with your layout above. Obviously you need something more, but I can't discern what that is. Can you provide more information please.
0
dina78Author Commented:
what i have is row data with 5000 customers
Customer A monthly 100
Customer B Quartely 50 starting from Feb
Customer C Annualy 500 in oct
Customer D Quartely 1000 starting from say March

i want i want to take these values and duplicate them in a calendar
please see sample file .. it may help
sample.xlsx
0
Dale FyeCommented:
If I were doing this in Access, I would have a table for Plan_Types (tbl_Plan_Types) and it would have fields:

PlanType: (text) A, B, C
Desc: (text) Monthly, Quarterly, Annually
Arg: (text) m, q, yyyy
Amt: Currency

I would also probably add from and thru dates to this table to allow me to change the Amount value over time.

I would also have a table of numbers (tbl_Numbers) which contains a single field intNumber and 10 records (the values 0 to 9).  I would then create a query (qry_Numbers) based on this table that looks like:

SELECT Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

With those two tables, query and a third (tbl_Contracts), I could would create a query that looks something like:

SELECT C.ClientID,
             C.PlanType,
             Month(DateAdd(PT.Arg, N.intNumber, C.StartDate)) as PmtMonthNum,
             Format(DateAdd(PT.Arg, N.intNumber, C.StartDate), "mmm") as PmtMonth
             PT.Amt as PmtAmt
FROM tbl_Contracts C (INNER JOIN tbl_Plan_Types PT ON C.PlanType = PT.PlanType), tbl_Numbers
WHERE DateAdd(PT.Arg, N.intNumber, C.StartDate) >= #1/1/13#
AND DateAdd(PT.Arg, N.intNumber, C.StartDate) < #1/1/14#

this would produce a list like:

1    A   1   Jan   100
1    A   2   Feb  100
1    A   3   Mar  100
...
2    B   2   Feb   50
2    B   5   Mar  50
2    B   8   Aug   50
2    B   11 Nov   50
3    C   6    Jun  500

I would then use a Crosstab query to convert that to format you have depicted above.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dina78Author Commented:
hello,
thanks for the idea.. i am trying to use what you have exactly but when i run
SELECT C.ClientID,
             C.PlanType,
             Month(DateAdd(PT.Arg, N.intNumber, C.StartDate)) as PmtMonthNum,
             Format(DateAdd(PT.Arg, N.intNumber, C.StartDate), "mmm") as PmtMonth,
             PT.Amt as PmtAmt

FROM tbl_Contracts C (INNER JOIN tbl_Plan_type PT ON C.PlanType = PT.PlanType), tbl_numbers

WHERE DateAdd(PT.Arg, N.intNumber, C.StartDate) >= #1/1/13#

AND DateAdd(PT.Arg, N.intNumber, C.StartDate) < #1/1/14#

i get that
Dyntax error in FORM clauses

any idea how to fix it
Dina
0
Dale FyeCommented:
That was air code, written from my iPad.  I may have the paranthesis in the wrong place.

Try building it from the query grid.  Ad the Contracts table, then the plan type table, join them on the PlanType field, then add tbl_numbers, but do not join it to either of the other tables.

Then build your columns and add the criteria.

on my way to a meeting, will check back around noon Eastern US time.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dina78Author Commented:
I guess my confusion is because the planTpe is only a field in the  (tbl_Plan_type) table and cant be linked to  (qry_Numbers) that is basically one field of numbers from 0 to 99
I guess im missing something here¿
 
Help?
0
dina78Author Commented:
ok so it is working but only when i remove the where condtion

SELECT C.Company,
             C.PlanType,
             Month(DateAdd(PT.Arg, N.intNumber, C.StartDate)) as PmtMonthNum,
             Format(DateAdd(PT.Arg, N.intNumber, C.StartDate), "mmm") as PmtMonth,
             C.Amt as PmtAmt
FROM tbl_Contracts C INNER JOIN tbl_Plan_Type PT ON C.PlanType = PT.PlanType, tbl_Numbers N

WHERE DateAdd(PT.Arg, N.intNumber, C.StartDate) >= #1/1/13#
AND DateAdd(PT.Arg, N.intNumber, C.StartDate) < #1/1/14#

propblem is all the annual amoy is showing as an error
but this is great
0
dina78Author Commented:
if you see the sample you will notice that type C is generating an error and it is geting duplication
Doc1.docx
0
dina78Author Commented:
sorry ment to attach this file
Doc2.docx
0
dina78Author Commented:
ok so i had yyy insted of YYYY ! its working fine! finally although still repeated for the year values

if i have a semi annual and every 4 months cycle how would i add this

Dina
0
dina78Author Commented:
each year value is repated 10 times
0
Dale FyeCommented:
OK, post the SQL string that you are using for the query, and the screen shot of the current results and I'll take a look.
0
dina78Author Commented:
i removed the repeated values by using distinct function.
but im noticing that for example my monthly clients are missing 2 months.  i have attached a screen shot and if you notice oct and Sep is missing

SELECT DISTINCT C.Company, C.PlanType, C.EndDate, Month(DateAdd(PT.Arg,N.intNumber,C.Enddate)) AS PmtMonthNum, Format(DateAdd(PT.Arg,N.intNumber,C.Enddate),"mmm") AS PmtMonth, C.Amt AS PmtAmt
FROM tbl_Numbers AS N, tbl_Contracts AS C INNER JOIN tbl_Plan_Type AS PT ON C.PlanType=PT.PlanType;

also what if i have a Semi annual cycle or every 4 months how can i include them? or shall i post a new question for that.

youve been great help thank you:)
Doc3.docx
0
Dale FyeCommented:
Can you provide some sample data in a database?  It looks like the db you are using may just be a sample.  If not, change critical information like client names and anything else that might be proprietary.

1.  I don't know why you would need the DISTINCT clause, but if you can provide the sample database I can figure that out.

2.  My guess on the missing months is that the Nov/Dec months shown in your screen shot are for 2012, not 2013, and since your numbers table probably only contains 10 records, you would only get 8 months in 2013.  I've added a column for PmYearNum and added an ORDER BY clause to the query below.

SELECT C.Company
           , C.PlanType
           , C.EndDate
           , Year(DateAdd(PT.Arg,N.intNumber,C.Enddate)) AS PmtYearNum
           , Month(DateAdd(PT.Arg,N.intNumber,C.Enddate)) AS PmtMonthNum
           , Format(DateAdd(PT.Arg,N.intNumber,C.Enddate),"mmm") AS PmtMonth
           , C.Amt AS PmtAmt
FROM tbl_Numbers AS N
         , tbl_Contracts AS C INNER JOIN tbl_Plan_Type AS PT ON C.PlanType=PT.PlanType
ORDER BY C.Company
                , Year(DateAdd(PT.Arg,N.intNumber,C.Enddate)) AS PmtMonthNum
                , Month(DateAdd(PT.Arg,N.intNumber,C.Enddate)) AS PmtMonthNum
0
dina78Author Commented:
i have attached a sample.
i"ll work with your feedback meanwhile
thank you:)
sample.xlsx
0
Dale FyeCommented:
Your duplicates were coming from the Products that are assigned to each company.  

Given the data you gave me, I created a PaymentTypes table and developed the first part of the query, which identifies what months payments are due for each company/product combination.

So, are you looking for one record per company, with the sum of all their products, or are you looking for one record per company/product?

There is a "Value" column in your table.  Is that the total value of that product over the period [Start] to [End]  or is that the annual value, or something else?

What I have so far is attached.  When you advise me how to deal with the [Amount] column and whether you are looking for 1 record per company or 1 record per company/product, I'll be able to finish the query and will send a sample database back to you.

Note that only the quarterly and monthly payment types have more than one record in this result set, which is filtered for those company/products which have the start/end or some month between the start and end in calendar year 2013.
qry-PC-Client-Payments.xlsx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.