Assigning values to dates

S

SteveS

Hello,

Am fairly new to Access and this is puzzling me. I have a form (obviously
tied to a table) that asks for several inputs in fields:

- Month 1
- Amount 1
- Month 2
- Amount 2
etc...

How do I tie Amount 1 with Month 1, Amount 2 with Month 2, etc. so that when
I get to the point of charting the results the correct amounts go into the
correct months? Am I missing something basic, or not thinking about this the
right way...?

Thank you!
 
S

Steve

<< Am I missing something basic >>

Yes!! Your table is not designed correctly. Assuming you have one or more
Amounts each month, you need:
TblTransaction
TransactionID
TransactionDate
etc

TblTransactionDetail
TransactionDetailID
TransactionID
TransactionAmount

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
 
J

John W. Vinson

Hello,

Am fairly new to Access and this is puzzling me. I have a form (obviously
tied to a table) that asks for several inputs in fields:

- Month 1
- Amount 1
- Month 2
- Amount 2
etc...

How do I tie Amount 1 with Month 1, Amount 2 with Month 2, etc. so that when
I get to the point of charting the results the correct amounts go into the
correct months? Am I missing something basic, or not thinking about this the
right way...?

Thank you!
Click to expand...

Correct. You are missing something basic and not thinking about this in the
right way!

Your table comes FIRST. It sounds like you started with the form and worked
back to the table... a very common and reasonable error!

Your Table should *not* have repeating fields. Instead, it should have a month
field (not named Month, that's a reserved word), or perhaps better a Date/Time
field, and an Amount field. Rather than entering twelve months of data in
twelve (or twenty-four) fields, enter it in twelve *RECORDS* in a table.

This table may well be related one-to-many to some other table that is your
main form's recordsource. This would often use a Subform - the fields in
common to all twelve amounts on the mainform, and just the monthly data on the
subform. What type of information are you charting? What *other* fields do you
have?

John W. Vinson [MVP]
 
S

SteveS

Thank you John! (Sorry for the delayed response, but I needed to take the
long weekend off)

That makes sense, but I actually need to take this one step further. What
I'm ultimately trying to get to is to project financial ROI over a 5 year
period based on user-input (the dates and amounts I referenced below). Based
on user input, I would like to:

- calculate monthly revenue stream over a 5 year period
- total the results of each month for multiple activities (and this is why
it's crucial that the amounts be tied to specific months) over that 5 year
period

I have the formulas set up in a query to calculate the revenues (per single
record), but only by "Month 1, Month 2... etc" and haven't been able to
figure out how to tie the calculations to a specific month within the query.
However, like you said maybe I've been going about this a bit backwards. I
understand what you mean by the tables, but how would I extend that to the
query(ies)?

Thank you!
Steve
 
S

SteveS

Hi Steve,

Thank you for the response. Between the response from you and John I see
where my thinking (sorry, based on my Excel background) was off. However,
please see my response to John - how do I extend that design to accomplish
what I need using calculations within queries?

Thanks!
Steve
 
J

John W. Vinson

long weekend off)

That makes sense, but I actually need to take this one step further. What
I'm ultimately trying to get to is to project financial ROI over a 5 year
period based on user-input (the dates and amounts I referenced below). Based
on user input, I would like to:

- calculate monthly revenue stream over a 5 year period
- total the results of each month for multiple activities (and this is why
it's crucial that the amounts be tied to specific months) over that 5 year
period

I have the formulas set up in a query to calculate the revenues (per single
record), but only by "Month 1, Month 2... etc" and haven't been able to
figure out how to tie the calculations to a specific month within the query.
However, like you said maybe I've been going about this a bit backwards. I
understand what you mean by the tables, but how would I extend that to the
query(ies)?
Click to expand...

You DON'T want fields in your table for Month 1, Month 2.

That is spreadsheet logic!

Don't confuse data *presentation* - totals for each month over a date range,
say - with data *storage*.

Your data should be stored in a properly normalized table, with each revenue
"item" (whatever it is that you're totalling) stored in its own individual
record, with a Date/Time field indicating the date applicable to that item.

You can then create a wide variety of Totals queries or Crosstab queries to
sum the revenue values, group them by date, split them out into twelve monthly
columns, etc. - but you do not need to, nor should you, model your Table
structure after the eventual layout of the report.

John W. Vinson [MVP]
 
S

SteveS

Hi John,

Sorry - I guess I didn't make myself too clear in my last post, because I
shifted from talking about strictly tables to including queries. I understand
the logic behind laying out the tables as you described. However, now I'm
using queries to calculate the monthly cash flow based on user input (the
data they enter into the records). (So in effect, the monthly revenues and
costs are not stored in a table - they are calculated in queries to minimize
user input/manual calculations). I now need to figure out how to structure my
queries in such a way that I can aggregate the results by month across all
the records.

So, based on your recommendation on how to structure the tables, I've set up
a few test queries - each query represents one month during the year and I
have successfully calculated the correct costs and revenues to each single
month. (Note that the month itself is also calculated based on the timeframe
for the report the user enters in a separate form/table). My question now is
the best way to take those single month queries and sum each month into a
single query, which I could then use as the basis for a chart in a report.

I've just started looking at crosstab queries as a possible solution, and I
also need to look into temp tables though it's unclear to me know whether
people actually recommend using those or staying away... I hope this all
makes sense.

Any guidance is appreciated - thanks again for your help!
Steve
 
Top