changing totals as a date changes

Not sure if my subject makes sense so I will explain. I need to create
a query that shows open orders for vendors for 2 dates. Previous and
Current. When i run a new query, the old current becomes the new
previous and i add anew surrent. Right now i have 2 dates and i used a
crosstab query and that works fine becuase i can delete one date and
add another. the issue is when i need to find the difference between
the two dates. if i run it now the dates are say 2/1 and 3/1. if i
rerun the data on 4/1, the formulas i used to get the difference form
2/1 and 3/1 won't work. Long story short i need a way to have 2/1 say
previous and 3/1 say current. Longwinded i know. hopefully someone
understands my ramblings
0
pat67
3/2/2010 8:43:19 PM
access.queries 6343 articles. 1 followers. Follow

8 Replies
863 Views

Similar Articles

[PageSpeed] 30

Post the SQL of your crosstab.
-- 
Build a little, test a little.


"pat67" wrote:

> Not sure if my subject makes sense so I will explain. I need to create
> a query that shows open orders for vendors for 2 dates. Previous and
> Current. When i run a new query, the old current becomes the new
> previous and i add anew surrent. Right now i have 2 dates and i used a
> crosstab query and that works fine becuase i can delete one date and
> add another. the issue is when i need to find the difference between
> the two dates. if i run it now the dates are say 2/1 and 3/1. if i
> rerun the data on 4/1, the formulas i used to get the difference form
> 2/1 and 3/1 won't work. Long story short i need a way to have 2/1 say
> previous and 3/1 say current. Longwinded i know. hopefully someone
> understands my ramblings
> .
> 
0
Utf
3/2/2010 9:52:01 PM
On Mar 2, 4:52=A0pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> Post the SQL of your crosstab.
> --
> Build a little, test a little.
>
>
>
> "pat67" wrote:
> > Not sure if my subject makes sense so I will explain. I need to create
> > a query that shows open orders for vendors for 2 dates. Previous and
> > Current. When i run a new query, the old current becomes the new
> > previous and i add anew surrent. Right now i have 2 dates and i used a
> > crosstab query and that works fine becuase i can delete one date and
> > add another. the issue is when i need to find the difference between
> > the two dates. if i run it now the dates are say 2/1 and 3/1. if i
> > rerun the data on 4/1, the formulas i used to get the difference form
> > 2/1 and 3/1 won't work. Long story short i need a way to have 2/1 say
> > previous and 3/1 say current. Longwinded i know. hopefully someone
> > understands my ramblings
> > .- Hide quoted text -
>
> - Show quoted text -

Here you go

TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
FROM tbl_Move_Out_History
GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
name]
PIVOT tbl_Move_Out_History.[Extraction Date];


0
pat67
3/3/2010 1:49:45 PM
Try this --
TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
FROM tbl_Move_Out_History
WHERE [tbl_Move_Out_History].[Extraction Date] Between 
DateSerial(Year(Date()), Month(Date()),0) AND DateSerial(Year(Date()), 
Month(Date())-2,1)
GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
PIVOT IIF(Format([tbl_Move_Out_History].[Extraction Date], "yyyymm") = 
Format(DateAdd("m", -1, Date()), "yyyymm"), "Current", "Previous");


-- 
Build a little, test a little.


"pat67" wrote:

> On Mar 2, 4:52 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
> > Post the SQL of your crosstab.
> > --
> > Build a little, test a little.
> >
> >
> >
> > "pat67" wrote:
> > > Not sure if my subject makes sense so I will explain. I need to create
> > > a query that shows open orders for vendors for 2 dates. Previous and
> > > Current. When i run a new query, the old current becomes the new
> > > previous and i add anew surrent. Right now i have 2 dates and i used a
> > > crosstab query and that works fine becuase i can delete one date and
> > > add another. the issue is when i need to find the difference between
> > > the two dates. if i run it now the dates are say 2/1 and 3/1. if i
> > > rerun the data on 4/1, the formulas i used to get the difference form
> > > 2/1 and 3/1 won't work. Long story short i need a way to have 2/1 say
> > > previous and 3/1 say current. Longwinded i know. hopefully someone
> > > understands my ramblings
> > > .- Hide quoted text -
> >
> > - Show quoted text -
> 
> Here you go
> 
> TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> FROM tbl_Move_Out_History
> GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
> name]
> PIVOT tbl_Move_Out_History.[Extraction Date];
> 
> 
> .
> 
0
Utf
3/3/2010 4:13:01 PM
On Mar 3, 11:13=A0am, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> Try this --
> TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> FROM tbl_Move_Out_History
> WHERE [tbl_Move_Out_History].[Extraction Date] Between
> DateSerial(Year(Date()), Month(Date()),0) AND DateSerial(Year(Date()),
> Month(Date())-2,1)
> GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> PIVOT IIF(Format([tbl_Move_Out_History].[Extraction Date], "yyyymm") =3D
> Format(DateAdd("m", -1, Date()), "yyyymm"), "Current", "Previous");
>
> --
> Build a little, test a little.
>
>
>
> "pat67" wrote:
> > On Mar 2, 4:52 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> > wrote:
> > > Post the SQL of your crosstab.
> > > --
> > > Build a little, test a little.
>
> > > "pat67" wrote:
> > > > Not sure if my subject makes sense so I will explain. I need to cre=
ate
> > > > a query that shows open orders for vendors for 2 dates. Previous an=
d
> > > > Current. When i run a new query, the old current becomes the new
> > > > previous and i add anew surrent. Right now i have 2 dates and i use=
d a
> > > > crosstab query and that works fine becuase i can delete one date an=
d
> > > > add another. the issue is when i need to find the difference betwee=
n
> > > > the two dates. if i run it now the dates are say 2/1 and 3/1. if i
> > > > rerun the data on 4/1, the formulas i used to get the difference fo=
rm
> > > > 2/1 and 3/1 won't work. Long story short i need a way to have 2/1 s=
ay
> > > > previous and 3/1 say current. Longwinded i know. hopefully someone
> > > > understands my ramblings
> > > > .- Hide quoted text -
>
> > > - Show quoted text -
>
> > Here you go
>
> > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> > FROM tbl_Move_Out_History
> > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
> > name]
> > PIVOT tbl_Move_Out_History.[Extraction Date];
>
> > .- Hide quoted text -
>
> - Show quoted text -


I ran that and the result was blank. Vendor and Vendor Name fields
only and no data. Is it because the date format you have is yyymm and
i have mdyyy?
0
pat67
3/3/2010 4:28:40 PM
>>Is it because the date format you have is yyymm andi have mdyyy?
A couple of questions to provide an answer to that.

Is tbl_Move_Out_History.[Extraction Date] a DateTime datatype field?
You did want all dates of last month to rollup as 'Current' and the month 
before that as 'Previous'?

If it is a DateTime field then format does not matter as I am comparing that 
field formated the same way as Date(), the present date.  If it is a text 
field then it makes all the difference.

-- 
Build a little, test a little.


"pat67" wrote:

> On Mar 3, 11:13 am, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
> > Try this --
> > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> > FROM tbl_Move_Out_History
> > WHERE [tbl_Move_Out_History].[Extraction Date] Between
> > DateSerial(Year(Date()), Month(Date()),0) AND DateSerial(Year(Date()),
> > Month(Date())-2,1)
> > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> > PIVOT IIF(Format([tbl_Move_Out_History].[Extraction Date], "yyyymm") =
> > Format(DateAdd("m", -1, Date()), "yyyymm"), "Current", "Previous");
> >
> > --
> > Build a little, test a little.
> >
> >
> >
> > "pat67" wrote:
> > > On Mar 2, 4:52 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> > > wrote:
> > > > Post the SQL of your crosstab.
> > > > --
> > > > Build a little, test a little.
> >
> > > > "pat67" wrote:
> > > > > Not sure if my subject makes sense so I will explain. I need to create
> > > > > a query that shows open orders for vendors for 2 dates. Previous and
> > > > > Current. When i run a new query, the old current becomes the new
> > > > > previous and i add anew surrent. Right now i have 2 dates and i used a
> > > > > crosstab query and that works fine becuase i can delete one date and
> > > > > add another. the issue is when i need to find the difference between
> > > > > the two dates. if i run it now the dates are say 2/1 and 3/1. if i
> > > > > rerun the data on 4/1, the formulas i used to get the difference form
> > > > > 2/1 and 3/1 won't work. Long story short i need a way to have 2/1 say
> > > > > previous and 3/1 say current. Longwinded i know. hopefully someone
> > > > > understands my ramblings
> > > > > .- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Here you go
> >
> > > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> > > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> > > FROM tbl_Move_Out_History
> > > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
> > > name]
> > > PIVOT tbl_Move_Out_History.[Extraction Date];
> >
> > > .- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
> I ran that and the result was blank. Vendor and Vendor Name fields
> only and no data. Is it because the date format you have is yyymm and
> i have mdyyy?
> .
> 
0
Utf
3/3/2010 6:39:01 PM
On Mar 3, 11:28=A0am, pat67 <pbus...@comcast.net> wrote:
> On Mar 3, 11:13=A0am, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
>
>
>
>
>
> > Try this --
> > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> > FROM tbl_Move_Out_History
> > WHERE [tbl_Move_Out_History].[Extraction Date] Between
> > DateSerial(Year(Date()), Month(Date()),0) AND DateSerial(Year(Date()),
> > Month(Date())-2,1)
> > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name=
]
> > PIVOT IIF(Format([tbl_Move_Out_History].[Extraction Date], "yyyymm") =
=3D
> > Format(DateAdd("m", -1, Date()), "yyyymm"), "Current", "Previous");
>
> > --
> > Build a little, test a little.
>
> > "pat67" wrote:
> > > On Mar 2, 4:52 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> > > wrote:
> > > > Post the SQL of your crosstab.
> > > > --
> > > > Build a little, test a little.
>
> > > > "pat67" wrote:
> > > > > Not sure if my subject makes sense so I will explain. I need to c=
reate
> > > > > a query that shows open orders for vendors for 2 dates. Previous =
and
> > > > > Current. When i run a new query, the old current becomes the new
> > > > > previous and i add anew surrent. Right now i have 2 dates and i u=
sed a
> > > > > crosstab query and that works fine becuase i can delete one date =
and
> > > > > add another. the issue is when i need to find the difference betw=
een
> > > > > the two dates. if i run it now the dates are say 2/1 and 3/1. if =
i
> > > > > rerun the data on 4/1, the formulas i used to get the difference =
form
> > > > > 2/1 and 3/1 won't work. Long story short i need a way to have 2/1=
 say
> > > > > previous and 3/1 say current. Longwinded i know. hopefully someon=
e
> > > > > understands my ramblings
> > > > > .- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Here you go
>
> > > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> > > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name=
]
> > > FROM tbl_Move_Out_History
> > > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
> > > name]
> > > PIVOT tbl_Move_Out_History.[Extraction Date];
>
> > > .- Hide quoted text -
>
> > - Show quoted text -
>
> I ran that and the result was blank. Vendor and Vendor Name fields
> only and no data. Is it because the date format you have is yyymm and
> i have mdyyy?- Hide quoted text -
>
> - Show quoted text -

Also, i run this every week so the fact your query is looking for last
month might be an issue?
0
pat67
3/3/2010 6:43:50 PM
On Mar 3, 1:43=A0pm, pat67 <pbus...@comcast.net> wrote:
> On Mar 3, 11:28=A0am, pat67 <pbus...@comcast.net> wrote:
>
>
>
>
>
> > On Mar 3, 11:13=A0am, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> > wrote:
>
> > > Try this --
> > > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> > > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name=
]
> > > FROM tbl_Move_Out_History
> > > WHERE [tbl_Move_Out_History].[Extraction Date] Between
> > > DateSerial(Year(Date()), Month(Date()),0) AND DateSerial(Year(Date())=
,
> > > Month(Date())-2,1)
> > > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor na=
me]
> > > PIVOT IIF(Format([tbl_Move_Out_History].[Extraction Date], "yyyymm") =
=3D
> > > Format(DateAdd("m", -1, Date()), "yyyymm"), "Current", "Previous");
>
> > > --
> > > Build a little, test a little.
>
> > > "pat67" wrote:
> > > > On Mar 2, 4:52 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> > > > wrote:
> > > > > Post the SQL of your crosstab.
> > > > > --
> > > > > Build a little, test a little.
>
> > > > > "pat67" wrote:
> > > > > > Not sure if my subject makes sense so I will explain. I need to=
 create
> > > > > > a query that shows open orders for vendors for 2 dates. Previou=
s and
> > > > > > Current. When i run a new query, the old current becomes the ne=
w
> > > > > > previous and i add anew surrent. Right now i have 2 dates and i=
 used a
> > > > > > crosstab query and that works fine becuase i can delete one dat=
e and
> > > > > > add another. the issue is when i need to find the difference be=
tween
> > > > > > the two dates. if i run it now the dates are say 2/1 and 3/1. i=
f i
> > > > > > rerun the data on 4/1, the formulas i used to get the differenc=
e form
> > > > > > 2/1 and 3/1 won't work. Long story short i need a way to have 2=
/1 say
> > > > > > previous and 3/1 say current. Longwinded i know. hopefully some=
one
> > > > > > understands my ramblings
> > > > > > .- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > Here you go
>
> > > > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Ope=
n]
> > > > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor na=
me]
> > > > FROM tbl_Move_Out_History
> > > > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
> > > > name]
> > > > PIVOT tbl_Move_Out_History.[Extraction Date];
>
> > > > .- Hide quoted text -
>
> > > - Show quoted text -
>
> > I ran that and the result was blank. Vendor and Vendor Name fields
> > only and no data. Is it because the date format you have is yyymm and
> > i have mdyyy?- Hide quoted text -
>
> > - Show quoted text -
>
> Also, i run this every week so the fact your query is looking for last
> month might be an issue?- Hide quoted text -
>
> - Show quoted text -

Letme explain further. The table is made from a query that deletes the
oldest date and adds a new date. so for instance yesterday the data
was from 3/1 and 3/2 and today it is from 3/2 and 3/3. There will
always only be 2 dates Normally about a week a part

i changed the sql to this

TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
FROM tbl_Move_Out_History
WHERE (((tbl_Move_Out_History.[Extraction Date]) Between Date()+1 And
Date()-10))
GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
name]
PIVOT IIf(Format([tbl_Move_Out_History].[Extraction
Date],"mmddyyyy")=3DFormat(DateAdd("d",-1,Date()),"mmddyyyy"),"Previous","C=
urrent");

it now works but i believe only because the dates are today and
yesterday. is there a way to incorporate MIN into the PIVOT iif
statement so i don't need to rely the DateAdd being the right number?
0
pat67
3/3/2010 7:22:59 PM
On Mar 3, 2:22=A0pm, pat67 <pbus...@comcast.net> wrote:
> On Mar 3, 1:43=A0pm, pat67 <pbus...@comcast.net> wrote:
>
>
>
>
>
> > On Mar 3, 11:28=A0am, pat67 <pbus...@comcast.net> wrote:
>
> > > On Mar 3, 11:13=A0am, KARL DEWEY <KARLDE...@discussions.microsoft.com=
>
> > > wrote:
>
> > > > Try this --
> > > > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Ope=
n]
> > > > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor na=
me]
> > > > FROM tbl_Move_Out_History
> > > > WHERE [tbl_Move_Out_History].[Extraction Date] Between
> > > > DateSerial(Year(Date()), Month(Date()),0) AND DateSerial(Year(Date(=
)),
> > > > Month(Date())-2,1)
> > > > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor =
name]
> > > > PIVOT IIF(Format([tbl_Move_Out_History].[Extraction Date], "yyyymm"=
) =3D
> > > > Format(DateAdd("m", -1, Date()), "yyyymm"), "Current", "Previous");
>
> > > > --
> > > > Build a little, test a little.
>
> > > > "pat67" wrote:
> > > > > On Mar 2, 4:52 pm, KARL DEWEY <KARLDE...@discussions.microsoft.co=
m>
> > > > > wrote:
> > > > > > Post the SQL of your crosstab.
> > > > > > --
> > > > > > Build a little, test a little.
>
> > > > > > "pat67" wrote:
> > > > > > > Not sure if my subject makes sense so I will explain. I need =
to create
> > > > > > > a query that shows open orders for vendors for 2 dates. Previ=
ous and
> > > > > > > Current. When i run a new query, the old current becomes the =
new
> > > > > > > previous and i add anew surrent. Right now i have 2 dates and=
 i used a
> > > > > > > crosstab query and that works fine becuase i can delete one d=
ate and
> > > > > > > add another. the issue is when i need to find the difference =
between
> > > > > > > the two dates. if i run it now the dates are say 2/1 and 3/1.=
 if i
> > > > > > > rerun the data on 4/1, the formulas i used to get the differe=
nce form
> > > > > > > 2/1 and 3/1 won't work. Long story short i need a way to have=
 2/1 say
> > > > > > > previous and 3/1 say current. Longwinded i know. hopefully so=
meone
> > > > > > > understands my ramblings
> > > > > > > .- Hide quoted text -
>
> > > > > > - Show quoted text -
>
> > > > > Here you go
>
> > > > > TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal O=
pen]
> > > > > SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor =
name]
> > > > > FROM tbl_Move_Out_History
> > > > > GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendo=
r
> > > > > name]
> > > > > PIVOT tbl_Move_Out_History.[Extraction Date];
>
> > > > > .- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I ran that and the result was blank. Vendor and Vendor Name fields
> > > only and no data. Is it because the date format you have is yyymm and
> > > i have mdyyy?- Hide quoted text -
>
> > > - Show quoted text -
>
> > Also, i run this every week so the fact your query is looking for last
> > month might be an issue?- Hide quoted text -
>
> > - Show quoted text -
>
> Letme explain further. The table is made from a query that deletes the
> oldest date and adds a new date. so for instance yesterday the data
> was from 3/1 and 3/2 and today it is from 3/2 and 3/3. There will
> always only be 2 dates Normally about a week a part
>
> i changed the sql to this
>
> TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
> SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
> FROM tbl_Move_Out_History
> WHERE (((tbl_Move_Out_History.[Extraction Date]) Between Date()+1 And
> Date()-10))
> GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
> name]
> PIVOT IIf(Format([tbl_Move_Out_History].[Extraction
> Date],"mmddyyyy")=3DFormat(DateAdd("d",-1,Date()),"mmddyyyy"),"Previous",=
"Cur=ADrent");
>
> it now works but i believe only because the dates are today and
> yesterday. is there a way to incorporate MIN into the PIVOT iif
> statement so i don't need to rely the DateAdd being the right number?- Hi=
de quoted text -
>
> - Show quoted text -

Ok I got it. I changed the pivot iif statement to look for the
extraction date equal to today since these will run at the same time,
put that in current and all else in previous. looks like this

TRANSFORM Sum(tbl_Move_Out_History.[Total Open]) AS [SumOfTotal Open]
SELECT tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor name]
FROM tbl_Move_Out_History
WHERE (((tbl_Move_Out_History.[Extraction Date]) Between Date()+1 And
Date()-10))
GROUP BY tbl_Move_Out_History.Vendor, tbl_Move_Out_History.[Vendor
name]
PIVOT IIf([tbl_Move_Out_History].[Extraction
Date]=3DDate(),"Current","Previous");


It's working so i will keep testing. if i run into any problems i will
post again. Thanks for the help.
0
pat67
3/3/2010 7:34:40 PM
Reply:

Similar Artilces:

Change display name #2
Hello, Currently we're hosting 4 domains. To keep everything organized I've added the location name to the users' display name. Example: LOC1_Bob Smith, LOC2_Tom Smith. This is something we want to show in the users' To: box when using outlook. The drawback is when emails are sent to the "outside" those recipients see that display name too. Is there another/better way of doing this? Thank you, Chris Pirillo <chrisp516@gmail.com> wrote: > Hello, > > > > Currently we're hosting 4 domains. To keep everything organized I've > ...

chart templates
Hello, sorry for not knowing the exact english terms as I am using the german versions of Office. In Office 2007 chart templates exists. But I have not found the setting to change the standard folder where they are saved. Right now it is C:\Documents and settings\USERNAME \Anwendungsdaten(?)\Microsoft\Templates\Charts. How can I change that path? Only in Word 2007 you can set the standard folders. But I haven't found the appropriate setting there! I want to set the standard folder to the server so every user can use the same chart templates! If I put a template file (*.crtx) on the Se...

Unexpected 'Save Changes in Book1' prompt
Running Office 2000 Whenever I exit a workbook I now get the following prompt: 'do you want to save the changes you made to Book 1' I've repaired my Office installation, but this had no effect. Any ideas? There are some worksheet functions that are volatile--that are recalculated when excel recalcs. AREAS() CELL() INDIRECT() ROWS() COLUMNS() NOW() TODAY() RAND() Any of these functions in formulas could make excel think your workbook has changed. Glynn Taylor wrote: > > Running Office 2000 > > Whenever I exit a w...

Changing newsgroup servers...how?
I have been trying without success to access news groups on news.individual.net... but I keep getting an error message Outlook express was unable to switch to newsgroup***** on the server news.individual.net. 480 Authentication required Configuration Account. news.individual.net Server news.individual.net Any suggestions please Andy The news server requires you to supply a userid and password in order to access it. Did you configure the account properties correctly? "laroffie" <laroffie@wanadoo.fr> wrote in message news:cfo5v7$f1b$1@news-reader1.wanadoo.fr... &g...

The ability to change multiple activity due dates at once
I would find it extremely helpful when I have multiple activities that are due on the same date and I want to change the due date to be able to change them all at once rather than having to open each one separately and then changing the due date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&...

Date numbering out of order in Outlook 07
Hi guys, I am a function organiser and within Outlook I have mail files for each event I am running. So for example, I would have a mail folder set up that looks like this: 12 December 1 Dec Pharmacy Guild 6 Dec 15 Dec Event Central 25 Dec Christmas Day But what I find is that Outlook puts things in a different order than I would expect and so I have ended up with this: [IMG]http://i14.tinypic.com/6kz8umq.jpg[/IMG] Is there a work around (other than renaming every sub-folder a-z)? Thanks in advance for help on sorting in order, Jo Actually, none of your sub-folders a...

Add time to a date time group
Hi, thanks for any help in advance I have my date and time displayed in this format dd hhmm "K" mmm yy which shows up as 03 1800 K Sep 08. I would like a formula that will add 30 min to this time group so it becomes 03 1830 K Sep 08 Assume your date/time is in A1, then you can do this in another cell: =3DA1+30/24/60 Format the cell in the same way as A1 (using the Format Painter icon). Times are stored in Excel as fractions of a 24-hour day, hence the divisions by 24 and 60. Hope this helps. Pete On Sep 4, 12:28=A0am, Patrick <Patr...@discussions.microsoft.com> wr...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

Receivable Document Date
I noticed for a past transaction that a receivable was post dated while the posting date was the correct date. What is the best way to correct this? Can I do this through the database? Lynn, The best way to do this is to void the original transaction and re- enter it. This will provide a complete audit trail. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com What is your Posting Setup? Is it "Post To GL" or "Post Thru' GL"? In case of "Post To GL", it is possible as the posting of Receivable could be in a past date and t...

Dating 6 events for 5 groups on a 18 month cycle
I have been trying tocalendarise 6 key dates in a cycle for 5 groups Each group A,B,and C all have different start dates to their cycle in the 18 month period - after that time it all repeats itself. Is there anyway this can be done I also need to find some way of labelling these key dates - Thanks no Denise - Despite Colin's certainty that it can't be done, I suspect all it requires is a better statement of your problem. Are you talking about a Gantt chart? If so, check out some of the links on this web page: http://peltiertech.com/Excel/Charts/GanttLinks.html - Jon ------- ...

auto email if cell value changed
I'm using a workbook to schedule staff. Each month is a worksheet then I have some counts that are done. The days of the month are in the top row and the names of the staff in the first column. We've set up autorefresh on web pages so the schedule is always available but... people still don't notice shift changes. I want excel to generate an email to the person (we're running exchange too) if a cell value is changed. I was thinking of a couple of steps. 1. So they only get emails about the current month and the month ahead -- Have a cell at the top with YES/NO in i...

how can i format a date that uses "1st" or "2nd" or "3rd" or 4th".
how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am working on a program that generates certificates. i want to use dates such as "3rd of April 2010". when i run my program the words that appears onthe screen was 3-Apr-10. can you help me solve this problem? On Mon, 5 Apr 2010 20:47:01 -0700, cristin wrote: > how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am > working on a program that generates certificates. i want to use dates such > as &q...

Bar graph with percentage of total marked inside each bar
I'm trying to create a chart in Excel but I can't seem to figure out how to make the data work for me. I need to create a bar graph with each bar with a "goal" amount, and the percentage of the goal reached within the bar. There are two values where the goal has been exceeded and I wanted to reverse the chart for this - mark the total raised percent and the total of the goal within the bar. It's hard for me to explain without visuals but I seem to be getting somewhat close to this, I just can't quite make it work. Any advice? ...

start and end date of given date
Hi All, What's the easiest way to get the start and end date for a given date. The Mon thru Sun dates. Thanks, rodchar You can use known Monday and Sunday dates and calculate based on current date (or any parameter date): -- '20010101' is known Monday -- '20010107' is known Sunday SELECT DATEADD(DAY, (DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP) / 7) * 7, '20010101') AS monday_for_date, DATEADD(DAY, (DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP) / 7) * 7, '20010107') AS sunday_for_date; -- Plamen Ratchev http://www.SQ...

Help needed on date()
Can anybody please tell me if, and how, I can use the date() funtion in excel for dates lying before 1900. The problem is that I'm trying to fill up columns with dates from a 19th century journal. thanks in advance D. Gerling Take a look at the following: http://j-walk.com/ss/excel/files/xdate.htm MRO "Delano Gerling" <delano@gerling.demon.nl> wrote in message news:vm99477u8k7mdc@corp.supernews.com... > Can anybody please tell me if, and how, I can use the date() funtion in > excel for dates lying before 1900. > > The problem is that I'm trying to f...

Hide Change Sales Stage
Is it possible to hide the change sales stage option in opportunity so the stages can not be changed manually? You can prevent the user from changing stage or applying rule by removing create privilege from Process Instance entity, form the user security role. -- This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use. © 2005 Microsoft Corporation. All rights reserved. "MC_Develop" wrote: > Is it possible to hide the change sales stage option in opportunity so the > stages can not be changed manually...

changing margins
This is probably a really stupid question, but how the heck do I change the page margins? I'm making a flyer, and it looks squished into the middle, with well over an inch of ugly, empty white space around everything. Thanks! By the way, I'm using Publisher 2000, and I didn't start with a design template or use the wizard or anything. "J Brydle" wrote: > This is probably a really stupid question, but how the heck do I change the page margins? I'm making a flyer, and it looks squished into the middle, with well over an inch of ugly, empty white space around every...

change range of relative formulas to absolute
Excel 2003. I'd like to change an entire range of cell formulas from relative to absolute. Mark You would need VBA to make global changes to cell references. Here are four........ Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) ...

Change dollar amt to written form
Is there a formula on changing numerical dollar amt to their written-out form? If there is, can you tell me? Hi, There are several versions of this available, I like this one http://www.ozgrid.com/VBA/ValueToWords.htm Mike "Shihachi" wrote: > Is there a formula on changing numerical dollar amt to their written-out > form? If there is, can you tell me? There is: http://support.microsoft.com/default.aspx/kb/213360 Micky "Shihachi" wrote: > Is there a formula on changing numerical dollar amt to their written-out > form? If there i...

Changing Bill Account Number
I am trying to change the account number that coincides with a telephone bill. (The account number changed when I moved). But when I go into the account details and click the link for 'Account number', the account number field is grayed out and not editable. I've been paying this bill online using a debit card for the past few months because I cannot change this number. Back in October, when they reassigned my old phone number to somebody new, my bill was payed to the wrong account. (I am still fighting this too). Why is it so hard to change bill account numbers in this...

Filtering rows with tracked changes
Hi I am trying to work out how to filter the rows that have been updated over a period of a couple of days. I understand that you can highlight records with tracked changes in a certain time period � but I would like to be able to just have those rows that have highlighted cells to look at. Any ideas out there? You can list changes on a new sheet but it doesn't allow you to see the whole row. Cheers, Mike P.s � is it possible to highlight changes between dates � I can only see highlighting changes since � ...

Change Legend Entry label of "Total" to actual name of field.
I have two pivot charts. One has multiple criteria and when I create a pivot chart, each series field breaks out properly using the names provided in the multiple criteria given. Now, the issue is when I have a single (one) field name, the chart automatically has the legend entry of "Total" since I am using the 'sum of' option. I've manually changed the name of the field in the pivot table, but the label on the chart remains the same. I've go so far as to read several tips on going to the source data to change this but of course when working with pivot tables, you ca...

Request between week ending date
I am currently using a query that request a "start date" and an "end date", I would like to change the request to "Week ending date" and include information 7 day back. What would I have to change to get this information? In the Criteria row under your date field, you currently have: Between [start date] And [end date] Try: Between [week ending date] - 6 And [week ending date] It is always a good idea to declare date parameters. In query design view, choose Parameters on the Query menu. Enter into the dialog: [week ending date] Date...

How do I Change the Column Names (Re-Name A -
I want to Change the Column Headings from letters to actual names. How do I do that? hi this can not be done. excel needs these to find/remember where everthing is on the spreadsheet grid. you can change the letters to numbers but that is it. on the 2003 menu bar... tools>options>general tab. check R1C1 reference style. or you can get rid of the letters all togeather. on the 2003 menu bar.... tools>options>view tab>uncheck row & column headers. but they are still there, just not visible. usually when someone want to wants to get rid of the column header letters, they u...

change asset lable if asset are already retired
Hi, Can I remove the value in Asset_Label field for FA00100 if the Asset is already retired because retired assets cannot be seen in the Asset General Information? Try it in your test system and let us know :-) Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "mel" wrote: > Hi, > > Can I remove the value in Asset_Label field for FA00100 if the Asset is > already retired because retired assets cannot be seen in the Asset General...