Automatically using end of month total for beginning of next month

[lisaeileen]

Good Afternoon Everyone.

I need some help with Access. I am keeping track of a huge amount of data and am trying to simplify it as much as possible. Here's what my table looks like :

Table

Field Name            Type       
GarrisonInput2ID      Auto number
Garrison              Number
MonthYear             Date/Time
TempLoss              Number
TermLoss              Number
VSIP/VERA             Number
Attrition             Number
RIF                   Number
ForeignNationalsLoss  Number
TempGains             Number
PermGains             Number
ForeignNationalGains  Number
StartingStrength      Number

What I need to do is use the previous end of month (EOM) total and automatically copy it to the beginning of the next month. As you can see I have a number of data points I'm tracking and don't really have a problem with the calculation for that (adding and subtracting gains and losses) What I can't figure out is how to get ACCESS to use the EOM totals from the previous month to start that calculation.

The calculation includes a starting on-board strength number (for 31 Oct 2011) and each time the garrison loses or gains an individual it needs to reflect in those numbers. What I want to do is push their gains and losses into the DB and have ACCESS do the rest based on their original Oct starting number......

I need to see a "closeout" for each month because I need to graph the data for a General and he needs to see pictures!

Any help you can provide will save me valuable medication.

I throw myself on the mercy of the ACCESS Gods.

Lisa

[C CSR]

Correct me if I'm wrong. You said you have a "...starting on-board strength number... and each time the garrison loses or gains [the on-board strength number] needs to reflect ... [the calculations made using the loses or gains dynamically]," meaning you overwrite the strength number as the gains and losses change. Is this what you're saying?

Then, at the end of the month, you want to freeze the "dynamic on-board strength number" somewhere as the "starting on-board strength number," ditch the rest of the data, and start over. So in effect you have 2 on-board strength numbers: A) number preserved from last period, and B) the dynamic number changing based on gains and losses.

That would be 2 numbers, stored within the period; one changes and one doesn't. Is that it?

[NeoPa]

Lisa, please correct or confirm, as I'm not so familiar with military terms as I might need to be for this question, but am I right in thinking that most of the fields in the table refer to adjustments throughout the month, while [StartingStrength] refers to the number of the personnel available at the start of the period (month) and [Garrison] refers to the adjusted figure which takes all the adjustments into account?

Generally, in RDBMS databases, it's not a good idea to store adjustments as well as the results of those adjustments. Anything that's calculable should not be saved. The concepts behind this are based on Set theory and are a part of the idea of Normalisation (See Database Normalisation and Table Structures for more on this).

If you read that you will probably understand that the point of the most critical importance is the design of the data at the first stage of the project. It may be that you will need to revisit yours to avoid getting yourself in knots later on. We'll be better able to advise on that when we have a clearer view of exactly what you're trying to achieve I suspect.

[lisaeileen]

C CSR and NeoPA - thank you both for the responses. To answer your questions - yes, there should be two fields - a starting field for the beginning of the month and an ending field. As the garrisons submit their numbers for each month - I'll update their gains and losses - this information will be added and subtracted from the starting number. Once all the garrisons have submitted their numbers for a month - that total will need to be moved to the start of the next month. The "freezing" that C CSR refers to is exactly what I need.....

[NeoPa]

Lisa, Could you please answer my question directly. If your answer was addressed to it (as you seem to feel) then I'm confused. I'm sure a direct answer would clarify the situation perfectly.

[lisaeileen]

NeoPa,

Garrison refers to the base that is submitting the information (it is a number because it is pulling for another table). There are 100+ bases reporting information. Each month, each garrison will give me their gains and losses for the previous month. I only get one submission, per garrison, per month. I was given each garrisons starting strength as of Oct 2011 - they are now reporting only their losses and gains per month. So, I need to subtract that information (or add depending on what it is) to come up with a new starting strength for the next month.

[NeoPa]

I'll take that as a correct in that case Lisa. That's fine. My next question pertains to how we would start to look at updating the table with the submitted data.

Does the data come in the form of a table, from which we might create new records, or as visible information that someone would want to enter into a form?

The approaches taken depending on the two different possible answers are radically different of course.

[C CSR]

Lisa,

This may seem over-simplified, but I keep thinking all you need is a working "hot" table and a monthly table off to the side that contains your final results at the end of the month, or whenever you choose to close-out. The tables would be linked by a "PeriodID." So, after you work out your calculations (which hasn't been fully explained to me yet), when you run the update, the two tables are joined for the query and it puts whatever the final strength data is from the Hot Table into a NEW RECORD in the Monthly Table (having one entry per month). Do your comparisons (or reports) by joining your Hot table and the Monthly table any time you want, for some intermittent activity maybe.

The "starting strength" would always be available in the Monthly Table going back as far as you want, even if you repeated your update, because you would be appending, not overwriting. How you use your dating options would give you flexibility in summoning specific periods of time.

So your calculations prior to closing would occur on the Hot table (with some "Form" automation) so that each time you add some data the strength is modified. During the close-out, you would require a separate query for carrying over whatever stats you want to the Monthly table.

So my concept is: 1 Form, 2 tables, 2 - 3 Querys (calculations on 1tbl, update to 2nd tbl, and a report query for whenever). I know
NeoPa is looking at the broader picture, so I may be missing some significant detail here. I just wanted to chime in and perhaps promote some further thought.

Also, getting some nitty-gritty on the calculations (all) would absolutely take away some mistery and probably simply the solution. OK?

[NeoPa]

I would be cautious of a 'separate table' approach. I believe everything can be done from the existing table, and separating out any of that information would detract from the Normalised nature of the database itself (duplication of existing data).

The solutions are both clear, but so far the question is still to be made so (No criticism implied - Lisa has been good at responding to questions so far). It only remains to know which approach is relevant to the question. If SQL is required then that's straightforward enough. If a form is required that is likewise straightforward. I'd like to know which approach is required though, before putting together the details.

[C CSR]

In compromising, I may be too concerned about Lisa's timeframe for getting a draft ready. The duplication of "stats" into the 2nd table, you're right, is not necessary, although it would require less sophistication for generating reports, and I'm not sure what those might look like. I'd like to see the right package produced, so I am looking for the light.

One thing though-- She says she's already running calculations "in" the 1st table which sounds like it is turning raw data into final statistics (but that's not real clear), and I think you are telling us to avoid that and instead only form results as output is required. Now that's what we can't do without understanding her calculations and the difference between what her raw input is versus any results that's already being set via her calculations in the table. If she just puts in the data, the queries can be properly constructed.

I would be considering using only Dates to pull up a static timeframe instead of storing the "starting strength" somewhere, if she's not "technically" overwriting past input, but then there would be some extra calculations for handling that (dates and running gains & losses).

I'll look in later :)