I need to create some reports using Sage data tables. I am quite confidente producing reports and normalizing but never before worked with linked tables and as this is my first major project, I am really learning as i go along. I started by producing a database analysis and design report for my employer with all the tables that they would need to carry out business processes. They currently use Sage. For the time being we are keeping the current software and trying to make amendements.
I am using at work Access to create the reports by linking the tables from Sage then appending them to new ones to perform queries.
Well if anyone ever used Sage noticed that the tables are anything but normalized.
My question is, should I normalize the the tables that I appended and create the relationships as i already have a good grasp of where the data is or should i just perform the queries on the data without adding any relationships and normalizing. The Access database is also used for some order processing that cannot be carried out in Sage.
I am at an impass and want to move on with the work, but am thinking every option without a clear solution.

I would suggest doing a clean and simple link to the tables and leave it at that. Sounds like you're thinking of setting up a mirror set of data and are going to try and normalize those - ? That's going going to be a nightmare to maintain. See if you can crunch the data with simple links first. You may have to have several queries running one on top of the other to get the data the way you need it but I think thats a WHOLE lot better than trying to mirror the data - Just my opinion. It will be interesting to get other views on the topic...

Thanks for the reply, problem is when we need to create an order processing on access to be sent back into Sage, it will still be a nightmare.

I'm not familiar with Sage, is it an enterprise type solution?

Yes mostly, but I found it very bad, their report designer does not allow much flexibility. It is aimed to small companies, but it does not allow much customization. So most times companies have to improvise and remove the data into another application for reporting, or resource to other tools in the market.

I see. Then main problem as I see it is that if you bring the data into a secondary system you'll be working with 'snapshot' data. Meaning the data isn't 'live'. So every time you need to do something with current data you'll have to go through the entire 'normalizing' process. ugh :(

I tried linking it directly, but the ODBC database does not accept every query comand and it tends to crash too easely. Oh well will have to refresh the data regularly then and just link the data. Might take a few more queries than expected.:(

When you get linked just use a simple select. Then if you need to do complex stuff build it using the first query instead of the original table - ?

Right I'll give it a try it and get back to you, thanks :)

Sage is well known for its byzantine table structure! And flakey ODBC drivers!