Creating a "grid" of data

[DawnTreader]

Hello All

been a while since i posted an access problem but here is one that has
been rolling in my mind all day.

i have a "contract" database that i need to track customer contracts
with. my table has a start date and an end date. i also have a query
that calculates the number of months the contract is for.

what i need is a way to generate a grid that shows a month as
"populated". meaning that given the dates the contract is generating a
revenue each month. i am thinking this would be like a gantt chart,
but it is not as complicated. i dont think...

so what i would like it to do is this:

Contract StartDate EndDate Jan Feb Mar Apr May Jun Jul Aug
123 01/01/2010 08/30/2010 y y y y y y y y
124 03/01/2010 07/31/2010 y y y y
125 02/01/2010 06/30/2010 y y y y y
126 04/01/2010 06/30/2010 y y y
127 04/01/2010 08/30/2010 y y y y y

is there a way to get access to calculate that a month should be
"occupied" because of the start and end dates?

i am thinking that i am going to need another table to store the month
names. i was also thinking that sometimes because a customer might
cancel for a month i would have to store the months that the contract
is actually valid for in a table, probably a list generated based on
the start date and end date by some code. then have a field in that
table that the user can check or uncheck to say the month was "not
used".

i hope this is clear, thanks for any and all help!

 

[DawnTreader]

Hello All

been a while since i posted an access problem but here is one that has
been rolling in my mind all day.

i have a "contract" database that i need to track customer contracts
with. my table has a start date and an end date. i also have a query
that calculates the number of months the contract is for.

what i need is a way to generate a grid that shows a month as
"populated". meaning that given the dates the contract is generating a
revenue each month. i am thinking this would be like a gantt chart,
but it is not as complicated. i dont think...

so what i would like it to do is this:

Contract        StartDate       EndDate Jan     Feb    Mar     Apr     May     Jun     Jul     Aug
123     01/01/2010      08/30/2010      y       y       y       y       y       y       y       y
124     03/01/2010      07/31/2010                              y       y       y       y
125     02/01/2010      06/30/2010              y      y       y       y       y
126     04/01/2010      06/30/2010                              y       y       y
127     04/01/2010      08/30/2010                              y       y       y       y       y

is there a way to get access to calculate that a month should be
"occupied" because of the start and end dates?

i am thinking that i am going to need another table to store the month
names. i was also thinking that sometimes because a customer might
cancel for a month i would have to store the months that the contract
is actually valid for in a table, probably a list generated based on
the start date and end date by some code. then have a field in that
table that the user can check or uncheck to say the month was "not
used".

i hope this is clear, thanks for any and all help!

Dang, that table didnt quite turn out. but i think you can read
between the lines...

 

[John Spencer]

One method would be to create a table with two fields (DateTime) and one
record for each month and year you are interested in.
CalendarMonth (table)
BeginDate
EndDate

Then you can join the Contract table to the CalendarMonth table with a
non-equi join or a cartesian join.

SELECT Contract.*, CalendarMonth.EndDate
FROM Contract INNER JOIN CalendarMonth
ON Contract.StartDate <= CalendarMonth.EndDate
AND Contract.EndDate >= CalendarMonth.StartDate

Of course, if your contracts always start on the first day of the month and
end of the last day of the month, you would only need one field in
CalendarMonth and could use something like

SELECT Contract.*, CalendarMonth.EndDate
FROM CalendarMonth INNER JOIN Contract
ON CalendarMonth.TheDate >= Contract.StartDate
AND CalendarMonth.TheDate <= Contract.EndDate

You should be able to turn either one of those queries into a Crosstab query.
Or if you feel the necessity to store the individual months of activity for
each contract, you should be able to use the idea to append records to a table
ContractActivity that has the contract id and the month of the activity.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[DawnTreader]

One method would be to create a table with two fields (DateTime) and one
record for each month and year you are interested in.
CalendarMonth (table)
BeginDate
EndDate

Then you can join the Contract table to the CalendarMonth table with a
non-equi join or a cartesian join.

SELECT Contract.*, CalendarMonth.EndDate
FROM Contract INNER JOIN CalendarMonth
ON Contract.StartDate <= CalendarMonth.EndDate
AND Contract.EndDate >= CalendarMonth.StartDate

Of course, if your contracts always start on the first day of the month and
end of the last day of the month, you would only need one field in
CalendarMonth and could use something like

SELECT Contract.*, CalendarMonth.EndDate
FROM  CalendarMonth INNER JOIN Contract
ON CalendarMonth.TheDate >= Contract.StartDate
AND CalendarMonth.TheDate <= Contract.EndDate

You should be able to turn either one of those queries into a Crosstab query.
  Or if you feel the necessity to store the individual months of activity for
each contract, you should be able to use the idea to append records to a table
ContractActivity that has the contract id and the month of the activity.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County












- Show quoted text -

this is kinda where i was headed. i have the second table for the
contract activity, but now i am wondering how to populate it.

i can manually populate it, but i am thinking i should get access to
do it for me. the problem is can i use a query or do i need code?

i was thinking of making a code that would use a do sql command to
insert the appropriate number of activity months with some sort of
loop based on the calculated number of months that the contract is
for.

does this seem like a good plan? anyone got some air code in thier
head?

is there a query that would do that instead of coding?