Data entry for cross tab reports

[MorningSun]

I am trying to design a budget database and found on this website a link to sample databases by dhookum. I think the cross tab method will work perfectly except I don't know how to get the data IN the database. Here's what I'm trying to do.

Budget by month for approximate 40 accounts. I'll need the following fields

Account #
JanuaryPlanned
JanuaryActual
FebuaryPlanned
FebruaryActual
Etc..thru December.

I currently have Account info in one table and the detail (janplan, janactual ....) in another. There are other tables with more descriptive data but the detail data is the important piece. This would be great except that for data entry, you can only enter the yearly data for one account at a time... I need to be able to have all account numbers show up and a place to put all monthly entries in.

I need for the report to look much like the crosstab example on dhookum's site with Jan-December at the top, Account down the left side and all corresponding entries in the grid....

How can I get from point A to B?

Thanks!



Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[dhookom]

Your table structure should not include fields for each month. A more normalized structure would have fields like:
[tt][blue]
AccountNumber
BudgetYear
BudgetMth
ActOrPlan
BudgetAmt[/blue][/tt]


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[MorningSun]

I'm currently exploring that but I just can't figure out how to get the user to be able to pull up a data entry form that gives them a format such as:

Jan Feb March Apr
Acct1
Acct2
Acct3
etc...

As it stands in order to be normalized, they'd have to make too many selections when I want the form to be simple for them to see what is being required of them.

Accounts and Months already on the form with blanks to fill in.

It may not even be possible.
Thanks


Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[dhookom]

Everything is possible in Access ;-). I would use an unbound form or a temporary table for the data entry. Then run code or queries to update your normalized tables.

User interface should never determine table structures.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]