A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Keeping DB structure but adding new tables



 
 
Thread Tools Display Modes
  #1  
Old May 20th, 2010, 05:55 PM posted to microsoft.public.access.gettingstarted
Diddy
external usenet poster
 
Posts: 100
Default Keeping DB structure but adding new tables

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


  #2  
Old May 20th, 2010, 06:48 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Keeping DB structure but adding new tables

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/For...arted/201005/1

  #3  
Old May 20th, 2010, 07:13 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Keeping DB structure but adding new tables

On Thu, 20 May 2010 09:55:01 -0700, 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


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/acc...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]
  #4  
Old May 21st, 2010, 10:46 AM posted to microsoft.public.access.gettingstarted
Diddy
external usenet poster
 
Posts: 100
Default Keeping DB structure but adding new tables

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/For...arted/201005/1

.

  #5  
Old May 21st, 2010, 10:52 AM posted to microsoft.public.access.gettingstarted
Diddy
external usenet poster
 
Posts: 100
Default Keeping DB structure but adding new tables

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
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/acc...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]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.