We use Access as the front end and SQL as the back end. I like to create the queries in SQL and then just create a basic query in Access which will have all the date filters based on a form selection.
So I get several excel source spreadsheets. Which I import into SQL Tables. In the attached spreadsheet it gives you and idea of the structure of each table and the data within. Basically a sales person can sell through the company (phones), through an online portal or via some third party software called Nexus.
The data I recieve comes in Monthly for the online portal, the company sales in one spreadsheet. The nexus information also comes monthly but separately.
Also each week I recieve the weekly sales via the nexus software.
So the only common link (for all sources) is the name. Also a sales person may have sales in the nexus software for a given month but no sales via the company or portal so they might not appear in that source.....so no link available. Also there may be entries in the Nexus weekly source but nothing in the monthly since we only get that information at the end of the month.
The other source is a reference table which indicates each sales persons region and manager.
So the report they want (last sheet of the attached) is a report which indicates the region and manager for each person, the last three weeks of Nexus sales and the last three months of sales activity for all possible channels.
This isnt straight forward right? I am not completely dumb....just very tired. But because of all these sources being almost completly independant from one another its the JOINS that are killing me. How can the sample report be accomplished?
(Keep in mind that I have really simplified it as the reference table has 2000 sales staff and on top of that they are spelt different across data sources so I have to create an alias table for the name field)
Forgive me but I am so tired and burnt out I cant think straight but hopefully someone at EE can get me the help/advice by tomorrow :)