#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0

    Probably basic SQL problem


    Morning everyone

    First of all, apologies if this is a very simple question. I am not very familiar with SQL, I have cannibalised a few queries generated by MS SQL (created via Excel) and reached an impass for something I am trying to achieve, hence the cry for help here.

    I am attempting to create a self updating performance spreadsheet for my sales team.

    We use a transport planning system called Manpack and I can use MS Query to interrogate this system and pull back any data I want. My reps sign up new accounts and they are given an account name internally and I can construct an SQL query that looks for all the transactions for those named accounts in the current year.

    Currently this SQL statement looks like this:
    SELECT ANON01_HAJobFile.JobNumber, ANON01_HAJobFile.Account, ANON01_HAJobFile.Volume, ANON01_HAJobFile.Quantity, ANON01_HAJobFile.Value1, ANON01_HAJobFile.DelDate, ANON01_HAJobFile.JobCancelled
    FROM DBA.ANON01_HAJobFile ANON01_HAJobFile
    WHERE (ANON01_HAJobFile.JobCancelled='N') AND (ANON01_HAJobFile.Account In ('C01','CAR','COC','DEB','HAR','HOL','INT','KAC','RDM','UKC','RDM','DOB','VER')) AND (ANON01_HAJobFile.DelDate Between {d '2012-01-01'} And {d '2012-12-31'})
    ORDER BY ANON01_HAJobFile.DelDate

    Referring particularily to the bit:- WHERE Job is not cancelled and WHERE job is one of (list of accounts)

    Hold that thought a moment please...




    Now, we have another system (Sage Act!) which is a prospecting tool, I can also interrogate that and retrieve any info I need from there (and do). One of the things that I can get from Act! easily is a list of Account names for a given Rep in the form of a table. When a rep signs up a new customer, Act! is updated and the table will show the new accounts at the bottom of it.

    The code for this looks like:-
    SELECT TBL_CONTACT.CUST_AccountCode_015058247, TBL_CONTACT.CUST_Salesperson_053027311
    FROM SalesCombined.dbo.TBL_CONTACT TBL_CONTACT
    WHERE (TBL_CONTACT.CUST_AccountCode_015058247 Is Not Null) AND (TBL_CONTACT.CUST_Salesperson_053027311='Bryan Surname')
    ORDER BY TBL_CONTACT.CUST_AccountCode_015058247



    What I need to do is dynamically create the above SQL statement which retrieves all the customer account codes for that salesman from Act! and insert them into the SQL statement above The In list.

    I have attempted to use the [] and ? operators so that excel then asks me where to look for the parameter but it doesn't appear to like me adding a range (as opposed to a single cell) and also I need the range to be the length of the table returned which increases over time.

    Currently I manually update the Query to include any new accounts but this is time consuming and if I am not here, the reps are not able to do this themselves.

    Is it possible to nest the Act! Query within the Manpack one?

    I would appreciate any thoughts on this problem you may have
    thanks in advance
    Rob
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    Originally Posted by Reshaw
    Is it possible to nest the Act! Query within the Manpack one?
    don't know, but you can test it...
    Code:
    SELECT ANON01_HAJobFile.JobNumber
         , ANON01_HAJobFile.Account
         , ANON01_HAJobFile.Volume
         , ANON01_HAJobFile.Quantity
         , ANON01_HAJobFile.Value1
         , ANON01_HAJobFile.DelDate
         , ANON01_HAJobFile.JobCancelled
      FROM DBA.ANON01_HAJobFile ANON01_HAJobFile
     WHERE ANON01_HAJobFile.JobCancelled = 'N'
       AND ANON01_HAJobFile.Account In 
           ( SELECT TBL_CONTACT.CUST_AccountCode_015058247
               FROM SalesCombined.dbo.TBL_CONTACT TBL_CONTACT
              WHERE TBL_CONTACT.CUST_AccountCode_015058247 Is Not Null
                AND TBL_CONTACT.CUST_Salesperson_053027311 = 'Bryan Surname' )
       AND ANON01_HAJobFile.DelDate 
           BETWEEN {d '2012-01-01'} 
               AND {d '2012-12-31'}
    ORDER 
        BY ANON01_HAJobFile.DelDate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0
    Many thanks for your prompt reply

    I have entered this in as you have it and get the error message
    Table 'TBL_CONTACT' not found.

    Any ideas?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    Originally Posted by Reshaw
    Any ideas?
    well, i got that from you, didn't i

    maybe change this --
    Code:
    SalesCombined.dbo.TBL_CONTACT
    to this --
    Code:
    dbo.SalesCombined.TBL_CONTACT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0
    Yeah you did, sorry. I originally copied the code from the connection properties dialogue within Excel, so both sections of code were working as a standalone queries.

    I tried swapping the dbo to the front but get error message:
    User ID 'Salescombined' does not exist.




    Originally Posted by r937
    well, i got that from you, didn't i

    maybe change this --
    Code:
    SalesCombined.dbo.TBL_CONTACT
    to this --
    Code:
    dbo.SalesCombined.TBL_CONTACT
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    Originally Posted by Reshaw
    I tried swapping the dbo to the front but get error message:
    User ID 'Salescombined' does not exist.
    well, i'm sorry, i don't really know what your tables are called

    but i'm pretty sure 'Salescombined' isn't a User ID

    perhaps ms query doesn't allow subqueries?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0
    That's what I'm starting to think, there's references all over the web that seem to suggest it is very possible with SQL in general, but it seems that the Select function only works when its is the first line of the code and any subsequent select's are seemingly ignored and that it presumes you are referencing tables in the same database, which I'm not.

    Thanks for your help all the same
    Rob

IMN logo majestic logo threadwatch logo seochat tools logo