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
|
|||
|
|||
Columns based on Date
Not sure if I've started this project the right way, so please jump in and
offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? Thanks in advance!! |
#2
|
|||
|
|||
Columns based on Date
Hello Jennifer.
"jenniferspnc" wrote: Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? The good answer is: it is possible. The bad answer is: I can't tell you how, because you didn't tell us how the information "July 06" is stored. If it was a date field: Use the criterion = DateAdd("yyyy",-1,Date()). If not... -- Regards, Wolfgang |
#3
|
|||
|
|||
Columns based on Date
Sorry, that is important information missing. Well I have columns labeled,
July 06, August 06, September 06, etc; however, the data in these columns is currency. So I want my query to know to pull in the last 12 months (columns) of data. Each month I'll run the query to know the total for the prior 12 months, thus why I need something to pull it in or I'll have to recreate the wheel each month. Impossible I bet huh? I just wonder if I built my table out right so to make it happen. Customer July 06 GP August 06 GP September 06 GP abc $1,234 $2,345 $3,456 "Wolfgang Kais" wrote: Hello Jennifer. "jenniferspnc" wrote: Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? The good answer is: it is possible. The bad answer is: I can't tell you how, because you didn't tell us how the information "July 06" is stored. If it was a date field: Use the criterion = DateAdd("yyyy",-1,Date()). If not... -- Regards, Wolfgang |
#4
|
|||
|
|||
Columns based on Date
I have columns for each month dating all the way back to July 06.
You table structure is a spreadsheet like Excel and not as a relational database should be. This is how it should be -- DataID - primary key - autonumber Facet_1 - text Facet_2 - text Facet_Date - DateTime Mon_Total - number - integer, single, double, - based on the precission you need. Use an append query to pull data from existing table into a record per month. This should work but I did not build tables and try. INSERT INTO New_Table [X], [Y], [Mon_Total], [Facet_Date] SELECT [X], [Y], [July 06] AS [Mon_Total], #1/7/2006# AS [Facet_Date] FROM [Old_Table]; Then you pull your data using criteria =DateAdd("yyyy", -1,Date()) for the last year. -- KARL DEWEY Build a little - Test a little "jenniferspnc" wrote: Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? Thanks in advance!! |
#5
|
|||
|
|||
Columns based on Date
Your table design is wrong. You are already discovering this as you are
having trouble querying data from multiple columns. Also Access has a limit of a maximum of 255 columns in a table. Often you run out of columns way before that. Therefore you only have room for about 20 years of data at best. Your table should look something like: Customer GP_Date GP_Amount abc 1 Jul 2006 $1,234 abc 1 Aug 2006 $2,345 abc 1 Sep 2006 $3,456 abc 1 Jul 2006 $5,234 abc 1 Aug 2006 $2,322 abc 1 Sep 2006 $3,543 Then you could create a query on the GP_Date column with criteria something like Date() -365 or DateAdd("m",-13,Date()) -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jenniferspnc" wrote: Sorry, that is important information missing. Well I have columns labeled, July 06, August 06, September 06, etc; however, the data in these columns is currency. So I want my query to know to pull in the last 12 months (columns) of data. Each month I'll run the query to know the total for the prior 12 months, thus why I need something to pull it in or I'll have to recreate the wheel each month. Impossible I bet huh? I just wonder if I built my table out right so to make it happen. Customer July 06 GP August 06 GP September 06 GP abc $1,234 $2,345 $3,456 "Wolfgang Kais" wrote: Hello Jennifer. "jenniferspnc" wrote: Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? The good answer is: it is possible. The bad answer is: I can't tell you how, because you didn't tell us how the information "July 06" is stored. If it was a date field: Use the criterion = DateAdd("yyyy",-1,Date()). If not... -- Regards, Wolfgang |
#6
|
|||
|
|||
Columns based on Date
I did import a spreadsheet that a previous employee built, thus why I kept
it, to avoid having to retype everything in. Would there be an easy way to import the data into a new table with a different layout? So you say I should not have it built this way...unsure of your example. Should it appear like my example? Customer Date GP Total abc 7/1/06 $10,000.00 abc 8/1/06 $ 5,000.00 Am I understanding right? "KARL DEWEY" wrote: I have columns for each month dating all the way back to July 06. You table structure is a spreadsheet like Excel and not as a relational database should be. This is how it should be -- DataID - primary key - autonumber Facet_1 - text Facet_2 - text Facet_Date - DateTime Mon_Total - number - integer, single, double, - based on the precission you need. Use an append query to pull data from existing table into a record per month. This should work but I did not build tables and try. INSERT INTO New_Table [X], [Y], [Mon_Total], [Facet_Date] SELECT [X], [Y], [July 06] AS [Mon_Total], #1/7/2006# AS [Facet_Date] FROM [Old_Table]; Then you pull your data using criteria =DateAdd("yyyy", -1,Date()) for the last year. -- KARL DEWEY Build a little - Test a little "jenniferspnc" wrote: Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? Thanks in advance!! |
#7
|
|||
|
|||
Columns based on Date
So I rebuilt the table structure as you suggested.
I tried building a query with that Date Range...Works fine based on the little amt of data I have inputted. Two questions and I'll be out of your hair. Is there an easy way to import that data from that spreadsheet since it doesn't follow my layout in Access...assuming no is the answer. Secondly, on that query it still pulls in the individual records. When I was building the query I selected for it to Sum the totals and Group by Customer. It's not working. I want it to show the total per customer for those prior twelve months...not the individual lines of all that data detail. Thank you all. It's been very helpful to know where my errors were before I got too far along!! "Jerry Whittle" wrote: Your table design is wrong. You are already discovering this as you are having trouble querying data from multiple columns. Also Access has a limit of a maximum of 255 columns in a table. Often you run out of columns way before that. Therefore you only have room for about 20 years of data at best. Your table should look something like: Customer GP_Date GP_Amount abc 1 Jul 2006 $1,234 abc 1 Aug 2006 $2,345 abc 1 Sep 2006 $3,456 abc 1 Jul 2006 $5,234 abc 1 Aug 2006 $2,322 abc 1 Sep 2006 $3,543 Then you could create a query on the GP_Date column with criteria something like Date() -365 or DateAdd("m",-13,Date()) -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jenniferspnc" wrote: Sorry, that is important information missing. Well I have columns labeled, July 06, August 06, September 06, etc; however, the data in these columns is currency. So I want my query to know to pull in the last 12 months (columns) of data. Each month I'll run the query to know the total for the prior 12 months, thus why I need something to pull it in or I'll have to recreate the wheel each month. Impossible I bet huh? I just wonder if I built my table out right so to make it happen. Customer July 06 GP August 06 GP September 06 GP abc $1,234 $2,345 $3,456 "Wolfgang Kais" wrote: Hello Jennifer. "jenniferspnc" wrote: Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? The good answer is: it is possible. The bad answer is: I can't tell you how, because you didn't tell us how the information "July 06" is stored. If it was a date field: Use the criterion = DateAdd("yyyy",-1,Date()). If not... -- Regards, Wolfgang |
#8
|
|||
|
|||
Columns based on Date
Your layout is correct.
-- KARL DEWEY Build a little - Test a little "jenniferspnc" wrote: I did import a spreadsheet that a previous employee built, thus why I kept it, to avoid having to retype everything in. Would there be an easy way to import the data into a new table with a different layout? So you say I should not have it built this way...unsure of your example. Should it appear like my example? Customer Date GP Total abc 7/1/06 $10,000.00 abc 8/1/06 $ 5,000.00 Am I understanding right? "KARL DEWEY" wrote: I have columns for each month dating all the way back to July 06. You table structure is a spreadsheet like Excel and not as a relational database should be. This is how it should be -- DataID - primary key - autonumber Facet_1 - text Facet_2 - text Facet_Date - DateTime Mon_Total - number - integer, single, double, - based on the precission you need. Use an append query to pull data from existing table into a record per month. This should work but I did not build tables and try. INSERT INTO New_Table [X], [Y], [Mon_Total], [Facet_Date] SELECT [X], [Y], [July 06] AS [Mon_Total], #1/7/2006# AS [Facet_Date] FROM [Old_Table]; Then you pull your data using criteria =DateAdd("yyyy", -1,Date()) for the last year. -- KARL DEWEY Build a little - Test a little "jenniferspnc" wrote: Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? Thanks in advance!! |
#9
|
|||
|
|||
Columns based on Date
The first part is a little work. You could import the data one column at a
time from the old table to the new one. Below is an example of such a query. INSERT INTO TheOldTable ( Customer, GP_Date, GP_Amount ) SELECT TheNewTable.Customer, #6/1/2006# AS GP_Date, TheNewTable.[July 06 GP] FROM TheNewTable; After running it would would have to change the date within the #'s to that of the matching field. For example the above is #6/1/2006# and [July 06 GP]. You would need to change them to #7/1/2006# and [August 06 GP]. Also I noticed in another post that you were going to name a field in the new table "Date". Date is a reserved word and you might run into trouble with using it. That's why I suggested something like GP_Date instead. As for the second part, you are probably still grouping by the date field. Try changing it from Group By to Where. The resulting SQL should look something like this: SELECT jenniferspnc2.Customer, Sum(jenniferspnc2.GP_Amount) AS SumOfGP_Amount FROM jenniferspnc2 WHERE (((jenniferspnc2.GP_Date)DateAdd("yyyy",-1,Date()))) GROUP BY jenniferspnc2.Customer; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jenniferspnc" wrote: So I rebuilt the table structure as you suggested. I tried building a query with that Date Range...Works fine based on the little amt of data I have inputted. Two questions and I'll be out of your hair. Is there an easy way to import that data from that spreadsheet since it doesn't follow my layout in Access...assuming no is the answer. Secondly, on that query it still pulls in the individual records. When I was building the query I selected for it to Sum the totals and Group by Customer. It's not working. I want it to show the total per customer for those prior twelve months...not the individual lines of all that data detail. Thank you all. It's been very helpful to know where my errors were before I got too far along!! "Jerry Whittle" wrote: Your table design is wrong. You are already discovering this as you are having trouble querying data from multiple columns. Also Access has a limit of a maximum of 255 columns in a table. Often you run out of columns way before that. Therefore you only have room for about 20 years of data at best. Your table should look something like: Customer GP_Date GP_Amount abc 1 Jul 2006 $1,234 abc 1 Aug 2006 $2,345 abc 1 Sep 2006 $3,456 abc 1 Jul 2006 $5,234 abc 1 Aug 2006 $2,322 abc 1 Sep 2006 $3,543 Then you could create a query on the GP_Date column with criteria something like Date() -365 or DateAdd("m",-13,Date()) -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jenniferspnc" wrote: Sorry, that is important information missing. Well I have columns labeled, July 06, August 06, September 06, etc; however, the data in these columns is currency. So I want my query to know to pull in the last 12 months (columns) of data. Each month I'll run the query to know the total for the prior 12 months, thus why I need something to pull it in or I'll have to recreate the wheel each month. Impossible I bet huh? I just wonder if I built my table out right so to make it happen. Customer July 06 GP August 06 GP September 06 GP abc $1,234 $2,345 $3,456 "Wolfgang Kais" wrote: Hello Jennifer. "jenniferspnc" wrote: Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? The good answer is: it is possible. The bad answer is: I can't tell you how, because you didn't tell us how the information "July 06" is stored. If it was a date field: Use the criterion = DateAdd("yyyy",-1,Date()). If not... -- Regards, Wolfgang |
#10
|
|||
|
|||
Columns based on Date
Hello Jennifer.
"jenniferspnc" wrote; Not sure if I've started this project the right way, so please jump in and offer suggestions. Basically we track monthly totals. I have columns for each month dating all the way back to July 06. I want a query that will pull in only the past twelve months data from those columns. I don't want to delete those columns of data because I may need them later, but I only want my query to pull in the prior 12 months columns. Is this possible or will I have to pull in the selected prior 12 months into my query each time? The good answer is: it is possible. The bad answer is: I can't tell you how, because you didn't tell us how the information "July 06" is stored. If it was a date field: Use the criterion = DateAdd("yyyy",-1,Date()). If not... Sorry, that is important information missing. Well I have columns labeled, July 06, August 06, September 06, etc; however, the data in these columns is currency. So I want my query to know to pull in the last 12 months (columns) of data. Each month I'll run the query to know the total for the prior 12 months, thus why I need something to pull it in or I'll have to recreate the wheel each month. Impossible I bet huh? I just wonder if I built my table out right so to make it happen. Customer July 06 GP August 06 GP September 06 GP abc $1,234 $2,345 $3,456 As others stated before me: A table with Customer, Date and GP Total columns would be the best way to store the information in as Access database, and it would be easy to get the result you want. The hard thing is to answer "how to get the excel data into the table?". Someone will have to write code that reads the excel spreadsheet line by line and insert a new record in the table for every currency value read. For adding records to the table, you could post a question in the microsoft.public.access.modulesdaovba group. -- Regards, Wolfgang |
Thread Tools | |
Display Modes | |
|
|