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:
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
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:
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