Pivot table entry forms in Access 2003(or 2007) and performance is

  • Thread starter Thread starter Access Noob
  • Start date Start date
A

Access Noob

Given that I have extensive Excel background & virtually no DB training,
these may be stupid questions, but here goes.

Is there any easy way to replicate a spreadsheet entry format into Access?

I have an Excel capital allocation model that has long outlived it's
original purpose and useage is much greater than originally proposed ... so
it needs to be converted to some sort of database solution due to the storage
problems associated with multiple model copies. The Excel version has dates
across columns & projects down rows with row & column totals provided for the
user.

When I try this in Access I can get reports to replicate the presentation,
but I cant change values in forms. I'm shooting for a single product
approach, since I don't know the hardware capabilities or tech computer of
all the potential users, & don't want to run into memory & pagefile problems
that might arise from opening Access & Excel simultaneously while Outlook &
webbrowsers are running on the desktop.

I'm having enough problems on their machines with the performance of the
queries across their network. The Excel model with it's limited database
features is lightning quick & the Access queries take forever. Is this
something better handled by SQL? By combining Excel and Access?
 
A database and spreadsheet are vastly different creatures. For example
putting dates across columns, like in your spreadsheet, in a database would
be dead wrong. Improperly designed and normalized tables can cause slow
database queries.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
 
Back
Top