+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Using Microsoft Query to extract data from a database based on todays date

  1. #1
    Registered User
    Join Date
    10-14-2010
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    2

    Exclamation Using Microsoft Query to extract data from a database based on todays date

    I am currently using Act! as a CRM. I am able to create reports based on product sales in Excel using the Actreader. I am using MS Query to structure these reports based on fields I have created in the Act database. We run a publishing companny and have sales per magazine per month. I am trying to return all the sales for a certain magazine issue (say 2010 October edition) but only return the values where the closed date = todays date (we do daily sales reports).

    Basically in MS query I have a field ACTUALCLOSEDATE which is a date field that automatically updates when you change another field from OPEN to CLOSED. I want to display all the values where ACTUALCLOSEDATE = todays date but cannot structure it correctly. You can only currently choose it in the format: #2010/10/13 10:00:00 PM# for example. '

    The query runs fine if you choose a date as above that is the actual date and time in the system but only want the sales for date = today.

    Can anyone help please?

    Thanks a lot.

    Sandra

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Using Microsoft Query to extract data from a database based on todays date

    Try this.

    In the spreadsheet, enter this formula in an available cell:

    =Today()

    Then edit your query, and change the criteria for the date column to [Enter Date] (this denotes a user-entered parameter).

    Return the data to Excel.. you will be prompted for an entry, you can enter a date of any kind...

    When back in Excel... right-click in the query results area and select Parameters.

    Then choose to Get values from following Cell and then point to the cell with the today() formula.

    You can choose to refresh automatically so that when/if date cell changes, so will the query.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-14-2010
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Using Microsoft Query to extract data from a database based on todays date

    Hi,

    Ok, I can see the principle behind it and have run it like you said where you have to enter the parameter in and point it to the cell with =today(). It is now returning no values. When I put the ACTUAL CLOSE DATE criteria equals 2010/10/16 it returns all sales for the 16th but not if you put =today() so weird.
    Here is the code like you said with the last line being the date parameter:
    SELECT TBL_OPPORTUNITY.CUST_Magazine_050816166, TBL_PRODUCTSERVICE.NAME, TBL_PRODUCTSERVICE.CUST_IssueYear_013717519, TBL_PRODUCTSERVICE.CUST_1Month_120207366, TBL_PRODUCTSERVICE.QUANTITY, TBL_PRODUCTSERVICE.ITEMTYPE, TBL_CONTACT.COMPANYNAME, TBL_CONTACT.FULLNAME, TBL_PRODUCTSERVICE.UNITPRICE, TBL_OPPORTUNITY.ACTUALCLOSEDATE
    FROM TEST3SSALES.dbo.CONTACT_OPPORTUNITY CONTACT_OPPORTUNITY, TEST3SSALES.dbo.OPPORTUNITY_PRODUCTSERVICE OPPORTUNITY_PRODUCTSERVICE, TEST3SSALES.dbo.TBL_CONTACT TBL_CONTACT, TEST3SSALES.dbo.TBL_OPPORTUNITY TBL_OPPORTUNITY, TEST3SSALES.dbo.TBL_PRODUCTSERVICE TBL_PRODUCTSERVICE
    WHERE CONTACT_OPPORTUNITY.CONTACTID = TBL_CONTACT.CONTACTID AND CONTACT_OPPORTUNITY.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID AND OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID AND TBL_PRODUCTSERVICE.PRODUCTSERVICEID = OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID AND ((TBL_OPPORTUNITY.CUST_Magazine_050816166='IMIESA') AND (TBL_PRODUCTSERVICE.CUST_IssueYear_013717519='2011') AND (TBL_PRODUCTSERVICE.CUST_1Month_120207366='01/January') AND (TBL_OPPORTUNITY.STATUSNUM=1) AND (TBL_OPPORTUNITY.ACTUALCLOSEDATE=?))

    And then with the one that works how I want it to but you have to go in and select it every time:
    SELECT TBL_OPPORTUNITY.CUST_Magazine_050816166, TBL_PRODUCTSERVICE.NAME, TBL_PRODUCTSERVICE.CUST_IssueYear_013717519, TBL_PRODUCTSERVICE.CUST_1Month_120207366, TBL_PRODUCTSERVICE.QUANTITY, TBL_PRODUCTSERVICE.ITEMTYPE, TBL_CONTACT.COMPANYNAME, TBL_CONTACT.FULLNAME, TBL_PRODUCTSERVICE.UNITPRICE, TBL_OPPORTUNITY.ACTUALCLOSEDATE
    FROM TEST3SSALES.dbo.CONTACT_OPPORTUNITY CONTACT_OPPORTUNITY, TEST3SSALES.dbo.OPPORTUNITY_PRODUCTSERVICE OPPORTUNITY_PRODUCTSERVICE, TEST3SSALES.dbo.TBL_CONTACT TBL_CONTACT, TEST3SSALES.dbo.TBL_OPPORTUNITY TBL_OPPORTUNITY, TEST3SSALES.dbo.TBL_PRODUCTSERVICE TBL_PRODUCTSERVICE
    WHERE CONTACT_OPPORTUNITY.CONTACTID = TBL_CONTACT.CONTACTID AND CONTACT_OPPORTUNITY.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID AND OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID AND TBL_PRODUCTSERVICE.PRODUCTSERVICEID = OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID AND ((TBL_OPPORTUNITY.CUST_Magazine_050816166='IMIESA') AND (TBL_PRODUCTSERVICE.CUST_IssueYear_013717519='2011') AND (TBL_PRODUCTSERVICE.CUST_1Month_120207366='01/January') AND (TBL_OPPORTUNITY.STATUSNUM=1) AND (TBL_OPPORTUNITY.ACTUALCLOSEDATE={ts '2010-11-16 22:00:00'}))

    There are 5 tables: Contact, Opportunity and Product with 2 Unique ID tables linking the three together. As I said before, I am using Actreader to pull the information out of Sage Act! CRM system. Act! stamps the ACTUALCLOSEDATE with the current time when you change an opportunity from open to closed.

    So I don't know if I have to put in the parameters like Act understands them or Excel??

    Thank very much for your prompt reply.

    Kind regards,
    Sandra

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1