Columns based on Date

[Guest]

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!!

 

[Wolfgang Kais]

Hello Jennifer.

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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?

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

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!!

 

[Guest]

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!!

 

[Guest]

Your layout is correct.
--
KARL DEWEY
Build a little - Test a little

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?

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

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!!

 

[Guest]

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;

 

[Wolfgang Kais]

Hello Jennifer.

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.