Display and edit data in matrix format

[runnerbean]

Hi there,

I have a table with three columns, "a", "b" and "c". The combination of "a"
and "b" is the primary key - i.e. this combination of values is unique. I
need to display the data in a matrix, where values of "a" are the row
headings, values of "b" are the column headings, and values of "c" appear in
the matrix, like this:

a a a a
b c c c c
b c c c c
b c c c c
b c c c c


This is a bit like a pivot table, but there is no grouping or aggregation of
data. I also need to be able to edit values of "c", and update the
underlying table.

Please could some kind person tell me whether it is possible to do this in
Access? I am using Access 2000 at the moment, but could easily change to
Access 2003 if this would help.

Thank you very much.

runnerbean

 

[Douglas J. Steele]

While you presumably could create a table with field names corresponding to
a, to do so would violate database normalization principles.

I suppose you could dump the data into a temporary table, do your edits,
then put it back into the properly normalized table. Another possibility, if
you've got a fixed number of values for a and b would be to use an unbound
form, figuring out what values of a and b correspond to each text box c and
using SQL to update the table.

Is it really essential that you keep that format?

 

[runnerbean]

Thank you for your very speedy reply!

I have a fixed number of columns, but a variable number of rows.

Unfortunately it is essential to keep this format. My brief is to replicate
the functionality currently provided by a spreadsheet, and keep the
spreadsheet-type presentation of the data for part of the application. This
makes sense from the users' point of view because there are many many
records, and it is intuitive to think about them as a "table".

One possibility that crossed my mind it to tray and embed an Excel window in
the application and get the best of both worlds: the presentation of the
data as a matrix where required (from Excel), and the advantages of
normalization, extensibility etc. (from Access).

I'd be most grateful for your thoughts on the best approach to this, and
maybe a pointer to an appropriate web resource/sample code if you are aware
of anything.

rb

 

[Douglas J. Steele]

Trying to embed Excel into Access would actually be a fair bit of work (if
in fact it's even possible: you might have to get a 3rd party ActiveX
control to be able to do it). Easier would be to export the data to Excel,
open Excel separately, let your users make their changes and then reimport
the data to Access.

Putting the data into a temporary table for update purposes and then
transforming it back to the proper format for storage purposes (keeping
everything in Access) might actually be easier. You could use a cross-tab
query to populate the temporary table, and then a series of queries to store
the data "properly" again. (If you're going to do this, consider using a
temporary database for the temporary table to minimize the bloat of your
front-end. Tony Toews has an example of this at
http://www.granite.ab.ca/access/temptables.htm)

 

[Peter Hibbs]

RunnerBean

A Flex Grid Control will probably do what you want. Have a look at -

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

for some examples.

Peter Hibbs.


You can find more info on the Flex Grid control (as well as other
Active X Controls) at :-
http://msdn2.microsoft.com/en-us/library/aa338166(vs.60).aspx

 

[runnerbean]

Peter and Douglas,

Thank you both very much for this. It will take me a few days to figure out
how to use the suggested solutions, but just wanted to acknowledge your time
and effort in answering my question in the meantime.

rb