Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Saving Monthly Accounts and starting a new Month?
From: Diddy on 20 May 2010 12:55
Hi,

I'm a complete novice just dibbling my toes in the water.

I've been using Excel to analyse my data which I receive as .csv files. for
e.g. School, Student and Exams. Each year the data is new as the students
change.

I've set up an Access database with queries from which I'm planning to
create reports.

Next year I would like to be able to just import the .csvs into a copied
database and not lose the queries and reports etc.

Is it doable and how would I go about it? Bear in mind that it has taken a
lot of reading and trial and error just to get this far

Many thanks
Diddy


From: KenSheridan via AccessMonster.com on 20 May 2010 13:48
Firstly, it sounds as though you are proposing to use separate databases for
each year. This is not only unnecessary but also not a good idea as it makes
it difficult to compare data one year against the other. There are also more
fundamental reasons for not doing so as it amounts to encoding data as
database names rather than storing them as values in rows in tables. It's
one of the basic principles of the database relational model (the Information
Principle) that data is stored in this way only. You can use a single
database and by making sure this includes the academic year the data for each
year can be processed independently of together over all or a selected number
of years.

Each year you'd append the data which you receive as text files to the
tables by means of 'append' queries. Just how easily this can be done
depends on the extent to which the structure of the .csv files reflects that
of your tables. It's not necessary for the text files to include the
academic year; you can add this when running the append query simply by using
a parameter as the 'field' e.g. AcademicYear:[Enter year:], which would
prompt you to enter the value to be inserted when the query is executed.

Even if the structures match then the order in which the data is added is
important. It's essential that data is first inserted into a 'referenced'
table before inserting related data into a 'referencing' table. For instance
it's necessary to append rows to a Schools table before appending rows to a
Students table as the latter references the former, so referential integrity
would be violated if this were done the other way round.

To restrict a report to a particular year you can either restrict the
report's underlying query to the year in question by means of a parameter, or
the query can be unrestricted (returning rows for all years) and the report
can be filtered to the year in question when opened.

Ken Sheridan
Stafford, England

Diddy wrote:
>Hi,
>
>I'm a complete novice just dibbling my toes in the water.
>
>I've been using Excel to analyse my data which I receive as .csv files. for
>e.g. School, Student and Exams. Each year the data is new as the students
>change.
>
>I've set up an Access database with queries from which I'm planning to
>create reports.
>
>Next year I would like to be able to just import the .csvs into a copied
>database and not lose the queries and reports etc.
>
>Is it doable and how would I go about it? Bear in mind that it has taken a
>lot of reading and trial and error just to get this far
>
>Many thanks
>Diddy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

From: John W. Vinson on 20 May 2010 14:13
On Thu, 20 May 2010 09:55:01 -0700, Diddy <Diddy(a)discussions.microsoft.com>
wrote:

>Hi,
>
>I'm a complete novice just dibbling my toes in the water.
>
>I've been using Excel to analyse my data which I receive as .csv files. for
>e.g. School, Student and Exams. Each year the data is new as the students
>change.
>
>I've set up an Access database with queries from which I'm planning to
>create reports.
>
>Next year I would like to be able to just import the .csvs into a copied
>database and not lose the queries and reports etc.
>
>Is it doable and how would I go about it? Bear in mind that it has taken a
>lot of reading and trial and error just to get this far
>
>Many thanks
>Diddy

Importing new tables will not destroy or remove your forms, reports, or
queries.

I would strongly suggest you go a bit further, though - set up a permanent set
of properly normalized tables (a table of Students, a table of Schools, a
table of Exams, etc.) and use the .csv files simply as a means of collecting
data. Rather than creating a brand new database each year, you can include a
date field in each appropriate table so that you can use a Query to select
those exams given in 2010, or in 2011, or whenever; carry students forward
from one year to the next (Janet Smith is still Janet Smith even in a new
year).

You might want to go over some of the tutorials in these links, if you have
not done so already:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
From: Diddy on 21 May 2010 05:46
Thank you Ken,

Plenty to think about but I'm so glad I asked rather than charging off in
the wrong direction and then giving it all up as a bad job !

Thanks again
Diddy

"KenSheridan via AccessMonster.com" wrote:

> Firstly, it sounds as though you are proposing to use separate databases for
> each year. This is not only unnecessary but also not a good idea as it makes
> it difficult to compare data one year against the other. There are also more
> fundamental reasons for not doing so as it amounts to encoding data as
> database names rather than storing them as values in rows in tables. It's
> one of the basic principles of the database relational model (the Information
> Principle) that data is stored in this way only. You can use a single
> database and by making sure this includes the academic year the data for each
> year can be processed independently of together over all or a selected number
> of years.
>
> Each year you'd append the data which you receive as text files to the
> tables by means of 'append' queries. Just how easily this can be done
> depends on the extent to which the structure of the .csv files reflects that
> of your tables. It's not necessary for the text files to include the
> academic year; you can add this when running the append query simply by using
> a parameter as the 'field' e.g. AcademicYear:[Enter year:], which would
> prompt you to enter the value to be inserted when the query is executed.
>
> Even if the structures match then the order in which the data is added is
> important. It's essential that data is first inserted into a 'referenced'
> table before inserting related data into a 'referencing' table. For instance
> it's necessary to append rows to a Schools table before appending rows to a
> Students table as the latter references the former, so referential integrity
> would be violated if this were done the other way round.
>
> To restrict a report to a particular year you can either restrict the
> report's underlying query to the year in question by means of a parameter, or
> the query can be unrestricted (returning rows for all years) and the report
> can be filtered to the year in question when opened.
>
> Ken Sheridan
> Stafford, England
>
> Diddy wrote:
> >Hi,
> >
> >I'm a complete novice just dibbling my toes in the water.
> >
> >I've been using Excel to analyse my data which I receive as .csv files. for
> >e.g. School, Student and Exams. Each year the data is new as the students
> >change.
> >
> >I've set up an Access database with queries from which I'm planning to
> >create reports.
> >
> >Next year I would like to be able to just import the .csvs into a copied
> >database and not lose the queries and reports etc.
> >
> >Is it doable and how would I go about it? Bear in mind that it has taken a
> >lot of reading and trial and error just to get this far
> >
> >Many thanks
> >Diddy
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
>
> .
>
From: Diddy on 21 May 2010 05:52
Thank you John,

It makes so much sense when someone who knows what they are doing suggests
it :-)

Thank you for the links :-) I've already found Crystal's notes really clear
and helpful but I'll take a look at the others for sure.

Cheers
Diddy



"John W. Vinson" wrote:

> On Thu, 20 May 2010 09:55:01 -0700, Diddy <Diddy(a)discussions.microsoft.com>
> wrote:
>
> >Hi,
> >
> >I'm a complete novice just dibbling my toes in the water.
> >
> >I've been using Excel to analyse my data which I receive as .csv files. for
> >e.g. School, Student and Exams. Each year the data is new as the students
> >change.
> >
> >I've set up an Access database with queries from which I'm planning to
> >create reports.
> >
> >Next year I would like to be able to just import the .csvs into a copied
> >database and not lose the queries and reports etc.
> >
> >Is it doable and how would I go about it? Bear in mind that it has taken a
> >lot of reading and trial and error just to get this far
> >
> >Many thanks
> >Diddy
>
> Importing new tables will not destroy or remove your forms, reports, or
> queries.
>
> I would strongly suggest you go a bit further, though - set up a permanent set
> of properly normalized tables (a table of Students, a table of Schools, a
> table of Exams, etc.) and use the .csv files simply as a means of collecting
> data. Rather than creating a brand new database each year, you can include a
> date field in each appropriate table so that you can use a Query to select
> those exams given in 2010, or in 2011, or whenever; carry students forward
> from one year to the next (Janet Smith is still Janet Smith even in a new
> year).
>
> You might want to go over some of the tutorials in these links, if you have
> not done so already:
>
> Jeff Conrad's resources page:
> http://www.accessmvp.com/JConrad/accessjunkie/resources.html
>
> The Access Web resources page:
> http://www.mvps.org/access/resources/index.html
>
> Roger Carlson's tutorials, samples and tips:
> http://www.rogersaccesslibrary.com/
>
> A free tutorial written by Crystal:
> http://allenbrowne.com/casu-22.html
>
> A video how-to series by Crystal:
> http://www.YouTube.com/user/LearnAccessByCrystal
>
> MVP Allen Browne's tutorials:
> http://allenbrowne.com/links.html#Tutorials
>
> --
>
> John W. Vinson [MVP]
> .
>
 | 
Pages: 1
Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Saving Monthly Accounts and starting a new Month?