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? |
Thread Tools | |
Display Modes | |
|
|