tickler for quarterly reports

[spence]

I'm using Access to manage 700 annual budgets, each tied to a client, in a
simple table:

[BudgetID] (key field)
[BudgetStartDate]
[BudgetEndDate]
[ClientID] (tied to my client table's key field)

The budgets always run 365 days and they always start on the first of the
month. However, their start dates are scattered throughout the year rather
than aligning with a calendar or fiscal year.

I am responsible for generating quarterly financial reports for each budget
(reports which are done outside of Access) and so I need to create a query
that I can run on the first of every month that will give me a list of all
budgets where a quarter has just ended, e.g. on 08/01/06 I want to be able to
pull a list of all budgets that started 05/01/06 (Q1), 02/01/06 (Q2),
11/01/05 (Q3), and 08/01/05 (Q4). (Budget end dates would work as well as
start dates for my purposes.)

I have been managing this - with my limited knowledge - using a query that
requires four date parameters to be entered based on a cheat sheet that I
created. I would very much like to eliminate the inherent margin of error in
that scenario by reducing it to a single parameter -or no parameters based on
the day the query is run. *Ideally* the query would return not only the list
of budgets where quarterly reports are due, but also which quarter (1st, 2nd,
3rd, or 4th) for which a report is due.

I thought this would be easier than it is, a situation that has been
exacerbated by my particular difficulty with time relationships. Thanks very
much in advance for any guidance.

spence

 

[Allen Browne]

Create a table with just one field named CountID, type Number, size Long,
marked as primary key. Save as tblCount. Enter the values 0 to 3, i.e. 4
records.

Create a query that uses both your main table and tblCount, with no join
(i.e. no line joining the 2 tables in the upper pane of query design), known
as a Cartesian Product (i.e. every possible combination of the two.)

In a fresh column of the Field row, enter:
QtrStart: DateAdd("q", [CountID],[BudgetStartDate])

You now have a record for each quarter for each budget. Add whatever
criteria you want under this field.

 

[spence]

Thanks, Allen. That got me most of the way there. Can you help me with the
syntax to get quarter end dates in addition (or instead of) quarter start
dates?

Also, wondering how to write an expression that can turn the values in
[CountID] into "Quarter1", "Quarter2), etc.

Regards,
spence

Create a table with just one field named CountID, type Number, size Long,
marked as primary key. Save as tblCount. Enter the values 0 to 3, i.e. 4
records.

Create a query that uses both your main table and tblCount, with no join
(i.e. no line joining the 2 tables in the upper pane of query design), known
as a Cartesian Product (i.e. every possible combination of the two.)

In a fresh column of the Field row, enter:
QtrStart: DateAdd("q", [CountID],[BudgetStartDate])

You now have a record for each quarter for each budget. Add whatever
criteria you want under this field.

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

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

I'm using Access to manage 700 annual budgets, each tied to a client, in a
simple table:

[BudgetID] (key field)
[BudgetStartDate]
[BudgetEndDate]
[ClientID] (tied to my client table's key field)

The budgets always run 365 days and they always start on the first of the
month. However, their start dates are scattered throughout the year rather
than aligning with a calendar or fiscal year.

I am responsible for generating quarterly financial reports for each
budget
(reports which are done outside of Access) and so I need to create a query
that I can run on the first of every month that will give me a list of all
budgets where a quarter has just ended, e.g. on 08/01/06 I want to be able
to
pull a list of all budgets that started 05/01/06 (Q1), 02/01/06 (Q2),
11/01/05 (Q3), and 08/01/05 (Q4). (Budget end dates would work as well as
start dates for my purposes.)

I have been managing this - with my limited knowledge - using a query that
requires four date parameters to be entered based on a cheat sheet that I
created. I would very much like to eliminate the inherent margin of error
in
that scenario by reducing it to a single parameter -or no parameters based
on
the day the query is run. *Ideally* the query would return not only the
list
of budgets where quarterly reports are due, but also which quarter (1st,
2nd,
3rd, or 4th) for which a report is due.

I thought this would be easier than it is, a situation that has been
exacerbated by my particular difficulty with time relationships. Thanks
very
much in advance for any guidance.

spence

 

[Allen Browne]

Quarter end date is:
DateAdd("q", 1, [QuarterStartDate]) - 1

The expression would be:
"Quarter" & Format([QuarterStartDate], "q")

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

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

Thanks, Allen. That got me most of the way there. Can you help me with the
syntax to get quarter end dates in addition (or instead of) quarter start
dates?

Also, wondering how to write an expression that can turn the values in
[CountID] into "Quarter1", "Quarter2), etc.

Regards,
spence

Create a table with just one field named CountID, type Number, size Long,
marked as primary key. Save as tblCount. Enter the values 0 to 3, i.e. 4
records.

Create a query that uses both your main table and tblCount, with no join
(i.e. no line joining the 2 tables in the upper pane of query design),
known
as a Cartesian Product (i.e. every possible combination of the two.)

In a fresh column of the Field row, enter:
QtrStart: DateAdd("q", [CountID],[BudgetStartDate])

You now have a record for each quarter for each budget. Add whatever
criteria you want under this field.

I'm using Access to manage 700 annual budgets, each tied to a client,
in a
simple table:

[BudgetID] (key field)
[BudgetStartDate]
[BudgetEndDate]
[ClientID] (tied to my client table's key field)

The budgets always run 365 days and they always start on the first of
the
month. However, their start dates are scattered throughout the year
rather
than aligning with a calendar or fiscal year.

I am responsible for generating quarterly financial reports for each
budget
(reports which are done outside of Access) and so I need to create a
query
that I can run on the first of every month that will give me a list of
all
budgets where a quarter has just ended, e.g. on 08/01/06 I want to be
able
to
pull a list of all budgets that started 05/01/06 (Q1), 02/01/06 (Q2),
11/01/05 (Q3), and 08/01/05 (Q4). (Budget end dates would work as well
as
start dates for my purposes.)

I have been managing this - with my limited knowledge - using a query
that
requires four date parameters to be entered based on a cheat sheet that
I
created. I would very much like to eliminate the inherent margin of
error
in
that scenario by reducing it to a single parameter -or no parameters
based
on
the day the query is run. *Ideally* the query would return not only the
list
of budgets where quarterly reports are due, but also which quarter
(1st,
2nd,
3rd, or 4th) for which a report is due.

I thought this would be easier than it is, a situation that has been
exacerbated by my particular difficulty with time relationships. Thanks
very
much in advance for any guidance.