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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Rookie-building DB-want to get right the first time! Help?



 
 
Thread Tools Display Modes
  #1  
Old February 2nd, 2010, 07:53 PM posted to microsoft.public.access.tablesdbdesign
SandraRae2000
external usenet poster
 
Posts: 5
Default Rookie-building DB-want to get right the first time! Help?

Rookie user: Access 2007, Using “Picture Yourself Learning: Microsoft Access
2007” as reference guide.
Need suggestions for how to structure what seems to me to be a very
complicated DB. I want to build it the best way the first time, so I don’t do
a lot of work and not have it do what I need.
Here’s what I need to do:
Track GIS datasets for about 50 natural and environmental hazards.
The data itself does not need to be tracked. I do need to track its source
(National, State, County or City data) and know its date of creation, and
frequency of updates for example.
I don’t know whether to create one Table with tons of fields, and then try
to figure out queries. Or should I create tables for each specific hazard
like Earthquake, Flood, etc.? Should I create tables for each source of data
such as National, State, County and each City from which I obtain data?
What I want to be able to do is easily determine which updates to which data
need to happen when, which data came from which source, what data is
available for each specific hazard and what datasets overlap at the National,
State, County and City levels.

Here’s an example: I have data from the National Wetlands Inventory that is
updated annually.
I also have data from the County that is updated quarterly. The State and
the Cities have no additional data. I need to be able to know, for 50
different hazards or issues, the source of the data, how often it’s updated,
a scheduled reminder when it’s time to update if possible, and a field with
a hyperlink to the actual data or metadata.

My reference book includes instructions on Creating a DB, Creating and
Editing Tables, Improving Table Design and Creating Relationships, Creating
Forms, Creating Simple Queries, Creating Queries that filter and Summarize
Data and Creating Reports. I think it’s a beginner type book – well it must
be, if I’m able to sort of understand it. But I’m hoping someone who
understands the whole picture can tell me what parts to build and where. I
can follow directions, but can’t see the forest for the trees at this point.

Any help would be extremely appreciated, and paid forward!

  #2  
Old February 2nd, 2010, 08:50 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Rookie-building DB-want to get right the first time! Help?

YOU understand the whole picture, much better than we can!

I agree that you're much better off to get your table structure down right
before you move on.

I strongly recommend against committing spreadsheet on Access (one table,
tons of fields). You will always be revising that table and your queries,
reports, forms, etc if you use that approach.

What are the "things" about which you want to keep data? These are the
"entities" in relational database design.

In your situation, it sounds like [Hazards], [Sources] and [Datasets].

Now, how are they related ("relationship")? Can you have one [Hazard]
(e.g., "earthquake") that shows up in many [Datasets]? (guessing yes). Can
you have one [Source] that shows up in many [Datasets]? (again, guessing
yes).

Then your [Dataset] table would probably have a field for the [Hazard], a
field for the [Source], and a field for the datetime that it was created.
I'm not sure I understand if the frequency of update is being calculated
from the other data, or is a piece of data in its own right.

Good luck! Post back with further questions! That's how most of us
learned.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



"SandraRae2000" wrote in message
...
Rookie user: Access 2007, Using "Picture Yourself Learning: Microsoft
Access
2007" as reference guide.
Need suggestions for how to structure what seems to me to be a very
complicated DB. I want to build it the best way the first time, so I don't
do
a lot of work and not have it do what I need.
Here's what I need to do:
Track GIS datasets for about 50 natural and environmental hazards.
The data itself does not need to be tracked. I do need to track its source
(National, State, County or City data) and know its date of creation, and
frequency of updates for example.
I don't know whether to create one Table with tons of fields, and then try
to figure out queries. Or should I create tables for each specific hazard
like Earthquake, Flood, etc.? Should I create tables for each source of
data
such as National, State, County and each City from which I obtain data?
What I want to be able to do is easily determine which updates to which
data
need to happen when, which data came from which source, what data is
available for each specific hazard and what datasets overlap at the
National,
State, County and City levels.

Here's an example: I have data from the National Wetlands Inventory that
is
updated annually.
I also have data from the County that is updated quarterly. The State and
the Cities have no additional data. I need to be able to know, for 50
different hazards or issues, the source of the data, how often it's
updated,
a scheduled reminder when it's time to update if possible, and a field
with
a hyperlink to the actual data or metadata.

My reference book includes instructions on Creating a DB, Creating and
Editing Tables, Improving Table Design and Creating Relationships,
Creating
Forms, Creating Simple Queries, Creating Queries that filter and Summarize
Data and Creating Reports. I think it's a beginner type book - well it
must
be, if I'm able to sort of understand it. But I'm hoping someone who
understands the whole picture can tell me what parts to build and where. I
can follow directions, but can't see the forest for the trees at this
point.

Any help would be extremely appreciated, and paid forward!



  #3  
Old February 3rd, 2010, 03:30 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Rookie-building DB-want to get right the first time! Help?

Do what Jeff said.

Adding / reinforcing the fine points, start by pushing your Access book and
the computer aside and decide on what the ENTITIES that you want to database
are, and what the relationships are between them. You might even discuss
that further here in this forum at this early important stage.

My first guess is that main main entity is dtatsets, which are instances of
receiving data, or, more specifically, instances of data being recorded or
summarized by your sources.

"Sources" will probably be another major table. If so, a good guess would
be to put a SourceID autonumber PK field in your Sources table, and an
integer "SourceID" FK field in your Datasets table and link those two fields.

"Hazards" will probably be more of a "lookup table" to fill a "hazard" field
in your Datasets table.

Good luck!



  #4  
Old February 3rd, 2010, 08:59 PM posted to microsoft.public.access.tablesdbdesign
SandraRae2000
external usenet poster
 
Posts: 5
Default Rookie-building DB-want to get right the first time! Help?

I'm trying these things you suggested, but what is FK?

"Fred" wrote:

Do what Jeff said.

Adding / reinforcing the fine points, start by pushing your Access book and
the computer aside and decide on what the ENTITIES that you want to database
are, and what the relationships are between them. You might even discuss
that further here in this forum at this early important stage.

My first guess is that main main entity is dtatsets, which are instances of
receiving data, or, more specifically, instances of data being recorded or
summarized by your sources.

"Sources" will probably be another major table. If so, a good guess would
be to put a SourceID autonumber PK field in your Sources table, and an
integer "SourceID" FK field in your Datasets table and link those two fields.

"Hazards" will probably be more of a "lookup table" to fill a "hazard" field
in your Datasets table.

Good luck!



  #5  
Old February 3rd, 2010, 09:12 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Rookie-building DB-want to get right the first time! Help?

Hi Sandra,

In order to answer your last question:

I know you're having some trouble on this first step for your medium
complexity situation, but most people would, and that's what this design
discussion group is for. Are you somewhat fluent on the the basics of Access
design, like queries, forms etc?

Will this dtatbase need to be used by other people who will know absolutely
nothing about Access? If so, you will probably need to get substantial help
from a developer.

How much time to you have to get this up and running?


PS: Don't use anybody who hits you up for money in these free advice forums.





  #6  
Old February 3rd, 2010, 09:36 PM posted to microsoft.public.access.tablesdbdesign
Dorian
external usenet poster
 
Posts: 542
Default Rookie-building DB-want to get right the first time! Help?

FK is foreign key'and PK is primary key. You need to understand what these 2
extremely important concepts are. I suggest googling them.
Basically, a PK is a column (or columns) in a table that uniquely identifies
a row. A Fk is a reference to a PK from a related table.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"SandraRae2000" wrote:

I'm trying these things you suggested, but what is FK?

"Fred" wrote:

Do what Jeff said.

Adding / reinforcing the fine points, start by pushing your Access book and
the computer aside and decide on what the ENTITIES that you want to database
are, and what the relationships are between them. You might even discuss
that further here in this forum at this early important stage.

My first guess is that main main entity is dtatsets, which are instances of
receiving data, or, more specifically, instances of data being recorded or
summarized by your sources.

"Sources" will probably be another major table. If so, a good guess would
be to put a SourceID autonumber PK field in your Sources table, and an
integer "SourceID" FK field in your Datasets table and link those two fields.

"Hazards" will probably be more of a "lookup table" to fill a "hazard" field
in your Datasets table.

Good luck!



  #7  
Old February 3rd, 2010, 10:15 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Rookie-building DB-want to get right the first time! Help?

BTW that was just my first guess, not knowing the details of your
business/agency. But a good place to start, even if imperfect.
FK = Foreign Key

To answer this and your linking question.

Let's say that you just entered a new Source, the city of Las Vegas, NV.
You have an autonumber PK field named SourceID. When you entered that
record, Access automatically gave it a source ID number of 1234.

Now you have your first two datasets: December 2009 Plutonium leaks, and
January 2010 Plutonium leaks. You want to enter these and record where they
came from via a link. There are three steps to linking:

One time: Put an integer (not autonumber) field called
"IDNUmberofSourceofThisDataset" (or "SourceID" as I said before is also OK)
into your Dataset table. This defines the intended use of this field as a
"Foreign Key" ....FK is a USE, not a structure setting in Access like PK.

One time: Open the relationships window and draw a line between this field
and the "SourceID" field in your source table.

When adding those two DataSet Record, write "1234" in your
"IDNUmberofSourceofThisDataset" field.

Later this loading of the field and and use of the links gets automated by
forms.

  #8  
Old February 3rd, 2010, 10:54 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Rookie-building DB-want to get right the first time! Help?

If the responses you have gotten are all greek to you, I would like to offer
tp build the database for you. I provide help with Access, Excel and Word
applications for a small fee. My fee to help you would be very reasonable.
Contact me and let's discuss how we can work together to create your
database.

Steve



"SandraRae2000" wrote in message
...
Rookie user: Access 2007, Using "Picture Yourself Learning: Microsoft
Access
2007" as reference guide.
Need suggestions for how to structure what seems to me to be a very
complicated DB. I want to build it the best way the first time, so I don't
do
a lot of work and not have it do what I need.
Here's what I need to do:
Track GIS datasets for about 50 natural and environmental hazards.
The data itself does not need to be tracked. I do need to track its source
(National, State, County or City data) and know its date of creation, and
frequency of updates for example.
I don't know whether to create one Table with tons of fields, and then try
to figure out queries. Or should I create tables for each specific hazard
like Earthquake, Flood, etc.? Should I create tables for each source of
data
such as National, State, County and each City from which I obtain data?
What I want to be able to do is easily determine which updates to which
data
need to happen when, which data came from which source, what data is
available for each specific hazard and what datasets overlap at the
National,
State, County and City levels.

Here's an example: I have data from the National Wetlands Inventory that
is
updated annually.
I also have data from the County that is updated quarterly. The State and
the Cities have no additional data. I need to be able to know, for 50
different hazards or issues, the source of the data, how often it's
updated,
a scheduled reminder when it's time to update if possible, and a field
with
a hyperlink to the actual data or metadata.

My reference book includes instructions on Creating a DB, Creating and
Editing Tables, Improving Table Design and Creating Relationships,
Creating
Forms, Creating Simple Queries, Creating Queries that filter and Summarize
Data and Creating Reports. I think it's a beginner type book - well it
must
be, if I'm able to sort of understand it. But I'm hoping someone who
understands the whole picture can tell me what parts to build and where. I
can follow directions, but can't see the forest for the trees at this
point.

Any help would be extremely appreciated, and paid forward!



  #9  
Old February 3rd, 2010, 11:52 PM posted to microsoft.public.access.tablesdbdesign
StopThisAdvertising
external usenet poster
 
Posts: 334
Default Rookie-building DB-want to get right the first time! Help?


"Steve" schreef in bericht ...
If the responses you have gotten are all greek to you, I would like to offer tp build the database for you. I provide help with
Access, Excel and Word applications for a small fee. My fee to help you would be very reasonable. Contact me and let's discuss how
we can work together to create your database.

Steve



From now on I am going to hunt each and every post of you Steve
Again.... It won't work but that 's no problem... it's a shame...
--
Get lost $teve. Go away... far away....

Again... Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

This newsgroup is meant for FREE help..
No-one wants you here... no-one needs you here...
OP look at
http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

Arno R


  #10  
Old February 4th, 2010, 12:41 AM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Rookie-building DB-want to get right the first time! Help?

"Steve" wrote in message
...
If the responses you have gotten are all greek to you, I would like to
offer tp build the database for you. I provide help with Access, Excel and
Word applications for a small fee. My fee to help you would be very
reasonable. Contact me and let's discuss how we can work together to
create your database.

Steve


Still pimping your questionable services?


Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio 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 03:31 PM.


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