Mixed Report with Weekly and Monthly Data

Posted on 2009-12-29
Last Modified: 2012-05-08
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 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 :)

Question by:Wedmore
    LVL 30

    Expert Comment

    You could union them up instead of joining them.
    SELECT Salesperson, Date, SUM(AMOUNT) As Amount
    FROM (
    SELECT Salesperson, Date, Amount
    FROM Nexus
    SELECT Salesperson, Date, Amount
    FROM Company
    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!


    Author Comment

    I was always under the impression UNIONs should be avoided...
    LVL 30

    Accepted Solution

    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)

    Author Comment

    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.

    Author Closing Comment

    Not the solution I went with but provided something I wasnt aware of.  Cant split or reduce points though.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video discusses moving either the default database or any database to a new volume.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now