Wedmore
asked on
Mixed Report with Weekly and Monthly Data
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 :)
Example.xls
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 :)
Example.xls
ASKER
I was always under the impression UNIONs should be avoided...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not really a specific reason. Just a rumour I heard you know - probably the worst way to hear things I guess.
I solved it in a completely different approach with separte queries on the selected date. So I had one query that pulled the dates for the three previous weeks and one query for the three previous months. Then I create queries for each table to join on their respective date query and summarise the data.
Finally I join all three queries onto the reference table.
Long winded maybe but it works surprisingly quickly. But your comment did provide me with new insight on UNIONs so shared points.
I solved it in a completely different approach with separte queries on the selected date. So I had one query that pulled the dates for the three previous weeks and one query for the three previous months. Then I create queries for each table to join on their respective date query and summarise the data.
Finally I join all three queries onto the reference table.
Long winded maybe but it works surprisingly quickly. But your comment did provide me with new insight on UNIONs so shared points.
ASKER
Not the solution I went with but provided something I wasnt aware of. Cant split or reduce points though.
SELECT Salesperson, Date, SUM(AMOUNT) As Amount
FROM (
SELECT Salesperson, Date, Amount
FROM Nexus
UNION ALL
SELECT Salesperson, Date, Amount
FROM Company
UNION ALL
SELECT Salesperson, Date, Amount
FROM Portal
) A
GROUP BY Salesperson, Date
That will combine the salespeople at the lowest data level from each source. You then amy need to outer join to a salesperson list to get missing salespeople.
That doesn't cover anywhere near all of your request but hopefully points you in the right direction...... now go and have a sleep!