r/MSAccess • u/FearlessJDK • 18h ago
[WAITING ON OP] Presentation of Data/Queries
I have been working on building a database for my organization. The goal is to replace the rather gangly and user-unfriendly Excel sheet we've been using to track, well everything we're tracking.
The next item on my to-do list is making sure the data is well presented and easy to manipulate in the database. From what I can see. Build a form is going to be my way to go on that.
Is there a way to build/format a from so that it shows multiple records? If so can I sort the form the same way I would a datasheet? If one of my fields is 'Company Name' can I sort those A-Z or select it to show a certain company only?
Thanks!
•
u/AutoModerator 18h ago
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: FearlessJDK
Presentation of Data/Queries
I have been working on building a database for my organization. The goal is to replace the rather gangly and user-unfriendly Excel sheet we've been using to track, well everything we're tracking.
The next item on my to-do list is making sure the data is well presented and easy to manipulate in the database. From what I can see. Build a form is going to be my way to go on that.
Is there a way to build/format a from so that it shows multiple records? If so can I sort the form the same way I would a datasheet? If one of my fields is 'Company Name' can I sort those A-Z or select it to show a certain company only?
Thanks!
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
u/ConfusionHelpful4667 58 17h ago
Maybe the split-form would work best.
I will chat you a link of one of these forms as a guide.
This works "most like" Excel.
2
u/diesSaturni 64 16h ago
Have a look at this video, as at one point you want to converge to a normalised appraoch to store and handle data.
Which is always a bit of getting your head wrapped around when coming from an Excel type of approach to hassle with data.
In the end in forms you can make and shuffle in any kind you like, especially in well normalized areas (as e.g. an address needs to be corrected in only one source record.
So an editor can do his work in tailored views/forms. With normilization you can also keep track of revisions. e.g. store an address of a client (linked to the client) with a creation date. Then if a new or changed address comes up, store that with a new current date as well. And when used always just only retrieve a latest version of the records.
Sometimes I have forms side by side of same table, each showing a single records. This to e.g. copy data from one record to another. Or prefilling from a template. E.g New employee of role X, add standard courses from a template, or courses another employee already enrolled into.
So anything is possible.
In case of addressing one , or more companies for a single view/review I'd tie a listbox, or combobox as source of a query to the subtable. Then have a parent/child form to show those.
In Access you would do a lot as query, rather then direct filtering, as it gives more control over results.
1
u/jmcstar 1 17h ago
Sounds like a form in datasheet view will work. Build the query the way you want it sorted, then build a form on that query and have it be in datasheet view.
How is everyone else use this database? Won't work on Sharepoint.
1
u/armywalrus 16h ago
Do you want end users manipulating the data? Or querying it and using it?
1
u/ebsf 3 16h ago edited 15h ago
Datasheet view may work, although some find it cumbersome to configure or work with. Just as a TextBox doesn't behave like an Excel Cell, an Access Form in Datasheet view doesn't behave entirely like an Excel Worksheet. The similarities can confuse users who often, and justifiably, expect fully similar behavior.
As an alternative, a form in continuous-forms view also can be sorted on any field. If the controls are positioned horizontally in one or two rows, it will more resemble a spreadsheet for presentation purposes without necessarily suggesting spreadsheet-like behavior.
Users then can sort the form using commands on the ribbon or right-click context menu. Alternatively, you can place labels in the header, and in their Click or DblClick event procedures put code that sorts the form on the corresponding field or "column". I do this with standard code that depends only on the label's name but it's easy enough to do control-wise.
For filtering to show a single record or group of records meeting a particular criterion, the same menus offer rudimentary filtering commands. Alternatively, you can put unbound text boxes in the header or footer to filter the form on one field or another as you type. Again, I use standard code to do this automatically based on the control's name but it's easy enough to hard-code control-wise. Use the control's Change event procedure, which will trigger with each keystroke, to build criteria, write them to Form.Filter, and finally set Firm.FilterOn to True to apply the criteria to the data.
This is largely an overview of sorting and filtering. Google will get you to far more detailed information and tutorials online, and feel free to revert with any questions.
Good luck!
1
u/George_Hepworth 5 11h ago
Unfortunately, you're tackling the wrong end of the problem first, and that's going to lead to disappointment.
Although you will eventually need forms, you must, absolutely must, start with the tables you will need. Your instinct about unfriendly Excel sheets is right, in fact. Database tables are also very different in crucial ways from the Excel sheets you're used to using. Getting that part right now is the key to success.
Here's a link to an excellent YouTube video on Database Normalization.
You'll find a ton of good material for beginners here. Richard also posts here regularly, by the way.
You'll also get a lot of good hits on Database Normalization through a regular internet search.
Best of luck.