Table structure - combine or keep separate

[Allie12380]

We have a database that has data loaded periodically. Daily incoming inventory is updated in it and monthly we import financial sales data from our POS system. For the monthly POS data, we run 8 reports in POS that are either CSV or Excel. We have leading zero's in this dataset that must be maintained. So the csv files are loaded and transformed in Excel. See attached for a view of the relationships and tables relevant to this question. There are numerous other relationships not shown which mainly connect the tables with "detail" in the name to other data. For these tables, there are individual queries set up that combine the header and detail table. The purpose of this is to associate the month end date with the imported row data. Next queries connect information from various tables to present a complete dataset. For example, tblR12A_Sales_Detail includes the product num (barcode) and product description but not vendor or product department which is in the tblR3A_Product_List_Detail. Further, we have queries that pull data to create basic financial data such as total tender (funds collected), total tax, total sales income (actual price item sold for) and cost goods sold. These financial queries are connected to PowerBI and we display data on SharePoint for our teams.



In the New Year, we will be moving from Access for tables/queries to SQL Server for tables/queries. Access will remain the frontend for users to interact with the data. Access frontend and SQL backend. Hope is this will help with speed. The current Access is on share drive with multiple users. We cannot use the migration tool so it will be a lot of rebuilding. As a result, I have been considering ways to (1) streamline database (2) improve user functionality and (3) improve reporting and analytics. Trying to think through this all before start the rebuild of tables in SQL after the New Year.

So my questions:
- Is the header and detail table set up the best option? If so, any ideas to improve the existing?
- Any ideas on a better way to structure the tables?
- Any ideas on a better way to associate the import date and month end date with the detailed row data?

GS Database doc file.doc

Thanks

[Welshgasman]

Not even going to try and read that huge block of text.
Generally it is easier combine, than split.

[RayMilhon]

I've found it's faster to put the front end on the client so everyone has their own front end and they all access the back end. If I'm reading your message correctly it sounds like you have both on the server and everyone is accessing the same front end. That'll slow it down big time regardless of the backend being in SQL Server or access. The downside is that if you make any changes to the front end you'll have to migrate it to each user's machine. I think that you can push it out to each user's machine when they login, but I don't have those rights on our network so i've never done it.

[Welshgasman]

Each user should have trheir own separate FE.

[June7]

You are asking if some tables should be combined?

I am confused by some aspects of relationships diagram.

Why are there no links between entities, such as product and sales, etc?

Why is data (such as Product_Classification) duplicated between tables?