Access newbie here with what's probably a complicated question. I've
been tasked with developing a simple budgeting application using MS
Access for my UI. I'm not an Access developer by trade but I'm the
only resource available. Currently I have a SQL backend with all the
tables linked into Access. I've written a few queries that will start
to handle the user's needs.
The main form of the app will be a replica of an income statement. The
layout needs to look like a matrix (the sample was provided on an Excel
sheet) with accounts running down the side (vertical) and time across
the top. The source data for this is a partially normalized table with
accounts, time, and values on each row for multiple locations. Using
ODBC to connect the spreadsheet to the database is not an option :(
The question is really for some suggestions on how to populate the
form. So far I've created a form based on a temp table that contains
data for the location the user is currently working on.
I'm using bound textboxes for the account values and unbound textboxes
for the subtotals. The control source for the unbound boxes is an
expression that sums the value in the boxes above it. So far all this
works.
My problem is going across time. In my current design, the user
advances the record selector to see the next time period and associated
values. The user wants to have all time periods displayed, as they
were in the spreadhsheet. What are some ways to approach this?
What I've come up with so far is using multiple queries with one
representing each time period. Then each 'column' of data would have
textboxes for the time period at the top and the accounts and subtotals
below. To populate the accounts, I would use a SQL statement that
references the filters plus the time period in the column. The
subtotals I would handle the same as before.
Will this work? Are there alternate approaches that might work better?
Thanks in advance for any assistance.
