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


--
Joseph Meehan

Dia 's Muire duit

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

"Terry Bennett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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/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.

"Graham Mandeno" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Terry Bennett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>>
>
>

Hallelujah! Another convert <g>

Don't forget to come back here - we're mostly pretty good at hand-holding
:-)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Terry Bennett" <(E-Mail Removed)> wrote in message
news:ONQYG%(E-Mail Removed)...
> 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.

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


"Terry Bennett" <(E-Mail Removed)> wrote in message
news:ONQYG%(E-Mail Removed)...
> 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.
>
> "Graham Mandeno" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > "Terry Bennett" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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.
> >>
> >>
> >
> >
>
>

Thanks Tina

"tina" <(E-Mail Removed)> wrote in message
news:g7u3h.21604$(E-Mail Removed)...
> 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
>
>
> "Terry Bennett" <(E-Mail Removed)> wrote in message
> news:ONQYG%(E-Mail Removed)...
>> 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.
>>
>> "Graham Mandeno" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> > "Terry Bennett" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> 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.
>> >>
>> >>
>> >
>> >
>>
>>
>
>

you're welcome


"Terry Bennett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks Tina
>
> "tina" <(E-Mail Removed)> wrote in message
> news:g7u3h.21604$(E-Mail Removed)...
> > 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
> >
> >
> > "Terry Bennett" <(E-Mail Removed)> wrote in message
> > news:ONQYG%(E-Mail Removed)...
> >> 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.
> >>
> >> "Graham Mandeno" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >
> >> > "Terry Bennett" <(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> >> 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.
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>