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
WedmoreAsked:
Who is Participating?
 
nmcdermaidConnect With a Mentor Commented:
Is there any specific reason? Every SQL operator has advantages and disadvantages. Certainly its a common mistake to use UNION instead of UNION ALL, as UNION by itself forces a DISTINCT, which in turn generally forces a sort of data, which is an expensive operation.
You should use whatever operators solve your problem within whatever parameters you have. In this case you may find UNION is quicker then performing three full outer joins (which is the other 'direct' way of performing this 'merge' of data)
0
 
nmcdermaidCommented:
You could union them up instead of joining them.
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!

 
0
 
WedmoreAuthor Commented:
I was always under the impression UNIONs should be avoided...
0
 
WedmoreAuthor Commented:
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.
0
 
WedmoreAuthor Commented:
Not the solution I went with but provided something I wasnt aware of.  Cant split or reduce points though.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.