Restricting query data

I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru 
12/31/08 to take out duplicate accounts, this works great when I run the 
report quarterly or yearly but  I need to run this report monthly. I have 3 
tables that work together and these dates are coming from my Activities 
table, I keep track of when I have had contact with them.  

I am looking to only count these people 1 time in a year  (filter by start 
date does not work is it is driven by actibvity date) for example if I work 
with them in 1/08 run my report in 1/08 they should show up.  If I work with 
them in 2/08  I do not want them in my 2/08 report as they were counted in 
1/08.

How can I filter these clients out. What expression, operator word(s) or do 
i need to do it in the SQL can I use. 

I have tried Select DISTINCT [Actibity Date] between 1/1/08 and 12/31/08  

What string do I need to use to filter out the people I counted in 1/08 and 
so on.
0
Utf
2/11/2008 8:58:17 PM
access 16762 articles. 3 followers. Follow

1 Replies
728 Views

Similar Articles

[PageSpeed] 31

If you want to leave out a month, use something like:

Select DISTINCT [Actibity Date] between #2/1/08# and #12/31/08#

from now, until the end of the year:

Select DISTINCT [Actibity Date] between Date() and #12/31/08#
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"nvacek" <nvacek@discussions.microsoft.com> wrote in message 
news:A3843831-9819-417A-AD09-817471AA8791@microsoft.com...
>I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru
> 12/31/08 to take out duplicate accounts, this works great when I run the
> report quarterly or yearly but  I need to run this report monthly. I have 
> 3
> tables that work together and these dates are coming from my Activities
> table, I keep track of when I have had contact with them.
>
> I am looking to only count these people 1 time in a year  (filter by start
> date does not work is it is driven by actibvity date) for example if I 
> work
> with them in 1/08 run my report in 1/08 they should show up.  If I work 
> with
> them in 2/08  I do not want them in my 2/08 report as they were counted in
> 1/08.
>
> How can I filter these clients out. What expression, operator word(s) or 
> do
> i need to do it in the SQL can I use.
>
> I have tried Select DISTINCT [Actibity Date] between 1/1/08 and 12/31/08
>
> What string do I need to use to filter out the people I counted in 1/08 
> and
> so on. 


0
Arvin
2/11/2008 9:03:19 PM
Reply:

Similar Artilces:

Counting paired data
I have a set of data with columns alternating names and numbers. I would like to return the number of times a specified name and number is paired up. For example, how many times is "Jones" in the data range with "30" in the cell to the right. I have tried OFFSET, COUNTIF, and SUMPRODUCT formulas, but have been unable to get the results I want. Any help would be appreciated. An array formula would be fine. Using Excel 97. Ken Schmidt Hi Ken Provided names are in column A and numbers in column B, formula: =SUMPRODUCT((A1:A50="Jones")*(B1:B50=30)) NB: This is...

Data displayed that is not equal to FALSE in a row
In row 1 cells A1-D1 contain formulas (IF/AND statements). In cell E1 I want whatever word that is not equal to “FALSE” to be displayed. A B C D E 1 FALSE FALSE Under FALSE Under The words can appear in any of cells A-D in each row as below. A B C D E 1 FALSE FALSE Under FALSE Under 2 FALSE On FALSE FALSE On 3 Over FALSE FALSE FALSE Over 4 FALSE FALSE FALSE On On Thank you in advance for your help. =SUBSTITUTE(A1&B1&C1&D1,FALSE,"") -- Gary''s Student - gsnu201001 Thanks Gary. That works great! "Gary'...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

Using a Query to give me the 160th and 320th etc.
I need to create a Query that only shows me the 160th record, 320, 480 etc. Can anyone tell me how this can be done? -- -The Novice Learn Today, Teach Tomorrow Great Success is ones ability to ask for Help. If your records are numbered (i.e., if they include a sequence number field), you could try using the Mod() function to take every 160th record. Regards Jeff Boyce Microsoft Office/Access MVP "TheNovice" <TheNovice@discussions.microsoft.com> wrote in message news:811C7D2A-C791-4972-9E32-FFBEDD4A264A@microsoft.com... >I need to create a Query that only shows me...

Lost all my data in out look 2003
Very Urgent Situation I have Microsoft Outlook 2003 - Windows XP Pro part of Microsoft Outlook Small Business On the menu bar, I went on HELP then clicked on Detect and Repair. I had a few problems and thought that it would resolve it. Instead after this process, I reopened Outlook and found a fresh new copy of outlook and my all data where vanished. I lost everything: -All my contacts addresses, -All my data appointments etc. on my calendar -All my recent e-mails and saved one and differents created folders in fact everything. How can I retrieve and restore just like it was few hours...

chart only weekdays when data set includes weekends?
I've got a simple table of data: A B date1 val1 date2 val2 .... dateN valN I have several hundred data points, including weekends, but I would only like to chart the weekdays. Is there a straightforward way to do this? Regards, George Coulouris Jon Peltier has instructions for conditional charts that you could adapt to your workbook: http://www.peltiertech.com/Excel/Charts/format.html#CondChart For example, with dates in column A, use the Weekday function to return dates for Monday to Friday. =IF(WEEKDAY($A2,2)>=6,NA(),A2) george.coulouris@gmail.com wrote: &...

Convert Unix timestamp to Access 2003 data field
I am working with a Remedy HelpDesk system that has an "arrivaltime" field stored as a Unix timestamp that I need to convert to an Access date field to be able to do a report by month (from 1 to 31 Dec for example). Does anyone know an easy way to convert this "arrivaltime" field to an Access date field? nms wrote: >I am working with a Remedy HelpDesk system that has an "arrivaltime" field >stored as a Unix timestamp that I need to convert to an Access date field to >be able to do a report by month (from 1 to 31 Dec for example). Does anyone &g...

Restriction Exchange to only accept mail from Postini
We utilize Postini for external SPAM and virus filtering. I want to set my front end Exchange Enterprise server 2000 to accept mail only from the Postini domain. Therefore eliminating and throttling down any other sources from spamming me etc. How can I do this and what is the best possible method to accomplish this? Thanks for your time. Personally, I have always used the firewall to do this - only allow traffic on port 25 from specific IPs. This should be made to work the other way too, ie. only Exchange can send mail (SMTP, POP3) out and only through the smarthost. Setesh ...

Graphical Union-Query Builder?
I have two moderately complex queries (each one has at least 7-tables and several more joins) that must be combined via a Union query. The results will be used in subsequent queries. Writing and maintaining SQL for this Union query is a last resort. How can I keep my application as intuitive and as low-maintenance as possible? 1. Does Microsoft or anyone else have a graphical user interface for building Union queries yet? 2. Does any tool exist that could read user-specified Select queries in Access and create the corresponding Union query? I've been considering writing such...

Pivot Table Data 04-16-10
I have learned to use GETPIVOTDATA to retrieve numbers aligned with employees names. When an employees name does not appear on the pivot table, it returns #REF!, which can be turned into 4 with the ERROR.TYPE command. I can turn the 4 into a 0, but then when the number is not a 4, I get #N/A. Is there any way to get a 0 off of the pivot table if an employee name does not show up? You can wrap the GETPIVOTDATA formula inside another function to return zero if GETPIVOTDATA returns an error. In Excel 2007 you can use IFERROR: =IFERROR(GETPIVOTDATA(...),0) In Excel 2003 &a...

Finding all Authors in a Query
I have a Query that includes Authors,Rating Books and other fields. The criteria control is in Rating Books and is (is Null). This criteria will list all the unread books, but will not list Authors who do not have unread Books. I would like to show all Authors irrelevant of Rating Books. Thanx MilGar wrote: > I have a Query that includes Authors,Rating Books and other fields. The > criteria control is in Rating Books and is (is Null). This criteria will > list all the unread books, but will not list Authors who do not have > unread Books. I would like to show all Authors i...

Data Conversion
I took the plunge and purchased Quicken XG 2005. Microsoft hasn't released a deluxe version for the Canadian market in over five years. However, I have found it nigh impossible to convert my MS Money 2000 Deluxe files to Quicken. I tried QIF only to find dates disappearing and having the closing balance for the same account in both programs almost $6000 different. Last hope is to download a trial version of MS Money that will accept Cdn$ and terminology (RRSPs, RESPS etc) and convert my old MS Money 2000 to a newer MS version and then convert those files to Quicken, who appear t...

Data Definition/Schema export
Hello, Is there an easy way to obtain the schemas/data defintions for the primary entities? The purpose is to put together a data mapping document. Thanks, for the help! All of this info is contained in the Metabase. It's not too easy to put together, but there's some good code in the GotDotNet User Sample page that might have what you're looking for: http://www.gotdotnet.com/community/usersamples/default.aspx?query=crm&SortDirection=Desc&SortColumnName=CreationDate&Page=1&Size=50 "MDV1457" wrote: > Hello, > Is there an easy way to obtain t...

how to send data/message from win32 dll to mfc application?
Hi All , I have to send data from a Win32 DLL to mfc application. What are the possible ways to achieve this? In mfc application I have a document which should recieve this data . is it possible ? I read somewhere about PostMessage() method . it sends the message to applcations main window (CmainFrm ???) .Should I handle the messages sent by the dll and update my document from here ? Thanks in advance JustLikeDat ggurubasavaraja@gmail.com wrote: > Hi All , > > I have to send data from a Win32 DLL to mfc application. What are the > possible ways to achieve this? > In mfc a...

Yes/No query from form.
Greetings all, I am attempting to create what seems (to me at least) to be a very complex query. I have a form (which will eventually link to my query when I can figure out what I'm doing). The form has 4 yes/no check boxes, and two combo boxes. What I would like to do is have a user of my database select one of the check boxes (hence filtering for any checked record in my table) and then make a choice from each of the two combo boxes which would then filter the search results just to those choices selected. Any help would be greatly appreciated! See: Search form - Handle ...

Unable to restore data from previous year
This seems to me as a bug in DPM 2007 but maybe I'm wrong. Today I discovered that when trying to restore with the DPM UI, I'm unable to select a date before January 1st 2010. We've only recently set up DPM so we have no recovery points for January 2009. When I try to select the year 2009, the month box stays at January and the year goes back to 2010 (because DPM wasn't operational in January 2009). When I try to select the month December, the Year box stays at 2010 and the month box jumps back to January! The arrows also don't do a thing. For now, the mo...

parameter query 12-24-07
Hi, I have a parameter query that prompts for 3 different criteria. One being between one date and another date. I want the query to return data on the other criteria and return all dates if I dont put the dates in the last criteria. Hope someone can help me. Thanks Christina So you want the parameter to NOT limit the query results if it is left blank? That's doable, but somewhat messy. Switch the query to SQL View, and set up the WHERE clause like this: SELECT * FROM Table1 WHERE ((ClientID = [WhatClient]) OR ([WhatClient] Is Null)) AND ((SaleDate >= [StartDate]) OR ([Start...

Slugs, dynamic data in template mail etc.
Hello I can put some data filds in email templates for example: {!lead:firstname;} {!systemuser:lastname;} or evan realated fields: {!lead:ownerid/@name;} but why can NOT do this: {!lead:ownerid/@firstname;} or {!lead:ownerid/@FirstName;} etc. on the other hand i can use slugs in workflow just like below: &lead.firstname; &lead.ownerid; evan &lead.owneridname but i still can't put the field with the first name of the owner: &lead.owneridfirstname; doesn't work. Any suggestions would be appreciated -- Best regards Luck Piech It doesn't work because ther...

Using a Parameter Query in a Report?
I have a table called "Changes" with several fields that is feeding a sub-report of my main report. One of the fields in the table is named "Contract". This field is actually an index into a lookup table that has the text version of the contract name. What I want to do is somehow translate the index in "Changes" back into the contract name and put that name in the sub-report. The lookup table is named "tlkp_Contract" and has "ID" and "Contract_Name" as its fields. I created a query that looks up "Contract_Name&...

restricting invoice dates
Does the GP software have an option to setup restrictions on invoice dates both in Accounts Payable and Accounts Receivable. For example if the current period is May of 2006 you can only enter invoices dates of April and May of 2006... Nelson, There is a free 'customization' you can get from GP Support called Document Date Verify. With this customization installed and with the fiscal periods for all months except April and May closed for the Purchasing and Sales series (Tools > Setup > Company > Fiscal Periods) users trying to enter invoices with other dates will ...

create database from excel data
hello, I have an XP excel file with 21000rows & 20columns of athletic results. at the moment all I do is an auto filter on an athletes name to get all of their records displayed. Is there a way I could convert to XP access to have the data available through the web.NOT very competant with XP access. any thoughts or help appreciated, regards Ditchy Ballarat Vic Aus If the Excel spreadsheet is laid out in a simple tabular format without blank rows or columns then it can be easily imported into Access - choose 'Get external data ...' from the File menu. If it does contain blank ro...

report not following the query
I have a report that is not producing the correct order of the information that the underlying query indicates. Running just the query has the correct order, but report does not. Below is the sql from the query that is used in the report. SELECT tblChangeRequest.DevTargetEndDate, tblChangeRequest.ChangeRequestID, tblChangeRequest.ChangeRequest, tblProjectLead.ProjectLead, tblSPGName.SPGName, tblChangeRequestStatus.StatusReportOrder, tblChangeRequestStatus.ChangeRequestStatus FROM (((tblChangeRequest LEFT JOIN tblProjectLead ON tblChangeRequest.ProjectLeadID = tblProjectLea...

Retrieve/Recover data following my stupidity when asked to save changes
Can i retrieve data entered into a spreadsheet (after several hours of work) when I accidentally clicked "no" when asked if I wanted to save changes. I attempted to system restore, however, that failed to return my previous entries. Hi Philly! Afraid not! System Restore specifically does not touch files that you create. Jan Karel Pieterse's AutoSafe might be an Addin that will be better at preventing this problem. http://www.bmsltd.co.uk/MVP/Default.htm Here's the blurb: The standard Autosave (note the spelling) utility that ships with Excel just saves workbooks at a s...

Best Match Query
I need to create a query or queries, that return the number of matches on corresponding fields in two tables. Table 1 is Applicants, Table 2 is Mentors. Fields A1, A2, A3..., M1, M2, M3... are populated with a/b/c/d type values. I think it should be fairly simple to find the number of interests in common between Applicant1 and Mentor1, Applicant1 and Mentor2, etc. but I can't get started. Can someone point me in the right direction? The ultimate output is to identify the applicant/mentor pairs with the most in common, but that might be obvious if we can accomplish the above. TI...

XSD Data type for HTML?
Hello all, I have the below XML file whice has a node called statement. Within this node is html elements <p>, <strong> etc. I'm trying to load this into a XmlDataDocument in order to process it, however, i can't seem to get the string from within the <statement> node. Can anyone help? thanks, Jon <rat> <answer> <code><strong>A1000</strong></code> <statement> <h2>Summary</h2> <p>The formation and sustainability:</p> <ul> <li>The world of <strong>citizens&l...