Excelesque Data Entry and Totals in a Normalized Database?

[kagard]

Greetings:

I'm setting up an access 02 db for someone familiar with Excel. They
had each year's expenses in separate Excel workbooks and each month's
data separate columns. I've normalized the data so that it has:

Account, Fiscal Year, Month, Amount

Which works great for the reports they want. But they want to do data
entry in a form that has the months as column headings (fiscal year
begins with April) Arp - Mar, and row headings for the various expense
accounts. They want to see the whole fiscal year as the enter data.
They also want to total across the rows and down the columns. This
would be easy in Excel, and I could create a report to SHOW them what
they want, but also allowing for data entry on the same form has me
puzzled.

I've suggested a separate data entry form for all the accounts for a
given month, but that has been rejected. The only thing I can think of
is to denormalize the data, going back to one column for each month of
the fiscal year. Is there another way to approach this?

TIA
Keith

 

[aaron_kempf]

you just need to take your existing data and run the crosstab query
wizad, don't you ?

With SQL Server; you can just use the Pivot keyword-- this is a lot
more powerful that crosstabs; and of course, SQL has an unpivot
command-- so that if you _DID_ store/enter the data as repeating
columns-- it would be easy to normalize is (virtually) using the
UnPivot command

hth

-Aaron

 

[kagard]

Hi Aaron:

Thanks for your reply. I didn't think you could do data entry in a
crosstab query result set. The value at the intersection of a row and
column is the result of a calculation.

Keith

 

[Wayne-I-M]

I'm sorry to say that Arron is wrong in this case. It sounds to me as if the
best programme for this would in fact be excel. I may have misunderstanding
you post but it seems to me that excel has all you need for your project. It
may be that there is another reason you need to store the data in access that
i have missed.

Note to Arron.
I though we had gone throught this last night. Your answer to Keith was
niether polite, helpfull or informative. Don't forget that you don't "have"
to answer if you feel upset by the question. )

 

[kagard]

Hi Wayne:

I realize that, in the case I described, I'm trying to make Access
behave like Excel, but it's part of a larger project and not my call
so I need to find a way to work it out. Thanks for your reply.

Keith

 

[Wayne-I-M]

Sorry I can't be any more help. I do have some excel files that are also
part of a larger project but I just import them OnClick as and when needed.

I think someone else with more skillfullness will answer - I hope

 

[BruceM]

The expense accounts are the rows in the spreadsheet version? If so, does
that mean each account is a field in the database? If so, is there an entry
for all accounts each month? It seems to me this structure, if it is what
you have, would create problems if a new account is added. I realize it's
not your call; this is just an observation.
Anyhow, where I'm going with this is that one blunt-force approach may be to
have a collection of subforms side-by-side.

Hi Wayne:

I realize that, in the case I described, I'm trying to make Access
behave like Excel, but it's part of a larger project and not my call
so I need to find a way to work it out. Thanks for your reply.

Keith

 

[John W. Vinson]

Hi Wayne:

I realize that, in the case I described, I'm trying to make Access
behave like Excel, but it's part of a larger project and not my call
so I need to find a way to work it out. Thanks for your reply.

Well, a variation on Aaron's suggestion may be the best of a bad situation
then. You could have a normalized table AND a separate non-normalized
wide-flat table, with Queries to move data from one to the other. A
"Normalizing Union Query" could move data from the spreadsheet into the table,
and a Crosstab the other way. Will these users be just entering new records
(pretty easy) or selectively editing existing ones (a bit more complex)?

 

[Michel Walsh]

Mr. Aaron, no: At least, not with MS SQL Server 2005 PIVOT clause which does
not have the concept of GROUP, so the aggregate is totally dumb and you need
to use the whole PIVOT syntax as a sub-query to pump anything useful from
it. In fact, a CUBE or a ROLLUP could probably deliver the data (SUM), even
if the disposition of that data is not rectangular, and will work with
previous MS SQL Server versions.

Mr. Aaron, no: Jet Crosstabs are not updateable. Using a temporary table to
dump the data from the crosstab make the crosstab useless, since even if the
data become updateable, the SUMs won't be automatically updated.


Keith, using a form and an unbound grid (a real grid, or your own text box
controls disposed into a rectangular manner) into which you push the
relevant data (through a VBA loop), accept edit (and update the sum after
update of each control) and end, again through VBA, pull back the data into
the normalized table seems, to me, the most straight forward method of doing
it (if using Excel is not possible), given that the end user must not have
the possibility to add new line or new column (isn't it ? ). Lot of code,
well, more than the usual amount of code, but that is the less
circumvolutory way I see.


Note that while you cannot have a control array, you can create an array of
control, which can help a little bit, to maintain your summations, being
easier to loop on index than using explicit names each time, like:
TextBox1+TextBox13+ .... .


Hoping it may help,
Vanderghast, Access MVP



you just need to take your existing data and run the crosstab query
wizad, don't you ?

With SQL Server; you can just use the Pivot keyword-- this is a lot
more powerful that crosstabs; and of course, SQL has an unpivot
command-- so that if you _DID_ store/enter the data as repeating
columns-- it would be easy to normalize is (virtually) using the
UnPivot command

hth

-Aaron

 

[Peter Hibbs]

Keith,

A Flex Grid Control will probably do what you want but you will have
to write some VBA code. A Flex grid control is not normally editable
but you can create a grid which is (see example below).

Have a look at -

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

for some examples.

Peter Hibbs.