Can it be done in MS Access, if yes, how?
A matrix like data entry form with dropdown lists both at the rows and columns as per the attached image
TIA
Can it be done in MS Access, if yes, how?
A matrix like data entry form with dropdown lists both at the rows and columns as per the attached image
TIA
https://www.youtube.com/watch?v=ySyM...F8AS8T7ppKvm9O
It can defiantly be done. To start, I'm sure someone will say this at some point. Define the nature of your business and the requirements of the database as clearly as possible. Have that written down, it will help. Then work through examples.
@Homegrownandy, thank you for the reply. I may have exaggerated by mentioning matrix, but the requirement is as given in the attached image. The main conundrum is to have drop down select lists (#4 and #5) both at the rows and columns with multiple instances.
As Andy said, Access can handle this easily.
"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.
I see at least 5 table from your image. Tables created like the Excel design will be very difficult to use....... Remember: Access is NOT Excel.
Use pencil & paper, whiteboard, cardboard, sticky notes, etc to do the design before jumping into Access to create tables.
After drawing your tables, if you want a review of the structures, post back with a picture of the design.
Here is a lot of reading before you get started:
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
Tutorials at Roger's Access Library
http://www.rogersaccesslibrary.com/forum/forum46.html
Actually take the time to work through the tutorials - it will help you a lot.
Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.
It isn't clear to me if the "matrix" type of form is the requirement, or some "processes and manipulation on data".
You might want to review this thread in this forum for "similar subject matter".
For spreadsheet like forms see this post/article and database sample by Pat Hartman
Good luck.
Here's a thought: it would be easy enough to pull the data together in the "matrix" format that OP wants using a cross query. What about a datasheet form that uses the query as it's data source and to change the resistant/sensitive value would simply be a matter of a little vba on the onclick event of the cell? Assuming only 2 or 3 possible values you would just click them on and off.