Access Novice

[Terry Bennett]

Sorry if this is a really basic question. I am quite conversant with Excel
but have little experience with Access.

Not sure if there is a better way of doing this ...

I have an Excel workbook comprising several sheets. The main sheet on which
I
input data lists projects that are started and finished throughout the year.
In simple terms, the columns are: Date Started, Project Owner, Brief
Description, Date Conpleted, etc. I then filter the Date Completed column
so that only 'live' projects are showing and, on other sheets, have various
data analysis.

One further column on the inputs sheet details progress on each project -
every time something happens this is updated. I enter this simply by
creating a new line each time a new event occurs using Alt+Enter and then
prefixing each line with a '-'. This is fine but for some larger projects
there can be 30, 40 or 50+ entries so the cell in which all this is entered
starts to get somewhat 'crowded' and sometimes won't even show everything on
the screen (I have to click within the cell and then move down using the
arrow keys).

Although everything works OK, I'm conscious that the spreadsheet is getting
bigger and bigger (currently c 1.2 Mb) and I wonder whether there is a
simpler way of storing all of the 'progress' data whilst retaining the basic
format of things. I don't know much about Access - should I be using this
instead?

Any suggestions will be welcomed!

Thanks.

 

[Joseph Meehan]

Sorry if this is a really basic question. I am quite conversant with
Excel but have little experience with Access.

Not sure if there is a better way of doing this ...

I have an Excel workbook comprising several sheets. The main sheet
on which I
input data lists projects that are started and finished throughout
the year. In simple terms, the columns are: Date Started, Project
Owner, Brief Description, Date Conpleted, etc. I then filter the
Date Completed column so that only 'live' projects are showing and,
on other sheets, have various data analysis.

One further column on the inputs sheet details progress on each
project - every time something happens this is updated. I enter this
simply by creating a new line each time a new event occurs using
Alt+Enter and then prefixing each line with a '-'. This is fine but
for some larger projects there can be 30, 40 or 50+ entries so the
cell in which all this is entered starts to get somewhat 'crowded'
and sometimes won't even show everything on the screen (I have to
click within the cell and then move down using the arrow keys).

Although everything works OK, I'm conscious that the spreadsheet is
getting bigger and bigger (currently c 1.2 Mb) and I wonder whether
there is a simpler way of storing all of the 'progress' data whilst
retaining the basic format of things. I don't know much about Access
- should I be using this instead?

Any suggestions will be welcomed!

Thanks.

That would be an ideal project for Access and you will get started right
with a relational subject. " All those " a new line each time a new event
occurs using Alt+Enter and then prefixing each line with a '-'. This is
fine but for some larger projects there can be 30, 40 or 50+ entries " All
those entries would be in a second table. It would be a child table to the
parent so each record in the parent could have many many children each entry
(Alt+Enter and then prefixing each line with a '-') being a separate record.
It is called normalization.

 

[Graham Mandeno]

Hi Terry

In Access, you would create a separate table for your progress details, and
relate that table to your projects table with a one-to-many relationship.
Each *line* in your Excel cell would be implemented as a *record* in your
ProgressDetails table.

The Projects table would need to have a "primary key" which is a unique
value to identify each project. If you don't already have unique project
numbers, then you could just add a field named "ProjectID" and make it an
autonumber field.

Your ProgressDetails table needs a "foreign key", which is a field of the
same type as the related primary key (autonumbers are numeric long integers,
so that is what you would use for the foreign key).

So, it needs three fields:
ProjectID (number, long integer, required)
Timestamp (date/time, default value =Now() )
Details (text or memo)

The timestamp is required to ensure the records are in the correct order,
and would also be useful to query, for example, "which projects have had
activity in the last month?"

Data entry should be done with a form (Projects) and a subform
(ProgressDetails). Check out the sample NorthWind database for examples of
setting up forms and subforms.

 

[Terry Bennett]

Graham/Joseph. Thanks for your advice. What you have said confirms that I
should really convert this to an Access database at some stage. Can't say I
really follow all the finer points so I will try and find somebody locally
whoc can do some hand-holding when I do the conversion.

Thanks again.

Hi Terry

In Access, you would create a separate table for your progress details,
and relate that table to your projects table with a one-to-many
relationship. Each *line* in your Excel cell would be implemented as a
*record* in your ProgressDetails table.

The Projects table would need to have a "primary key" which is a unique
value to identify each project. If you don't already have unique project
numbers, then you could just add a field named "ProjectID" and make it an
autonumber field.

Your ProgressDetails table needs a "foreign key", which is a field of the
same type as the related primary key (autonumbers are numeric long
integers, so that is what you would use for the foreign key).

So, it needs three fields:
ProjectID (number, long integer, required)
Timestamp (date/time, default value =Now() )
Details (text or memo)

The timestamp is required to ensure the records are in the correct order,
and would also be useful to query, for example, "which projects have had
activity in the last month?"

Data entry should be done with a form (Projects) and a subform
(ProgressDetails). Check out the sample NorthWind database for examples
of setting up forms and subforms.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sorry if this is a really basic question. I am quite conversant with
Excel but have little experience with Access.

Not sure if there is a better way of doing this ...

I have an Excel workbook comprising several sheets. The main sheet on
which I
input data lists projects that are started and finished throughout the
year.
In simple terms, the columns are: Date Started, Project Owner, Brief
Description, Date Conpleted, etc. I then filter the Date Completed
column
so that only 'live' projects are showing and, on other sheets, have
various
data analysis.

One further column on the inputs sheet details progress on each project -
every time something happens this is updated. I enter this simply by
creating a new line each time a new event occurs using Alt+Enter and then
prefixing each line with a '-'. This is fine but for some larger
projects
there can be 30, 40 or 50+ entries so the cell in which all this is
entered
starts to get somewhat 'crowded' and sometimes won't even show everything
on
the screen (I have to click within the cell and then move down using the
arrow keys).

Although everything works OK, I'm conscious that the spreadsheet is
getting
bigger and bigger (currently c 1.2 Mb) and I wonder whether there is a
simpler way of storing all of the 'progress' data whilst retaining the
basic
format of things. I don't know much about Access - should I be using
this
instead?

Any suggestions will be welcomed!

Thanks.

 

[Graham Mandeno]

Hallelujah! Another convert <g>

Don't forget to come back here - we're mostly pretty good at hand-holding

 

[tina]

as this will be your first Access project, you'll find the going a lot
easier if you start off on the right foot. data normalization is not a
"finer point" when it comes to building an application, it's the basis of
your whole project. for more information, see
http://home.att.net/~california.db/tips.html, focusing first on tips 1 and
2, in that order.

and, as Graham said, come back to the newsgroups with specific questions -
the help you'll find here will amaze you.

hth

Graham/Joseph. Thanks for your advice. What you have said confirms that I
should really convert this to an Access database at some stage. Can't say I
really follow all the finer points so I will try and find somebody locally
whoc can do some hand-holding when I do the conversion.

Thanks again.

Hi Terry

In Access, you would create a separate table for your progress details,
and relate that table to your projects table with a one-to-many
relationship. Each *line* in your Excel cell would be implemented as a
*record* in your ProgressDetails table.

The Projects table would need to have a "primary key" which is a unique
value to identify each project. If you don't already have unique project
numbers, then you could just add a field named "ProjectID" and make it an
autonumber field.

Your ProgressDetails table needs a "foreign key", which is a field of the
same type as the related primary key (autonumbers are numeric long
integers, so that is what you would use for the foreign key).

So, it needs three fields:
ProjectID (number, long integer, required)
Timestamp (date/time, default value =Now() )
Details (text or memo)

The timestamp is required to ensure the records are in the correct order,
and would also be useful to query, for example, "which projects have had
activity in the last month?"

Data entry should be done with a form (Projects) and a subform
(ProgressDetails). Check out the sample NorthWind database for examples
of setting up forms and subforms.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sorry if this is a really basic question. I am quite conversant with
Excel but have little experience with Access.

Not sure if there is a better way of doing this ...

I have an Excel workbook comprising several sheets. The main sheet on
which I
input data lists projects that are started and finished throughout the
year.
In simple terms, the columns are: Date Started, Project Owner, Brief
Description, Date Conpleted, etc. I then filter the Date Completed
column
so that only 'live' projects are showing and, on other sheets, have
various
data analysis.

One further column on the inputs sheet details progress on each project -
every time something happens this is updated. I enter this simply by
creating a new line each time a new event occurs using Alt+Enter and then
prefixing each line with a '-'. This is fine but for some larger
projects
there can be 30, 40 or 50+ entries so the cell in which all this is
entered
starts to get somewhat 'crowded' and sometimes won't even show everything
on
the screen (I have to click within the cell and then move down using the
arrow keys).

Although everything works OK, I'm conscious that the spreadsheet is
getting
bigger and bigger (currently c 1.2 Mb) and I wonder whether there is a
simpler way of storing all of the 'progress' data whilst retaining the
basic
format of things. I don't know much about Access - should I be using
this
instead?

Any suggestions will be welcomed!

Thanks.

 

[Terry Bennett]

Thanks Tina

as this will be your first Access project, you'll find the going a lot
easier if you start off on the right foot. data normalization is not a
"finer point" when it comes to building an application, it's the basis of
your whole project. for more information, see
http://home.att.net/~california.db/tips.html, focusing first on tips 1 and
2, in that order.

and, as Graham said, come back to the newsgroups with specific questions -
the help you'll find here will amaze you.

hth

Graham/Joseph. Thanks for your advice. What you have said confirms that I
should really convert this to an Access database at some stage. Can't say I
really follow all the finer points so I will try and find somebody
locally
whoc can do some hand-holding when I do the conversion.

Thanks again.

Hi Terry

In Access, you would create a separate table for your progress details,
and relate that table to your projects table with a one-to-many
relationship. Each *line* in your Excel cell would be implemented as a
*record* in your ProgressDetails table.

The Projects table would need to have a "primary key" which is a unique
value to identify each project. If you don't already have unique project
numbers, then you could just add a field named "ProjectID" and make it an
autonumber field.

Your ProgressDetails table needs a "foreign key", which is a field of the
same type as the related primary key (autonumbers are numeric long
integers, so that is what you would use for the foreign key).

So, it needs three fields:
ProjectID (number, long integer, required)
Timestamp (date/time, default value =Now() )
Details (text or memo)

The timestamp is required to ensure the records are in the correct order,
and would also be useful to query, for example, "which projects have
had
activity in the last month?"

Data entry should be done with a form (Projects) and a subform
(ProgressDetails). Check out the sample NorthWind database for
examples
of setting up forms and subforms.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sorry if this is a really basic question. I am quite conversant with
Excel but have little experience with Access.

Not sure if there is a better way of doing this ...

I have an Excel workbook comprising several sheets. The main sheet on
which I
input data lists projects that are started and finished throughout the
year.
In simple terms, the columns are: Date Started, Project Owner, Brief
Description, Date Conpleted, etc. I then filter the Date Completed
column
so that only 'live' projects are showing and, on other sheets, have
various
data analysis.

One further column on the inputs sheet details progress on each project -
every time something happens this is updated. I enter this simply by
creating a new line each time a new event occurs using Alt+Enter and then
prefixing each line with a '-'. This is fine but for some larger
projects
there can be 30, 40 or 50+ entries so the cell in which all this is
entered
starts to get somewhat 'crowded' and sometimes won't even show everything
on
the screen (I have to click within the cell and then move down using the
arrow keys).

Although everything works OK, I'm conscious that the spreadsheet is
getting
bigger and bigger (currently c 1.2 Mb) and I wonder whether there is a
simpler way of storing all of the 'progress' data whilst retaining the
basic
format of things. I don't know much about Access - should I be using
this
instead?

Any suggestions will be welcomed!

Thanks.

 

[tina]

you're welcome

Thanks Tina

as this will be your first Access project, you'll find the going a lot
easier if you start off on the right foot. data normalization is not a
"finer point" when it comes to building an application, it's the basis of
your whole project. for more information, see
http://home.att.net/~california.db/tips.html, focusing first on tips 1 and
2, in that order.

and, as Graham said, come back to the newsgroups with specific questions -
the help you'll find here will amaze you.

hth

Graham/Joseph. Thanks for your advice. What you have said confirms that I
should really convert this to an Access database at some stage. Can't

say
I

really follow all the finer points so I will try and find somebody
locally
whoc can do some hand-holding when I do the conversion.

Thanks again.

Hi Terry

In Access, you would create a separate table for your progress details,
and relate that table to your projects table with a one-to-many
relationship. Each *line* in your Excel cell would be implemented as a
*record* in your ProgressDetails table.

The Projects table would need to have a "primary key" which is a unique
value to identify each project. If you don't already have unique project
numbers, then you could just add a field named "ProjectID" and make

it
an

autonumber field.

Your ProgressDetails table needs a "foreign key", which is a field of the
same type as the related primary key (autonumbers are numeric long
integers, so that is what you would use for the foreign key).

So, it needs three fields:
ProjectID (number, long integer, required)
Timestamp (date/time, default value =Now() )
Details (text or memo)

The timestamp is required to ensure the records are in the correct order,
and would also be useful to query, for example, "which projects have
had
activity in the last month?"

Data entry should be done with a form (Projects) and a subform
(ProgressDetails). Check out the sample NorthWind database for
examples
of setting up forms and subforms.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sorry if this is a really basic question. I am quite conversant with
Excel but have little experience with Access.

Not sure if there is a better way of doing this ...

I have an Excel workbook comprising several sheets. The main sheet on
which I
input data lists projects that are started and finished throughout the
year.
In simple terms, the columns are: Date Started, Project Owner, Brief
Description, Date Conpleted, etc. I then filter the Date Completed
column
so that only 'live' projects are showing and, on other sheets, have
various
data analysis.

One further column on the inputs sheet details progress on each project -
every time something happens this is updated. I enter this simply by
creating a new line each time a new event occurs using Alt+Enter and then
prefixing each line with a '-'. This is fine but for some larger
projects
there can be 30, 40 or 50+ entries so the cell in which all this is
entered
starts to get somewhat 'crowded' and sometimes won't even show everything
on
the screen (I have to click within the cell and then move down using the
arrow keys).

Although everything works OK, I'm conscious that the spreadsheet is
getting
bigger and bigger (currently c 1.2 Mb) and I wonder whether there is a
simpler way of storing all of the 'progress' data whilst retaining the
basic
format of things. I don't know much about Access - should I be using
this
instead?

Any suggestions will be welcomed!

Thanks.