A Question Of Design (1 Viewer)

LGDGlen

Member
Local time
Today, 16:59
Joined
Jun 29, 2021
Messages
229
Hi All

I have migrated my company from a spreadsheet to an access database to run the day to day business by sanitising, normalising and then modularising(sic) the data into a more logical format.

We used to run things from a sheet that had 4000+ rows of 270+ columns the majority of which are calculated fields. I have created forms to allow a more logical method of entering things and have linked things together so there is no repeating information. Everything from a data entry perspective has come together and the users are getting the data in as they are used to but in a more streamlined manner.

So all good so far and I am happy with data entry. The problem I have is the way in which the users interact with the database and find things.

The users are used to, and I have recreated, a view of the seasons records as all the rows that they can filter to find specific things. So recreating that I have a continuous form that shows the consignment details. This will mean that by the end of the season there might be upwards of 1000+ rows in that table being displayed on the continuous form.

This - to me - means that I can't push the back end online hosted somewhere so that the front ends can be anywhere and access the data instead of being local to our LAN as when we scroll through the data it has to retrieve all records every time we scroll up or down.

Here is an example of what the main front end looks like:

1634895569641.png


So the users - at this time - are conditioned and used to being able to see the whole seasons records that they can drill down into when required for accounting purposes.

So whats the purpose of this post.

As this is my first database I'm pretty sure I've fallen into the trap of looking at things from the perspective of what is currently being used - in this case Excel - and recreating it in Access instead of redesigning things to work better for Access not make Access work like Excel.

So I want to work on Version 2 of this to migrate the users from this method of looking at the data to a more "database" way of doing things but I'm not sure where to begin to educate the users as well as myself so I am looking for some advice on a better way of give the flexibility of being able to see the data but reducing the interaction with the tables in the back end to the minimum to reduce traffic so I can migrate the backend to a hosted solution.

I have split things to a Front End/Back End setup so the back end can be anywhere and in any format as long as i can access it so i tested things out with a mysql backend hosted on the internet and the Front End shown above was unusable.

I am sure there so many tricks and tips to maybe reduce the overhead on the transactions between the front and back end, currently the front end is not editable the users double click on a record to take them to a form so maybe on start up there is a query that pulls the data once and maybe gets updates when the user creates a new record locally or at regular intervals but not sure i'm just clutching at straws here

Really this is a request for some pointers/tips/tricks/links etc that i can review and see what i can implement that would work for what i have with out being too radical initially and slowly build things out so they work better, evolution more than revolution right now

Hope this all makes sense but if not let me know what i can explain more

Kind regards

Glen
 

LGDGlen

Member
Local time
Today, 16:59
Joined
Jun 29, 2021
Messages
229
@jdraw thank you i'll take a look
 

LGDGlen

Member
Local time
Today, 16:59
Joined
Jun 29, 2021
Messages
229
i'll continue to dig in as it looks interesting and i'd like to understand it more but i'm not sure its what i am looking for as the main "excel" like view is only 1 table of normalised data. its not bringing multiple tables together more it is improving the way the data from the 1 table is displayed to reduce interaction with the backend with the potential of up to 1000+ records to be displayed
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:59
Joined
Jan 23, 2006
Messages
15,364
Well Pat Hartman is a regular here and may be able to offer advice on extending or adjusting the general approach. If you have questions, I'm sure she'll respond.
You may get other responses to your question on design. But we don't know your business processes and workflow in any detail.
It's rare to see someone using/intending their first database as a primary tool for their business.

Good luck.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:59
Joined
Feb 19, 2002
Messages
42,971
What you are looking for is more of a custom search form. I'm busy now so can't go digging but I'm sure others have posted examples. If I only need one or two search textboxes or combos, I just add them to the main form but if I have a lot of fields and i waht to build a custom WHERE clause, I use a separate search form. The serch form builds the query and then does a dcount(). If there is only a single record returned, the code opens the normal edit form. If multiple records are selected, it opens a list type form with minimal data. That form then can be filtered if necessary since it is a subform in DS view. Then in the dbl-click event of one of the fields, is code to open the normal edit form.
 

LGDGlen

Member
Local time
Today, 16:59
Joined
Jun 29, 2021
Messages
229
@Pat Hartman i have kind of ended up doing something similar but have come in from a different point of view based on the way in which the users currently work, in that the search form has the comboboxes and date text boxes which allows the users to reduce what they see from the main list down to the things they want. The problem i think is that from where i'm at with this is that i need to slowly migrate the users way of thinking about the data and how they interact with it to get them to a place where they aren't reliant on "seeing everything and filtering to what they want" and moving to a "knowing what you want to see and searching for it" and giving them the tools and training to do this.

what you describe with the search and return only what is searched for or opening a form if only 1 item is returned is where i'm sure i need to be, or something similar, i think what i need to do is talk to the users about this and explain that whilst making it look like the excel sheet they are used to works for them it very much doesn't work for Access

quick question about datasheet sub form vs continuous form, looking at the example i screenshotted in the OP is there a way to
  1. remove the headers of a datasheet sub form
  2. make sure the columns line up with the headers in the main form
And if so is a datasheet view less transactional with the back end (i'm saying words there that make sense to me but might not be the right terms but just trying to say the amount of interaction with the back end is reduced) than scrolling up and down a continuous form.

I like the aesthetic of the continuous form but if its something i can recreate with a datasheet view sub form and that will work better then i'm happy to migrate to that as a first step and then continue on the transition to only show data in the sub form once the user has done a search

hope this is all making sense, kind of monday morning before coffee rambling at the moment but just trying to get down on paper whats in my head before i forget

thanks in advance

Glen
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:59
Joined
Feb 19, 2002
Messages
42,971
Here's two pictures that might give you some ideas. The intermediate list is a subform in DS view. It is not updateable (you really don't want your validation code in multiple places). But it can be filtered further. The EMS number (first column) is formatted to look like a hyperlinkso the user will click on it and it will open the edit form which shows all the data.
ClientSearch.JPG
ClientSearchList.JPG
 

Users who are viewing this thread

Top Bottom