Create Editable cross-tab type form

I want to create a table that has a structure similar to the following:
Month    Type     Value1    Value2     Value3
Jan         001     1000        3000        2000
Feb        001     1500        3500        2500

However I would like to be able to EDIT the date in the following format in subforms by Type

Type   001
                Jan        Feb    .....     Dec
Value1    1000     1500     ......  
Value2    3000     3500    .......
Value3   2000      2500

I think I can display the data using a crosstab query in a form but that renders it non-editable. Is there any way for a beginner to accomplish what I want to?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi rprineppi,

I doubt if even a non-beginner would attempt this without a lot of persuasion.

Can you explain why you want your table layout to be different to your form layout?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rprineppiAuthor Commented:
Hi Pete,

The sample I gave above is a simplified view of what I really need. If I structure the table the way I want it displayed and edited I will end up with literally dozens of fields. I know that the best way to create a table is to have as few fields as possible and that's what my proposed structure would accomplish. However your comments confirm what I thought would be the problem with my proposed structure and so I think I will persevere with my multi variable structure. Of course that presents me with other problems down the track but I will leave them till another date.

Thanks for your answer.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In addition to Pete's comment, your table design appears to be noon-normalized ... anytime I see column names like Value1, Value2, etc you can be pretty sure that the database isn't built correctly.

Crosstabs are always non-editable (since they're really designed for reports). Only way to have an editable crosstab form would be to do the data updates yourself in the various AfterUPdate events ... the form would have to be unbound, and you'd have to fill the data yourself.

Additionally, this may be more suited for Excel than Access ....
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

"persevere with my multi variable structure"  NO, do not make a table with columns that match what you showed in your question.  As you correctly note, such a structure will lead to HORRIBLE problems down the road.  Perhasp you might want to re-think the design of the application.

What is the nature of the application, and what are you trying to accomplish with this particular form?  There are MANY Decades of experience with almost any application you can think of represented by the Experts in this TA, and we have all encountered almost any problem you can think of (and many you haven't begun to think of), and worked out solutions for ALL of them.  If you tell use what you want to do, I'm 100% certain that several of the Experts here have encountered and worked out EXACTLY the same problem, and we will be gald to share our experience with you.

rprineppiAuthor Commented:
HI LSMConsulting,
Thanks for your additional comments. It seems that my original mult-variable approach may be the easiest one for me to find to handle this problem. It is an application that is currently running on Excel but there are even more limitations there with some of the other things it needs to do.

Hi Arthur Wood,
Thanks for your comments too. I have no doubt about the exceptional skill of the experts on this exchange and the large assortment of problems that they have addressed. My concern is that my application appears to be quite complex and my skill level very low. I wanted to use this problem as a means for me to learn Access while I overcome the limitations of the current Excel based system I am using.

I think I will persevere with my multi variable structure for the time being and see where it leads me. Hopefully I will learn a lot about Access in the process and perhaps later convert to a simpler database structure when I have more skill.


rprineppiAuthor Commented:
In case any of you are still subscribed to this question, I have spent the last week thinking about my structure and I am now convinced that it is in my best long term interests to take your advice and change the structure in line with your comments. I have simplified my requirements as much as possible but still cannot get into my head how to best set up the strucrure so I will be posting another question.

I just wanted to thank you again for forcing me to re-think.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.