A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

changing totals as a date changes



 
 
Thread Tools Display Modes
  #1  
Old March 2nd, 2010, 09:43 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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
  #2  
Old March 2nd, 2010, 10:52 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default changing totals as a date changes

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
.

  #3  
Old March 3rd, 2010, 02:49 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default changing totals as a date changes

On Mar 2, 4:52*pm, KARL DEWEY
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];


  #4  
Old March 3rd, 2010, 05:13 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default changing totals as a date changes

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
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];


.

  #5  
Old March 3rd, 2010, 05:28 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default changing totals as a date changes

On Mar 3, 11:13*am, KARL DEWEY
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
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?
  #6  
Old March 3rd, 2010, 07:39 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default changing totals as a date changes

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
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
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?
.

  #7  
Old March 3rd, 2010, 07:43 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default changing totals as a date changes

On Mar 3, 11:28*am, pat67 wrote:
On Mar 3, 11:13*am, KARL DEWEY
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
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?- 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?
  #8  
Old March 3rd, 2010, 08:22 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default changing totals as a date changes

On Mar 3, 1:43*pm, pat67 wrote:
On Mar 3, 11:28*am, pat67 wrote:





On Mar 3, 11:13*am, KARL DEWEY
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
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?- 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")=Format(DateAdd("d",-1,Date()),"mmddyyyy"),"Previous","Current");

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?
  #9  
Old March 3rd, 2010, 08:34 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default changing totals as a date changes

On Mar 3, 2:22*pm, pat67 wrote:
On Mar 3, 1:43*pm, pat67 wrote:





On Mar 3, 11:28*am, pat67 wrote:


On Mar 3, 11:13*am, KARL DEWEY
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
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?- 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")=Format(DateAdd("d",-1,Date()),"mmddyyyy"),"Previous","Cur*rent");

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?- Hide 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]=Date(),"Current","Previous");


It's working so i will keep testing. if i run into any problems i will
post again. Thanks for the help.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.