J
james
yes
M
mromyn
Hi -
Sorry if this question is ridiculous. I don't think this is hard, I just
haven't done this before in Access.
I have two tables; one is a budget table, the other an expense table. The
budget table is set up with row of $$ per category in columns for each
month. (so the records are: Supplies, Equipment, $0, $300, $12 etc.... and
the Columns are: Category, Subcategory, January 09, Feburary 09, March 09
etc...)
The expense table lists each expense include the Cat, SubCat, Month and
dollar amount.
How do I join these in a report that would link the cat, SubCat and month
values so I have a running net total per month per category? Is there a way
to join the field value of a month with the column name of a month?
Any suggestions/examples would be fantastic!
Sorry if this question is ridiculous. I don't think this is hard, I just
haven't done this before in Access.
I have two tables; one is a budget table, the other an expense table. The
budget table is set up with row of $$ per category in columns for each
month. (so the records are: Supplies, Equipment, $0, $300, $12 etc.... and
the Columns are: Category, Subcategory, January 09, Feburary 09, March 09
etc...)
The expense table lists each expense include the Cat, SubCat, Month and
dollar amount.
How do I join these in a report that would link the cat, SubCat and month
values so I have a running net total per month per category? Is there a way
to join the field value of a month with the column name of a month?
Any suggestions/examples would be fantastic!
J
Jeff Boyce
Based on your description, you have ... a spreadsheet! When column names
have 'repeating values' (e.g., January 09, February 09, ...), you are trying
to make a relational database (Access) behave as if it were a spreadsheet
.... and you and Access both will end up frustrated!
Before you work any further on "how", consider looking into "relational" and
"normalization". You won't get (good or easy) use of Access'
relationally-oriented features/functions if you try to feed it 'sheet data.
Is there a reason you can't just use a spreadsheet?
Regards
Jeff Boyce
Microsoft Office/Access MVP
have 'repeating values' (e.g., January 09, February 09, ...), you are trying
to make a relational database (Access) behave as if it were a spreadsheet
.... and you and Access both will end up frustrated!
Before you work any further on "how", consider looking into "relational" and
"normalization". You won't get (good or easy) use of Access'
relationally-oriented features/functions if you try to feed it 'sheet data.
Is there a reason you can't just use a spreadsheet?
Regards
Jeff Boyce
Microsoft Office/Access MVP
M
mromyn
The budget data is coming frm a spreadsheet, but the expense data is in
Access. There will be several mamanger within the department accessing and
changing the information, so we want it in Access for traceability and
reporting.
Access. There will be several mamanger within the department accessing and
changing the information, so we want it in Access for traceability and
reporting.
H
hor vannara
mromyn said:
M
mromyn
There no reply 
J
Jeff Boyce
If you are going to get easy and good use of Access' features/functions,
you'll need to 'feed' it well-normalized relational data. Even if the
budget data comes from a spreadsheet, there's no good (in Access terms)
reason to leave it in that structure.
Consider taking the time to establish a well-normalized data structure for
your data, both "Access" data and "spreadsheet" data. Both you and Access
will find it much easier to process, trace, and report on...
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
you'll need to 'feed' it well-normalized relational data. Even if the
budget data comes from a spreadsheet, there's no good (in Access terms)
reason to leave it in that structure.
Consider taking the time to establish a well-normalized data structure for
your data, both "Access" data and "spreadsheet" data. Both you and Access
will find it much easier to process, trace, and report on...
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP