Help with functional design of MDB data

[DMUM via AccessMonster.com]

Hello

I am working on a database that users update data items on a daily,weekly,
monthly and quarterly bases. At the end of the month the "compliance" group
runs reports to consolidate and review the data. The problem I am having is
trying to figure out how to update the quarterly data which is only updated
once a quarter but must be seen on the report each month. I have come up with
a couple ideas, but they seem more difficult then might be necessary. So I
am hoping someone can come up with a recommendation.

First my scenario:

Say Jan-Mar is Quarter 1
User inputs data on Jan 1 for Qtr1 lets call item 555
Report for Jan 1 shows 555 and corresponding information

Now it is Feb - since 555 is a quarterly entry user will not update new data
but when the Feb report is run, 555 data from Jan must show up

Now it is Mar - again since 555 is quarterly, user will not update new data
but the Report must show Jan data

Now it is Apr - A new quarter - user must enter new data for the new quarter
to 555...the cycle starts again

My idea:

In table that holds the data add 2 additional field.
Field 1 = qtr#
Field 2 = number of each qtr(i.e 1 of qrt 2, 2 of qrt 2 etc. Only it will
just show number 1 , 2 or 3

On initial update of data by user for Jan Field1 will be 1, Field2 will also
be 1

Everytime that the form used to update the data is opened the system date
will be checked to see if it is a new month/new quarter - I'm thinking I need
to compare it to a quarterly calendar table, or write in code if Jan do
nothing, else if Feb do...or if Mar do... i think the calendar table will
work better - Yes?

If it is Feb or Mar
1. Have a Make Table qry run that extracts data rows Where criteria =
"quarter"
Field1 = 1
Field2 = 1

2.crosscheck the quarterly calendar table with the system date to identify
the quarter - in this case it would be 1 (For quarter 2, 3 and 4 if it is
found that a new quarter has started - update Field1 to 2, 3 or 4)

3. Update Field2 to 2 or 3 depending on the month of the quarter identified -
again, I will probably check the quarterly calendar table

4. Append the data table with the updated Quarterly information from the temp
table (made in step 1)

5. If New Qtr - do nothing - user will need to update manually

I hope I explained this well enough. Basicly I am asking for 2 things

1. Is my idea feasible? Do you foresee problems with it?
2. Is there a better way?

Thank you

 

[Guest]

There is a better way than having a field for quarter. Just have a
transaction datetime field. Then let the matchine tell you what QTR it is --
Format([YourTransaction],"q") will give you the numerical quarter
starting with January as first QTR.
You can use it as criteria to pull data for the current or last quarter --

CQsDate = DateAdd("q",-1,(Date()))+1
CQeDate = DateAdd("q",0,(Date()))+1

LQsDate = DateAdd("q",-2,(Date()))+1
LQeDate = DateAdd("q",-1,(Date()))

 

[DMUM via AccessMonster.com]

Thanks so much for your reply Karl. I knew someone could recommend something
I hadn't thought about. So you think everything else I propse is the best
way to do this?

Thanks
'-----------------------------------------------------------------------------
------------------

There is a better way than having a field for quarter. Just have a
transaction datetime field. Then let the matchine tell you what QTR it is --
Format([YourTransaction],"q") will give you the numerical quarter
starting with January as first QTR.
You can use it as criteria to pull data for the current or last quarter --

CQsDate = DateAdd("q",-1,(Date()))+1
CQeDate = DateAdd("q",0,(Date()))+1

LQsDate = DateAdd("q",-2,(Date()))+1
LQeDate = DateAdd("q",-1,(Date()))

Hello

[quoted text clipped - 61 lines]

Thank you

 

[Guest]

Post your SQL statement for your query and say what it is not doing that you
need it to do.

Thanks so much for your reply Karl. I knew someone could recommend something
I hadn't thought about. So you think everything else I propse is the best
way to do this?

Thanks
'-----------------------------------------------------------------------------
------------------

There is a better way than having a field for quarter. Just have a
transaction datetime field. Then let the matchine tell you what QTR it is --
Format([YourTransaction],"q") will give you the numerical quarter
starting with January as first QTR.
You can use it as criteria to pull data for the current or last quarter --

CQsDate = DateAdd("q",-1,(Date()))+1
CQeDate = DateAdd("q",0,(Date()))+1

LQsDate = DateAdd("q",-2,(Date()))+1
LQeDate = DateAdd("q",-1,(Date()))

Hello

[quoted text clipped - 61 lines]

Thank you