changing totals as a date changes

P

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
 
P

pat67

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





- Show quoted text -
Click to expand...

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

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.


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





- Show quoted text -
Click to expand...

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


.
Click to expand...
 
P

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
Click to expand...
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];
Click to expand...
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


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

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.


pat67 said:
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 said:
On Mar 2, 4:52 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
Post the SQL of your crosstab.
Click to expand...
:
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 -
Click to expand...
- Show quoted text -
Click to expand...
Here you go
Click to expand...
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];
Click to expand...
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


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?
.
Click to expand...
 
P

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");
Click to expand...
pat67 said:
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.
:
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 -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

Also, i run this every week so the fact your query is looking for last
month might be an issue?
 
P

pat67

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.
:
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.
:
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 -
Click to expand...
Click to expand...
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 -
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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

pat67

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.
:
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.
:
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 -
Click to expand...
Click to expand...
Also, i run this every week so the fact your query is looking for last
month might be an issue?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
How can the font and size of (all) citation numbers be altered? 1
Adding Network Trusted Location on MS O365 Word per Computer Registry Setting 1
Finding the proper style field and the numbered paragraph value within the style 0
Finding the right style field in VBA 0
How to change font & size of (all) citation numbers? 7
Link a text box based on drop down box with four options MS Word 365 0
List all tables, including document structure 0

Top