I have a simple table with month-year names for column headers. After each
month, a new column will have to be added for that month's data. I would
like to be able to prompt the user to enter the last date in the month
reporting and have access automatically create a new column for the user to
start entering the data.

Thanks!

You are thinking like an Excel spreadsheet. Access is a relational database.
You should not have a colum per month.

You would have one column for the event and other columns that define
various factors of the event like who, what, where. The DateTime datatype
field will provide the when. You may have a start and and an end datetime
field.

Use a new record for each new datetime recording - not new field.

"Rachel" wrote:

> I have a simple table with month-year names for column headers. After each
> month, a new column will have to be added for that month's data. I would
> like to be able to prompt the user to enter the last date in the month
> reporting and have access automatically create a new column for the user to
> start entering the data.
>
> Thanks!

Rachel:

Your problem is that your table design is flawed. By having separate
columns for each year/month you are doing what's known as 'encoding data as
column headings'. In a relational database data should be stored as values
at column positions in rows in tables and in no other way.

Lets assume the table is called Transactions and records financial
transactions on a monthly basis. What you should have are columns such as
Transaction Year, TransactionMonth and Amount. Or you could just have a
TransactionDate instead of the separate year and month columns. The
Year/Month are easily extracted from the date. You'd probably also have a
TransactionID autonumber column as the primary key. You may well also have a
foreign key column such as TransactionTypeID which references the primary key
of a TransactionTypes table.

As you'll see this is a tall thin table rather than a short fat one, which
is usually a sign of a healthy table. When data for a new year/month needs
to be added all that's necessary is to enter new year and month values into
the TransactionYear and TransactionMonth columns (or the TransactionDate
column if you use that design).

As well as requiring no change to the table definition when data for a new
month is added this design makes querying the database very easy. To take a
simple example, say you need the sum of all transaction amounts grouped by
TransactionType for the first quarter of this year, assuming separate year
and month columns rather than a single date column, and the months are
entered as Jan, Feb, Mar etc, a query along these lines would do it:

SELECT TransactionType, SUM(Amount)
FROM Transactions INNER JOIN TransactionTypes
ON Transactions.TransactionTypeID = TransactionTpes.TransactionTypeID
WHERE TransactionYear = 2006
AND TransactionMonth IN("Jan","Feb","Mar")
GROUP BY TransactionType;

If a single TransactionDate column is used then the query could be any of
these:

SELECT TransactionType, SUM(Amount)
FROM Transactions INNER JOIN TransactionTypes
ON Transactions.TransactionTypeID = TransactionTpes.TransactionTypeID
WHERE DATEPART("q",TransactionDate) = 1
GROUP BY TransactionType;

or:

SELECT TransactionType, SUM(Amount)
FROM Transactions INNER JOIN TransactionTypes
ON Transactions.TransactionTypeID = TransactionTpes.TransactionTypeID
WHERE Transaction Date >= #01/01/2006#
AND TransactionDate < #04/01/2006#
GROUP BY TransactionType;

or:

SELECT TransactionType, SUM(Amount)
FROM Transactions INNER JOIN TransactionTypes
ON Transactions.TransactionTypeID = TransactionTpes.TransactionTypeID
WHERE MONTH(TransactionDate) IN(1,2,3)
GROUP BY TransactionType;

Ken Sheridan
Stafford, England

"Rachel" wrote:

> I have a simple table with month-year names for column headers. After each
> month, a new column will have to be added for that month's data. I would
> like to be able to prompt the user to enter the last date in the month
> reporting and have access automatically create a new column for the user to
> start entering the data.
>
> Thanks!

On Fri, 17 Feb 2006 13:56:27 -0800, Rachel
<(E-Mail Removed)> wrote:

>I have a simple table with month-year names for column headers.

Then you have an *incorrectly designed table*.

You're "committing spreadsheet upon a database", a misdemenor
punishable by being forced to read the links at

http://home.bendbroadband.com/conrad...abaseDesign101

<g>

>After each
>month, a new column will have to be added for that month's data. I would
>like to be able to prompt the user to enter the last date in the month
>reporting and have access automatically create a new column for the user to
>start entering the data.

Seriously... a MUCH better normalized design uses two tables, one with
the non-date specific information in this table (including a Primary
Key field), related one-to-many to a table with that ID, a Date/Time
field (containing #1/1/2006#, #2/1/2006# and so on in different
records), and a field for whatever you were storing in the "Jan2006"
field.

John W. Vinson[MVP]