changing totals as a date changes

[pat67]

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

 

[KARL DEWEY]

Post the SQL of your crosstab.

 

[pat67]

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





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

 

[KARL DEWEY]

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.

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





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


.

 

[pat67]

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.

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?

 

[KARL DEWEY]

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.

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.

On Mar 2, 4:52 pm, KARL DEWEY <[email protected]>
wrote:
Post the SQL of your crosstab.

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

 

[pat67]

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");

On Mar 2, 4:52 pm, KARL DEWEY <[email protected]>
wrote:
Post the SQL of your crosstab.
--
Build a little, test a little.
:
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/1say
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?

 

[pat67]

On Mar 3, 11:13 am, KARL DEWEY <[email protected]>
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.
:
On Mar 2, 4:52 pm, KARL DEWEY <[email protected]>
wrote:
Post the SQL of your crosstab.
--
Build a little, test a little.
:
Not sure if my subject makes sense so I will explain. I need tocreate
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 iused 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?

 

[pat67]

On Mar 3, 11:13 am, KARL DEWEY <[email protected]>
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.
:
On Mar 2, 4:52 pm, KARL DEWEY <[email protected]>
wrote:
Post the SQL of your crosstab.
--
Build a little, test a little.
:
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 andi 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 have2/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.