Keeping DB structure but adding new tables

[Diddy]

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

 

[John W. Vinson]

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

 

[Diddy]

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

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

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


.

 

[Diddy]

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