Periods

[William Foster]

Good evening all,

I am trying to create a query that returns a variety of counts against
period definitions; for instance how many businesses were created in a
particular period and how many businesses folded in that particular period
[Sorry for the example, but it is the best I could come up with].

If the table has the business name, business creation date and business
folding date if applicable; how can I return a query that has two columns;
one that counts businesses created in each period, and another which counts
businesses folded in that same period. I use the following expression to
return the period for each column: 'Year([Fictional_Date]) & "/" &
Month([Fictional_Date])'

Period Created Folded
2004/01 10 5
2004/02 5 10
etc...

I tried creating a table of periods, but I can not include this in the query
because it has no links; I can say how many businesses were created in a
period, and then of those how many folded in each period; but that is not how
I need the data returned.

Any assistance you may be able to provide would be greatly appreciated.

Yours sincerely,

William Foster

 

[Allen Browne]

A Total query will calculate the number of new businesses created per month.
It will be something like this:
SELECT Year(CreatedDate) AS TheYear,
Month(CreatedDate) AS TheMonth,
Count(ID) AS Created
FROM Table1
WHERE CreatedDate Is Not Null
GROUP BY Year(CreatedDate), Month(CreatedDate);

You can then create a similar query calculating the number of business that
folded by month.

Then create a 3rd query that combines the 2, outer-joining on TheYear and
TheMonth.

An alternative approach would be to use a subquery to get the other value
(rather than separate queries), though doubt this would be more efficient to
execute. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[William Foster]

Allen,

Thanks for your assistance!

William Foster

A Total query will calculate the number of new businesses created per month.
It will be something like this:
SELECT Year(CreatedDate) AS TheYear,
Month(CreatedDate) AS TheMonth,
Count(ID) AS Created
FROM Table1
WHERE CreatedDate Is Not Null
GROUP BY Year(CreatedDate), Month(CreatedDate);

You can then create a similar query calculating the number of business that
folded by month.

Then create a 3rd query that combines the 2, outer-joining on TheYear and
TheMonth.

An alternative approach would be to use a subquery to get the other value
(rather than separate queries), though doubt this would be more efficient to
execute. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Good evening all,

I am trying to create a query that returns a variety of counts against
period definitions; for instance how many businesses were created in a
particular period and how many businesses folded in that particular period
[Sorry for the example, but it is the best I could come up with].

If the table has the business name, business creation date and business
folding date if applicable; how can I return a query that has two columns;
one that counts businesses created in each period, and another which
counts
businesses folded in that same period. I use the following expression to
return the period for each column: 'Year([Fictional_Date]) & "/" &
Month([Fictional_Date])'

Period Created Folded
2004/01 10 5
2004/02 5 10
etc...

I tried creating a table of periods, but I can not include this in the
query
because it has no links; I can say how many businesses were created in a
period, and then of those how many folded in each period; but that is not
how
I need the data returned.

Any assistance you may be able to provide would be greatly appreciated.

Yours sincerely,

William Foster