Prev: splitting database
Next: Importing Tab Delimited Text File to an Existing Table in Access
From: SandraRae2000 on 2 Feb 2010 13:53
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!

From: Jeff Boyce on 2 Feb 2010 14:50
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" <SandraRae2000(a)discussions.microsoft.com> wrote in message
news:819D76E2-93D9-42BC-AB20-387A063FE431(a)microsoft.com...
> 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!
>


From: Fred on 3 Feb 2010 09:30
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!



From: SandraRae2000 on 3 Feb 2010 14:59
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!
>
>
>
From: Fred on 3 Feb 2010 15:12
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.





 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: splitting database
Next: Importing Tab Delimited Text File to an Existing Table in Access