By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,917 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,917 IT Pros & Developers. It's quick & easy.

Application and Form Design Suggestion

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Maybe you can use the Code Module "EditCrosstab" from my site somehow.
If you want to elaborate on it, be my guest. Mind my signature
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Nov 13 '05 #2

P: n/a
I think you might want to re-think the design a bit.

For example, I was required to show a persons name, check amount, and then
we need to also setup the accounts, and split out the cheque to each
account.

We also wanted a continues form. However, you can't have a sub-form in a
continuous form?

The trick is simply to use two sub-forms...side by side. There are several
examples in the follwing link. Tthe screen shot of a name, and amount being
split into many accounts is the last screen shot...

Those screen shots are here:

http://www.members.shaw.ca/AlbertKal...icles/Grid.htm
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.