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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|