killerflappy
Registered User.
- Local time
- Today, 17:42
- Joined
- Aug 23, 2017
- Messages
- 50
Hi,
I have a complicated Access file with a lot of tables, query’s and VBA.
It works roughly like a Warehouse Management System.
The Access file is used to import orders from a couple of customers and then use barcodes scanners to collect this orders.
In the Access file the scans (for invoicing) are stored in 3 different tables:
Scans of the day
Scans of the day that can’t be deliverd (for instance if a part of the order can’t fit in the truck)
History of scans of the day and Scans of the day that can’t be delivered.
When a new day is created the scans of the day are stored in the history table for scans.
The records of these tables are also added to a MariaDB/MySQL server every morning before a new scan day. Access holds 50 days of data. MariaDB/SQL is holding all the data.
Access has some ODBC connections to MariaDB/SQL for searching history.
The scans of the day and scans of the day that can’t be delivered tables is overwrited every day on the server.
Now I want to make a better invoicing system. It’s easy for me to get the amounts per customer per product of the scans in a crosstable-query.
But the administration-department want to add/remove/edit records of the scans and history.
They want to be able to make changes every day. Also for the table of the scans of the day.
At the end of the week they can make the invoices.
To make changes in the original data is creaping me out, because the original scans are made in the warehouse and the editing will be done by the administration-department.
I reather don’t want the administration-department use the same Access file. There must be some segregation of duties.
Also adding records can cause problems with autonumbers with synchronization and making history.
I need some ideas to get an easy way to implement the requirements of the administration-department.
I have a complicated Access file with a lot of tables, query’s and VBA.
It works roughly like a Warehouse Management System.
The Access file is used to import orders from a couple of customers and then use barcodes scanners to collect this orders.
In the Access file the scans (for invoicing) are stored in 3 different tables:
Scans of the day
Scans of the day that can’t be deliverd (for instance if a part of the order can’t fit in the truck)
History of scans of the day and Scans of the day that can’t be delivered.
When a new day is created the scans of the day are stored in the history table for scans.
The records of these tables are also added to a MariaDB/MySQL server every morning before a new scan day. Access holds 50 days of data. MariaDB/SQL is holding all the data.
Access has some ODBC connections to MariaDB/SQL for searching history.
The scans of the day and scans of the day that can’t be delivered tables is overwrited every day on the server.
Now I want to make a better invoicing system. It’s easy for me to get the amounts per customer per product of the scans in a crosstable-query.
But the administration-department want to add/remove/edit records of the scans and history.
They want to be able to make changes every day. Also for the table of the scans of the day.
At the end of the week they can make the invoices.
To make changes in the original data is creaping me out, because the original scans are made in the warehouse and the editing will be done by the administration-department.
I reather don’t want the administration-department use the same Access file. There must be some segregation of duties.
Also adding records can cause problems with autonumbers with synchronization and making history.
I need some ideas to get an easy way to implement the requirements of the administration-department.
