Query joining two tables with different dates

I'm sure there is a simple solution to this, but being fairly new to Access I
would appreciate some assistance...

I have a query that joins two tables. One table has call details for each day
that an agent works. The other table has details for each call that is graded
for each agent. However, calls can be graded on days that the agent does not
work. When joining the tables, the query will only display calls graded on
dates that the agent works. 

This is what I would like to do to correct this: If there was a call graded
on a day that the agent did not work, this data be rolled up to the last day
that they did work.

Current SQL:
SELECT tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent, Count
(tbl_Monitors_ScorecardStep3.SumOfTPE) AS CountOfSumOfTPE, Count
(tbl_Monitors_ScorecardStep3.SumOfTPP) AS CountOfSumOfTPP
FROM tbl_ScorecardStep1 LEFT JOIN tbl_Monitors_ScorecardStep3 ON
(tbl_ScorecardStep1.RID = tbl_Monitors_ScorecardStep3.RID) AND
(tbl_ScorecardStep1.row_date = tbl_Monitors_ScorecardStep3.EVALDATE)
GROUP BY tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent;

Example of tbl_ScorecardStep1:
row_date	Agent	                RID
5/3/2010	Frapples, Bob	26152
5/4/2010	Frapples, Bob	26152
5/13/2010	Frapples, Bob	26152

Example of tbl_Monitors_ScorecardStep3:
EVALDATE	RID	SumOfTPE     SumOfTPP
5/3/2010	                26152	91	     100
5/7/2010	                26152	76	     100
5/12/2010	                26152	147	     200

Current result:
row_date	Agent	         SumOfSumOfTPE    SumOfSumOfTPP
5/3/2010	Frapples, Bob     91	                       100
5/4/2010	Frapples, Bob    	                       
5/13/2010	Frapples, Bob

Desired result (5/1 and 5/12 monitor data is rolled up to the last day that
the agent worked:
row_date	Agent	         SumOfSumOfTPE    SumOfSumOfTPP
5/3/2010	Frapples, Bob     91	                       100
5/4/2010	Frapples, Bob    223	                       300
5/13/2010	Frapples, Bob		

Any help pointing me in the right direction is greatly appreciated!

0
bscott
5/25/2010 5:25:04 PM
access.gettingstarted 618 articles. 1 followers. Follow

12 Replies
1413 Views

Similar Articles

[PageSpeed] 19

Try this:

SELECT row_date, Agent,
    (SELECT SUM(SumOfTPE)
     FROM tbl_Monitors_ScorecardStep3
     WHERE tbl_Monitors_ScorecardStep3.RID = SS1.Rid
     AND tbl_Monitors_ScorecardStep3.EVALDATE >= SS1.row_date
     AND tbl_Monitors_ScorecardStep3.EVALDATE <
         (SELECT SELECT NZ(MIN(row_date),#2099-01-01#)
          FROM tbl_ScorecardStep1 AS SS2
          WHERE SS2.RID = SS1.RID
          AND SS2.row_date > SS1.row_date))
AS TotalTPE,
    (SELECT SUM(SumOfTPP)
     FROM tbl_Monitors_ScorecardStep3
     WHERE tbl_Monitors_ScorecardStep3.RID = SS1.Rid
     AND tbl_Monitors_ScorecardStep3.EVALDATE >= SS1.row_date
     AND tbl_Monitors_ScorecardStep3.EVALDATE <
         (SELECT NZ(MIN(row_date),#2099-01-01#)
          FROM tbl_ScorecardStep1 AS SS2
          WHERE SS2.RID = SS1.RID
          AND SS2.row_date > SS1.row_date))
AS TotalTPP
FROM tbl_ScorecardStep1 AS SS1
ORDER BY Agent, row_date;

Calling the NZ function to return an artificially late date would not be
necessary if the latest date per agent in tbl_Monitors_ScorecardStep3 cannot
be later than the latest date per agent in tbl_ScorecardStep1.

Ken Sheridan
Stafford, England

bscott wrote:
>I'm sure there is a simple solution to this, but being fairly new to Access I
>would appreciate some assistance...
>
>I have a query that joins two tables. One table has call details for each day
>that an agent works. The other table has details for each call that is graded
>for each agent. However, calls can be graded on days that the agent does not
>work. When joining the tables, the query will only display calls graded on
>dates that the agent works. 
>
>This is what I would like to do to correct this: If there was a call graded
>on a day that the agent did not work, this data be rolled up to the last day
>that they did work.
>
>Current SQL:
>SELECT tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent, Count
>(tbl_Monitors_ScorecardStep3.SumOfTPE) AS CountOfSumOfTPE, Count
>(tbl_Monitors_ScorecardStep3.SumOfTPP) AS CountOfSumOfTPP
>FROM tbl_ScorecardStep1 LEFT JOIN tbl_Monitors_ScorecardStep3 ON
>(tbl_ScorecardStep1.RID = tbl_Monitors_ScorecardStep3.RID) AND
>(tbl_ScorecardStep1.row_date = tbl_Monitors_ScorecardStep3.EVALDATE)
>GROUP BY tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent;
>
>Example of tbl_ScorecardStep1:
>row_date	Agent	                RID
>5/3/2010	Frapples, Bob	26152
>5/4/2010	Frapples, Bob	26152
>5/13/2010	Frapples, Bob	26152
>
>Example of tbl_Monitors_ScorecardStep3:
>EVALDATE	RID	SumOfTPE     SumOfTPP
>5/3/2010	                26152	91	     100
>5/7/2010	                26152	76	     100
>5/12/2010	                26152	147	     200
>
>Current result:
>row_date	Agent	         SumOfSumOfTPE    SumOfSumOfTPP
>5/3/2010	Frapples, Bob     91	                       100
>5/4/2010	Frapples, Bob    	                       
>5/13/2010	Frapples, Bob
>
>Desired result (5/1 and 5/12 monitor data is rolled up to the last day that
>the agent worked:
>row_date	Agent	         SumOfSumOfTPE    SumOfSumOfTPP
>5/3/2010	Frapples, Bob     91	                       100
>5/4/2010	Frapples, Bob    223	                       300
>5/13/2010	Frapples, Bob		
>
>Any help pointing me in the right direction is greatly appreciated!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
KenSheridan
5/25/2010 10:48:11 PM
Thanks Ken! When I tried this I received a dialogue saying 

"Syntax error in query expression '(SELECT SUM(SumOfTPE)
FROM tbl_Monitors_ScorecardStep3
WHERE tbl_Monitors_ScorecardStep3.RID = SS1.Rid
AND tbl_Monitors_ScorecardStep3.EVALDATE >= SS1.row_date
AND tbl_Monitors_ScorecardStep3.EVALDATE <
(SELECT SELECT NZ(MIN(r'.

Am I doing something wrong? I just pasted your code in place of mine in the
SQL view of the query.

-- 
Message posted via http://www.accessmonster.com

0
bscott
5/26/2010 1:38:29 PM
Actually, it looks like the error was due to two SELECTs in a row, I
corrected that and am about 30 minutes into the query running. I will post
how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
bscott
5/26/2010 2:45:03 PM
If it's taking that long its clearly not a practical solution.  Make sure the
RID, evaldate and row_date columns are all indexed; if they are not currently
that might speed things up.

Ken Sheridan
Stafford, England

bscott wrote:
>Actually, it looks like the error was due to two SELECTs in a row, I
>corrected that and am about 30 minutes into the query running. I will post
>how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
KenSheridan
5/26/2010 4:44:05 PM
I was able to filter the unmatched data into its own table and narrow down
the data from over 100,000 records to about 200 so it should go faster now.
Now that I know that every record in the monitor data will not match the
scorecard data and will need to roll up to the nearest date can the SQL be
updated as such?

KenSheridan wrote:
>If it's taking that long its clearly not a practical solution.  Make sure the
>RID, evaldate and row_date columns are all indexed; if they are not currently
>that might speed things up.
>
>Ken Sheridan
>Stafford, England
>
>>Actually, it looks like the error was due to two SELECTs in a row, I
>>corrected that and am about 30 minutes into the query running. I will post
>>how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
bscott
5/26/2010 9:36:49 PM
The only other way I can think of handling this would be to first create a
query which returns the row_date and the latest unmatched date after that for
each row in tbl_ScorecardStep1:

SELECT row_date, 
    (SELECT NZ(MIN(row_date),#2099-01-01#)-1
     FROM tbl_ScorecardStep1 AS SS2
     WHERE SS2.RID =SS1.RID
     AND SS2.row-date > SS1.row_date) AS EndDate, 
Agent, RID
FROM tbl_ScorecardStep1 AS SS1;

Save the above query as Q1 say.  The join it to tbl_Monitors_ScorecardStep3
like so:

SELECT Q1.rowdate, Q1.agent, 
SUM(SumOfTPE) As TotalTPE,
SUM(SumOfTPP) As TotalTPP,
FROM Q1, tbl_Monitors_ScorecardStep3
WHERE tbl_Monitors_ScorecardStep3.Evaldate
BETWEEN Q1.row_date AND Q1.EndDate
GROUP BY Q1.rowdate, Q1.agent;

Whether this will work or not I wouldn't like to say without testing it for
myself, which I obviously can't do, but I think the logic is sound.  Even
then I've no idea how it might perform.

Ken Sheridan
Stafford, England

bscott wrote:
>I was able to filter the unmatched data into its own table and narrow down
>the data from over 100,000 records to about 200 so it should go faster now.
>Now that I know that every record in the monitor data will not match the
>scorecard data and will need to roll up to the nearest date can the SQL be
>updated as such?
>
>>If it's taking that long its clearly not a practical solution.  Make sure the
>>RID, evaldate and row_date columns are all indexed; if they are not currently
>[quoted text clipped - 6 lines]
>>>corrected that and am about 30 minutes into the query running. I will post
>>>how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
KenSheridan
5/26/2010 10:36:32 PM
Because of the amount of rows I'm dealing with I think that the second
solution will be less feasible. 
The first solution that you gave me seems to be on the right track, but of
the 230 unmatched records it only matches half of them, 115. 
The reason why I asked if the SQL could be updated is now that I know all of
the records will be unmatched it no longer needs to check for matches, only
roll all records up to the previous date worked. However, if this fact will
not simplify the SQL at all then disregard.
But, would you have any idea why it doesn't match all of the records? I can
send you samples of the tables or any other data that might be of use in
resolving this.

KenSheridan wrote:
>The only other way I can think of handling this would be to first create a
>query which returns the row_date and the latest unmatched date after that for
>each row in tbl_ScorecardStep1:
>
>SELECT row_date, 
>    (SELECT NZ(MIN(row_date),#2099-01-01#)-1
>     FROM tbl_ScorecardStep1 AS SS2
>     WHERE SS2.RID =SS1.RID
>     AND SS2.row-date > SS1.row_date) AS EndDate, 
>Agent, RID
>FROM tbl_ScorecardStep1 AS SS1;
>
>Save the above query as Q1 say.  The join it to tbl_Monitors_ScorecardStep3
>like so:
>
>SELECT Q1.rowdate, Q1.agent, 
>SUM(SumOfTPE) As TotalTPE,
>SUM(SumOfTPP) As TotalTPP,
>FROM Q1, tbl_Monitors_ScorecardStep3
>WHERE tbl_Monitors_ScorecardStep3.Evaldate
>BETWEEN Q1.row_date AND Q1.EndDate
>GROUP BY Q1.rowdate, Q1.agent;
>
>Whether this will work or not I wouldn't like to say without testing it for
>myself, which I obviously can't do, but I think the logic is sound.  Even
>then I've no idea how it might perform.
>
>Ken Sheridan
>Stafford, England
>
>>I was able to filter the unmatched data into its own table and narrow down
>>the data from over 100,000 records to about 200 so it should go faster now.
>[quoted text clipped - 7 lines]
>>>>corrected that and am about 30 minutes into the query running. I will post
>>>>how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
bscott
5/27/2010 5:21:04 PM
I figured out why it wasn't matching all of them and it was on my end. So
looks like it works as should! Thanks! If you are aware of a way to further
simplify the SQL because of what I previously brought up that would be great,
but otherwise, looks like I'm all set!

bscott wrote:
>Because of the amount of rows I'm dealing with I think that the second
>solution will be less feasible. 
>The first solution that you gave me seems to be on the right track, but of
>the 230 unmatched records it only matches half of them, 115. 
>The reason why I asked if the SQL could be updated is now that I know all of
>the records will be unmatched it no longer needs to check for matches, only
>roll all records up to the previous date worked. However, if this fact will
>not simplify the SQL at all then disregard.
>But, would you have any idea why it doesn't match all of the records? I can
>send you samples of the tables or any other data that might be of use in
>resolving this.
>
>>The only other way I can think of handling this would be to first create a
>>query which returns the row_date and the latest unmatched date after that for
>[quoted text clipped - 31 lines]
>>>>>corrected that and am about 30 minutes into the query running. I will post
>>>>>how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
bscott
5/27/2010 7:22:53 PM
I think its also worth giving the second method a try.  You'll probably find
it considerably faster than the first with the large number of rows you are
dealing with.  Have you tried it?

Even better would be to use a JOIN in Q1 rather than a subquery:

SELECT SS1.row_date,
NZ(MIN(SS2.row_date),#2099-01-01#)-1 As EndDate,
FROM tbl_ScorecardStep1 AS SS1 LEFT JOIN
tbl_ScorecardStep1 AS SS2 
ON SS1.rowdate < SS2.row_date
GROUP BY SS1.row_date;

BTW if you open this query independently you may well find that the EndDate
column is returned as a number, not a date.  Don't worry, it's just the
underlying number as which the date/time data type is implemented in Access.
Normally this query would not be opened of course as its only purpose is to
return a result table to be joined to tbl_Monitors_ScorecardStep3 in the
final query. 

Ken Sheridan
Stafford, England

bscott wrote:
>I figured out why it wasn't matching all of them and it was on my end. So
>looks like it works as should! Thanks! If you are aware of a way to further
>simplify the SQL because of what I previously brought up that would be great,
>but otherwise, looks like I'm all set!
>
>>Because of the amount of rows I'm dealing with I think that the second
>>solution will be less feasible. 
>[quoted text clipped - 13 lines]
>>>>>>corrected that and am about 30 minutes into the query running. I will post
>>>>>>how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
KenSheridan
5/27/2010 9:20:57 PM
I'm working on giving that a try right now Ken. While I'm working on this, I
thought that I would double check something with you. When I try to run the
JOIN qry it says that there is a syntax error at "FROM". When I remove the
comma from the end of "EndDate" the error goes away. Was this the correct
thing to do without messing up the qry?

KenSheridan wrote:
>I think its also worth giving the second method a try.  You'll probably find
>it considerably faster than the first with the large number of rows you are
>dealing with.  Have you tried it?
>
>Even better would be to use a JOIN in Q1 rather than a subquery:
>
>SELECT SS1.row_date,
>NZ(MIN(SS2.row_date),#2099-01-01#)-1 As EndDate,
>FROM tbl_ScorecardStep1 AS SS1 LEFT JOIN
>tbl_ScorecardStep1 AS SS2 
>ON SS1.rowdate < SS2.row_date
>GROUP BY SS1.row_date;
>
>BTW if you open this query independently you may well find that the EndDate
>column is returned as a number, not a date.  Don't worry, it's just the
>underlying number as which the date/time data type is implemented in Access.
>Normally this query would not be opened of course as its only purpose is to
>return a result table to be joined to tbl_Monitors_ScorecardStep3 in the
>final query. 
>
>Ken Sheridan
>Stafford, England
>
>>I figured out why it wasn't matching all of them and it was on my end. So
>>looks like it works as should! Thanks! If you are aware of a way to further
>[quoted text clipped - 6 lines]
>>>>>>>corrected that and am about 30 minutes into the query running. I will post
>>>>>>>how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
bscott
5/28/2010 2:36:20 PM
Mea culpa!  That was just a typo on my part when adapting the SQL statement
from a query on one of my own tables.

Ken Sheridan
Stafford, England

bscott wrote:
>I'm working on giving that a try right now Ken. While I'm working on this, I
>thought that I would double check something with you. When I try to run the
>JOIN qry it says that there is a syntax error at "FROM". When I remove the
>comma from the end of "EndDate" the error goes away. Was this the correct
>thing to do without messing up the qry?
>
>>I think its also worth giving the second method a try.  You'll probably find
>>it considerably faster than the first with the large number of rows you are
>[quoted text clipped - 24 lines]
>>>>>>>>corrected that and am about 30 minutes into the query running. I will post
>>>>>>>>how successful I am. Thanks again!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

0
KenSheridan
5/28/2010 3:51:04 PM
On Fri, 28 May 2010 14:36:20 GMT, "bscott via AccessMonster.com" <u60332@uwe>
wrote:

>I'm working on giving that a try right now Ken. While I'm working on this, I
>thought that I would double check something with you. When I try to run the
>JOIN qry it says that there is a syntax error at "FROM". When I remove the
>comma from the end of "EndDate" the error goes away. Was this the correct
>thing to do without messing up the qry?
>
>KenSheridan wrote:
>>I think its also worth giving the second method a try.  You'll probably find
>>it considerably faster than the first with the large number of rows you are
>>dealing with.  Have you tried it?
>>
>>Even better would be to use a JOIN in Q1 rather than a subquery:
>>
>>SELECT SS1.row_date,
>>NZ(MIN(SS2.row_date),#2099-01-01#)-1 As EndDate,
>>FROM tbl_ScorecardStep1 AS SS1 LEFT JOIN
>>tbl_ScorecardStep1 AS SS2 
>>ON SS1.rowdate < SS2.row_date
>>GROUP BY SS1.row_date;

PMFJI but... yes, removing the comma was the right thing to do, and will not
mess up your query.
-- 

             John W. Vinson [MVP]
0
John
5/28/2010 5:36:35 PM
Reply:

Similar Artilces:

Need help on comparison of dates in different format.
Hi I have question pertaining comparison of dates in different format :- eg. 20 April , 2004 [Cell A1] 20-4-04 [Cell A2] I was using if not function. ie. if(A1=A2, True, False). The result always shown as False. Pls help as to how could I do correct way solving this problem. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi are both cells real date values or are they stored as 'Text'?. Check both values with =ISNUMBER(A1) =ISNUMBER(A2) th...

Two Steps at Once...
I have a column of stock prices in column B with associated dates in column A. Let's call it dates in A1:A100 and prices in B1:B100. In cells A101:A200 I repeat the same dates In cells B101:B200 I fill down the formula =B2/B1 so those cells show the daily change in the stock prices. I can now calculate the STDEV of those daily price changes =STDEV(B101:B200) How can I do this in one step, direct from a column of stock prices to the STDEV of the daily price changes? Thanks, Steve =STDEV(N(B2:B100/B1:B99)) which is an array formula, it should be committed with Ctrl-Shift-Enter, n...

Reminder Date in Outlook 2007
Hi I would appreciate help with this problem. I often set reminders on e- mails using the Right click on the e-mail in my inbox and selecting Add Reminder... My problem is that the default date for the reminder is:01 January 4501 at 08:30 am. I am perfectly happy with the time, but cannot find where to change the default date for this option. My computer date and time are correct, and so is my calendar in Outlook working on the correct date. I am running Windows XP Home SP3 and Office 2007 SP1. Thanks Heather ...

Need Help with Sumif Function including dates
Hi, I need some assistance with tracking my commissions that are due to be paid each month. The "Comm Due Date" is in column "C", rows 3 - 30 which list by date all of the Commissions Due to be paid and the "Comm Balance" is in column "H", rows 3 - 30 which has the Commission Balance due for each sale. I tried utilizing a formula from a post that I found from April '05 but for some reason I keep receiving a #NAME? error. I'm not sure what I am doing wrong. I want to find the total due by month so that as I add additional sale...

Excel Web Queries
I know how to setup Excel 2002 web queries from sites that are publicl available. The question is: *can you setup web queries linked to a we site that is private (i.e. you need a password to access it) such a your private portfolio in Morningstar or similar sites? -- rpensott ----------------------------------------------------------------------- rpensotti's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1464 View this thread: http://www.excelforum.com/showthread.php?threadid=26267 ...

Pivot table question #9
I recently had a problem with values not showing properly in my PivotTable, until it was poited out to me that I was using Text in some cells instead of numbers. Ideally, I would like to include text. Is there anyway at all that I can? My data comes from an Access Table by the way My reasoning. the data in the pivot shows scores overall of several years competition, and each meet has 2 reserves, who can step in for an excluded competitor. However, the reserves might not compete, but still need to be recorded as a competitor at the event. Rather than showing zero (0) as their score, I'd r...

Priv1.edb has old date?
I was checking the size of my Priv1.edb files today on my two exchange servers and noticed the date windows explorer is reporting is two days old (9/15/04). Is this normal? Both servers are up and running with many users and no trouble reported. Thanks! Sure. Note that size of the edb file may not be accurate either while the store is up. If you stop the IS , it should give the accurate size on disk. On Fri, 17 Sep 2004 16:51:09 GMT, "You Know Who" <stop-spam-please_kilbyfan@aol.com> wrote: >I was checking the size of my Priv1.edb files today on my two exchange >...

two types of opportunities, cloning?
Hi all, we would like to use two different types of opportunities in CRM 3.0. One would be for standard products, and one for service. We've been thinking about it and having separate entity for service opps seems just to be the way for us. Easier when adding data, much more convenient to display... So, is there a way of cloning the opportunity entity in a way that we could use its functionality for another entity? Just like deriving a class in OOP. Or any workaround like creating a new entity and linking an opportunity? Or the other way round? Any thoughts on this are welcome. Cheers, ...

two copies of every email in crm3
Hi Whenever a user sends an email message from within a crm3 account as an activity, two instances of the original message subsequently appear in "history." how do i ensure that only 1 instance of an email is kept? many thanks in advance This does happens if you sent an email from one Crm user to another. If this is the case you can avoid this by going to Settings > Organization Settings > System Settings and on the tab E-mail Tracking set the second radio button (Exclude e-mail) to Yes. If it is not user to user mail the above won't change that. -- Patrick Verbe...

Difference between yyy and yyyy?
When typing custom formats, yyy appears to do the same thing as yyyy. Some online tutorials seem to indicate this is a hold-over. Does yyy have any official function? IlNxdWFyZSBQZWciIDxTcXVhcmVQZWdAUm91bmQuSG9sZT4gaGF0aCB3cml0dGVuOiBuZXdzOmRz NXZjNGRnMWdlcnAwZmhwdHJxNTBzc2k2NmtlbzJ2dDlANGF4LmNvbS4uLg0KPiBXaGVuIHR5cGlu ZyBjdXN0b20gZm9ybWF0cywgeXl5IGFwcGVhcnMgdG8gZG8gdGhlIHNhbWUgdGhpbmcgYXMgeXl5 eS4NCj4gU29tZSBvbmxpbmUgdHV0b3JpYWxzIHNlZW0gdG8gaW5kaWNhdGUgdGhpcyBpcyBhIGhv bGQtb3Zlci4NCj4gDQo+IERvZXMgeXl5IGhhdmUgYW55IG9mZmljaWFsIGZ1bmN0aW9uPw0KDQoN Ck5vLCBpdCdzIGp1c3QgZmFpbC1zYWZlIGFuYWxvZyBmb3...

How to pick date and month?
Hi All - Could you please help me on this? In a worksheet "A" column containing birthdays(date, dd/mm/yyyy). How can I write a code to show date & month("A" column) matching with todays date & month in the respective "B" column.? Thanks a lot in advance for your helps... Ratheesh Hi Ratheesh, You don't really need any code for this, a simple formula will work as I understand your question. Try this: =IF(A1=TODAY(),A1,"") Enter this into cell B1 and copy it down the rows that you need it. It will display the date in th...

Problem starting different http connections from the same application
Hi, I have a multithreaded application in which one thread sends a request to a web server -and reads the response for a while (which is why its a separate thread). The other thread needs to send data periodically to the server - but this is where I run into problems. In the method that the second thread uses makes a new session , gets a new connection, creates a new http file - but fails to send the data. Looking at the ethereal captures, it seems that the second thread is sending requests through the same tcp connection that the very first request (that hasn't returned yet) we...

won't recognize two different cd's
I am trying to rip two differentcd's, the media player insists they are the same and copies one over the other. What do I do? "Pat" wrote: > I am trying to rip two differentcd's, the media player insists they are the > same and copies one over the other. What do I do? If you purchased the CDs or have them on their originally produced discs, you shouldn't have a problem. If they were burned to CD from another device, try changing the 'quality' settings under the rip menu to the highest setting. They might be on low quality CD's that ...

Please Help: compare values in two columns present in seperate tab
Hi All, How can I compare values in two columns (columns are in different tables) ? I also want to display the values that dont match in a seperate table. For eg: If values in Column1 from Table1 does not match Values in Column1 from Table2 then display those values in a seperate table Is there a way to do this? THanks in advance Here's one way: http://www.fmsinc.com/microsoftaccess/query/distinct_vs_distinctrow/unique_values_records.asp -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wr...

To Query or not to Query??
Hello all - I'm a newbe to Access but have made great progress. I'm using Acess 2003. The db is up and running prefectly. Now I need to tackle the reporting. I have seen posts about reports and queries and am a little confused. Do I need to create a query to run my reports off of? I have a tabbed subform in the main form. There are seven tabs breaking up all the services we provide. Like "Promo Items", "Leads", "Client Printed Material","Internal Print Material" and so on. The main form has the User, Branch and Marketing Rep. I need to...

Append query?
Hello Experts,I have an Append query that takes data from one table and puts it intoanother.I only want the query to insert rows, however, where two records aredistinct.I've tried using the QBE and writing some SQL code.Something like this:Insert into Table_ASelect Table_B.*>From Table_BWhereTable_A.cust_no <> Table_B.cust_noAndTable_A.empl_no <> Table_B.empl_noDoes anyone have any ideas?I'm basically trying to use an Append query to back up a table. Ionly want the query, however, to append new records.alex...

Cleaner way to query for negatives?
Access 2000, Win XP SP2 Hi all, I'm looking for a cleaner way to determine whether or not an entry in one table has a specific field checked yes or no in another table. Table 1: fldVendorID (Primary key, no dupes) fldVendorName Table 2: fldVendorID (dupes allowed) fldContact fldAllowed Table 2 can have many fldContacts per each fldVendorID from Table 1. fldAllowed is a yes/no field. I want to query for any VendorID which has NO fldAllowed=yes. I've done it this way: Query 1 counts all contacts from Table 2, grouped by fldVendorID Query 2 counts all fldAllowed="No"...

return all records in crosstab query
I have a combo box on user pick form where users can choose one Operator and leave the box blank to return all records. How do I revise my below code to allow users to leave the [frm_Pick_Operation_Emp_Chart]![Operationcmb] field on my pick form blank? Thank you! PARAMETERS [forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] DateTime, [forms]![frm_Pick_Operation_Emp_Chart]![EndDatetxt] DateTime, [forms]![frm_Pick_Operation_Emp_Chart]![Operationcmb] Text ( 255 ); TRANSFORM Sum(qry_Defect_by_Operation_Emp_Chart.[SumOfDef Qnty]) AS [SumOfSumOfDef Qnty] SELECT qry_Defect_by_Operation_Emp_...

Sort grouped unread email by date?
Outlook 2003. My unread email is grouped by folder. How can I get the mail in each folder to sort by date? I tried sorting by date, then folder, but the dates get scrambled after the folder sort. Thanks, Mich ...

Reestablishing Outlook Files from a Different Location
Just recovering from a crash with a new hard drive. Using XP with Outlook 2003 and need to change from the default path to c:\exchange for the location of all Outlook Files, Mail Boxes, Address Book, Contacts, Folders, etc. Managed to change path for Address Book, but not the rest. Not on network Need to recover old e-mail, folders, etc. Any and all assistance gratefully appreciated. -- Les Les <Les@discussions.microsoft.com> wrote: > Just recovering from a crash with a new hard drive. Using XP with > Outlook 2003 and need to change from the default path to c:\excha...

Two-Axes Pivot Table Chart
Hi Folks - Here's my dilemma ...I have a pivot table that looks like this: Cat1 Cat2 Cat3 Unit Data Unit1 SumOfCatCount 50 60 70 CountOfCat 1 2 3 Unit 2 SumOfCatCount 55 60 70 CountOfCat 3 4 3 Unit3 SumOfCatCount 75 75 80 CountOfCat 2 5 1 I can make a nice...

Size of file with pivot table
I have a file that was 8MB when I started. I created a pivot table pulling 7 fields from a worksheet. Now the size is 20MB. Any ideas why this one pivot table would cause the file to be so large when the 8MB file already had pivot table sin it? When it was 8MB, it had 6 pivot tables. Now it's 20MB and it has 7 pitvot tables. Why is this one table so much larger? Were the 6 PivotTable independent, or did they all use the same cache? If you build PivotTables that use another PivotTable as reference (for when you're reference the same raw data, just taking a different...

Store Sent Items in a different mailbox
I would like to create a general email box for one of my groups and what is happening is that whenever there is a reply, the copy of the sent email is put onto the individual email box and not in the general. Does any have a solution to my problem where, if I hit reply on the general box a copy of the sent will be place in the general sent folder as oppose to that one particular email. TIA. You can only configure a Wizard Rule to move a copy of the message to a different folder so it will end up in both. -- Roady www.sparnaaij.net Microsoft Office and Microsoft Office related News Also ...

Converting dates to fiscal quarters
Is there a way to easily convert a date to a fiscal quarter Eg 1/1/03 converted to Q1-2003, 12/7/98 to Q4-199 Thanks in advance for your respons One way with the date in A1 ="Q"&INT((MONTH(A1)+2)/3)&" - "&TEXT(YEAR(A1),"@") -- Regards, Peo Sjoblom "James" <anonymous@discussions.microsoft.com> wrote in message news:9F45345C-B1DB-4353-8292-A216A88121A3@microsoft.com... > Is there a way to easily convert a date to a fiscal quarter? > Eg 1/1/03 converted to Q1-2003, 12/7/98 to Q4-1998 > > Thanks in advance for your res...

When to split a single database into two
I'm creating a database in Office2003 Access. This is my first time doing this. After reading a manual, I'm still trying to understand what constitutes a need for separate but linked databases? My proposed database is to track inventory, log and monitor trouble tickets, users, and document service and pertinent information of network devices. I could have one very large database with a lot of tables (how many is "a lot" or too many?). or... should I break these up into 2 or 3 databases (and link them)? Just looking for loose advice here - maybe some good book title...