Chart growth by quarter with two date fields

[caligirl061477]

I have been able to acheive the result I need in excel 07 but have no idea
how to do it in access 07.

I have the follwing two queries.

Query1
[Start] [Portfolio] [SqFt]
[1999 Q4] [EB] [390306]
[2000 Q1] [AUS] [1144327]
[2000 Q2] [EB] [749816]

Query2
[End] [Portfolio] [Sqft]
[2000 Q1] [AUS] [88000]
[2001 Q1] [AUS] [385075]

The result I need is something that looks like the following for every
quarter so I can then chart growth by quarter.

[QTR] [AUS] [EB]
[1999 Q4] [0] [390306]
[2000 Q1] [1056327] [390306]
[2000 Q2] [1056327] [1140122]
In excel is used Columns: I, J, K & Rows: 3, 4, 5, 6 for the above result.

In excel, i used the follwing two formulas in columns I, J, K and rows
3,4,5,6:
Row 4= +SUMIFS($C:$C,$A:$A,$I4,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I4,$F:$F,J$3)
Row 5= +SUMIFS($C:$C,$A:$A,$I5,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I5,$F:$F,J$3)+J4
Row 6= +SUMIFS($C:$C,$A:$A,$I5,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I5,$F:$F,J$3)+J4

Column A , B, & C are Start, Portfolio, and Sq Ft aka Query 1
Colum E, F, & G, are End, Portfolio, and Sq Ft aka Query 2

This is a lot and I may be doing this the hard way but this is the first
database that I have built myself and need detailed help/instruction if
possible. Thank you for your time!!!
P.S. I could email the excel spreadsheet if that would help. Thanks again,

 

[Duane Hookom]

You need to tell us about your tables/data and what you are attempting to do
with the data.

 

[caligirl061477]

I have one table that has the following 4 main columns:
[Start] [End] [Portfolio] [Sqft]

The portfolio is the name of the project group. The Start and End column
are the dates we started the project and ended the project. What I am trying
to accomplish is growth chart by quarter. The chart will have Sqft on the
vertical axis and quarters on the horizontal axis. What I need charted is
the Sqft that we were currently working on on the last day of each quarter.
In other words, SqFt as of the last day of last quarter plus and new
projects, less any projects that ended during the current quarter. Does this
help at all?

You need to tell us about your tables/data and what you are attempting to do
with the data.

--
Duane Hookom
Microsoft Access MVP

I have been able to acheive the result I need in excel 07 but have no idea
how to do it in access 07.

I have the follwing two queries.

Query1
[Start] [Portfolio] [SqFt]
[1999 Q4] [EB] [390306]
[2000 Q1] [AUS] [1144327]
[2000 Q2] [EB] [749816]

Query2
[End] [Portfolio] [Sqft]
[2000 Q1] [AUS] [88000]
[2001 Q1] [AUS] [385075]

The result I need is something that looks like the following for every
quarter so I can then chart growth by quarter.

[QTR] [AUS] [EB]
[1999 Q4] [0] [390306]
[2000 Q1] [1056327] [390306]
[2000 Q2] [1056327] [1140122]
In excel is used Columns: I, J, K & Rows: 3, 4, 5, 6 for the above result.

In excel, i used the follwing two formulas in columns I, J, K and rows
3,4,5,6:
Row 4= +SUMIFS($C:$C,$A:$A,$I4,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I4,$F:$F,J$3)
Row 5= +SUMIFS($C:$C,$A:$A,$I5,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I5,$F:$F,J$3)+J4
Row 6= +SUMIFS($C:$C,$A:$A,$I5,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I5,$F:$F,J$3)+J4

Column A , B, & C are Start, Portfolio, and Sq Ft aka Query 1
Colum E, F, & G, are End, Portfolio, and Sq Ft aka Query 2

This is a lot and I may be doing this the hard way but this is the first
database that I have built myself and need detailed help/instruction if
possible. Thank you for your time!!!
P.S. I could email the excel spreadsheet if that would help. Thanks again,

 

[Duane Hookom]

To keep this simple, I would create a table of quarters with the last date
and quarter name:

tblQuarters
===================
[quaLastdate] [quaTitle]
3/31/2007 2007 Q1
6/30/2007 2007 Q2
.....
12/31/2008 2008 Q4
....

You then can create a query that counts sums the SqFt by quarter end like
SELECT quaTitle, quaLastDate, Portfolio, Sum(SqFt) as SumSqFt
FROM tblNoNameGiven, tblQuarters
WHERE quaLastDate Between [Start] and [End]
GROUP BY quaTitle, quaLastDate, Portfolio;

Create a crosstab query based on the above query with:
Column Heading: Portfolio
Row Headings: quaTitle, quaLastDate
Value: Sum(SumSqFt)

This could all probably be done with fewer queries but this should provide a
better understanding.

--
Duane Hookom
Microsoft Access MVP

I have one table that has the following 4 main columns:
[Start] [End] [Portfolio] [Sqft]

The portfolio is the name of the project group. The Start and End column
are the dates we started the project and ended the project. What I am trying
to accomplish is growth chart by quarter. The chart will have Sqft on the
vertical axis and quarters on the horizontal axis. What I need charted is
the Sqft that we were currently working on on the last day of each quarter.
In other words, SqFt as of the last day of last quarter plus and new
projects, less any projects that ended during the current quarter. Does this
help at all?

You need to tell us about your tables/data and what you are attempting to do
with the data.

--
Duane Hookom
Microsoft Access MVP

I have been able to acheive the result I need in excel 07 but have no idea
how to do it in access 07.

I have the follwing two queries.

Query1
[Start] [Portfolio] [SqFt]
[1999 Q4] [EB] [390306]
[2000 Q1] [AUS] [1144327]
[2000 Q2] [EB] [749816]

Query2
[End] [Portfolio] [Sqft]
[2000 Q1] [AUS] [88000]
[2001 Q1] [AUS] [385075]

The result I need is something that looks like the following for every
quarter so I can then chart growth by quarter.

[QTR] [AUS] [EB]
[1999 Q4] [0] [390306]
[2000 Q1] [1056327] [390306]
[2000 Q2] [1056327] [1140122]
In excel is used Columns: I, J, K & Rows: 3, 4, 5, 6 for the above result.

In excel, i used the follwing two formulas in columns I, J, K and rows
3,4,5,6:
Row 4= +SUMIFS($C:$C,$A:$A,$I4,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I4,$F:$F,J$3)
Row 5= +SUMIFS($C:$C,$A:$A,$I5,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I5,$F:$F,J$3)+J4
Row 6= +SUMIFS($C:$C,$A:$A,$I5,$B:$B,J$3)-SUMIFS($G:$G,$E:$E,$I5,$F:$F,J$3)+J4

Column A , B, & C are Start, Portfolio, and Sq Ft aka Query 1
Colum E, F, & G, are End, Portfolio, and Sq Ft aka Query 2

This is a lot and I may be doing this the hard way but this is the first
database that I have built myself and need detailed help/instruction if
possible. Thank you for your time!!!
P.S. I could email the excel spreadsheet if that would help. Thanks again,