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

[SandraRae2000]

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!

 

[Jeff Boyce]

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.

 

[Fred]

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!

 

[SandraRae2000]

I'm not sur eif I'm doing this reply post correctly. Coach me if I'm not.
Thank you so much to the both of you! I'm pretty overwhelmed, but NEED to
understand this and get it done!
Based on your replies, this is what I've come up with:
I'm pretty sure I need a table for Sources, since I have dozens of agencies
that will provide data to me. I'm thinking I need the basic Table Template
for Contacts, with a couple of extra fields added. What I don't understand is
how I will make this table "relate" with my other tables. The relationship as
I see it is that the source provides me with anywhere from 1-200 unique
hazard datasets, and I need to be able to know who gives what, and how
frequently it's updated. After knowing how frequently it's updated, I can
then create a schedule for myself to follow up and get the most updated
release of the data. (Some do weekly, monthly, quarterly, etc updates)
Second, I think I need a Table for Datasets, since each dataset has its own
metadata, like when it was created, how often it's updated, and most
importantly, a description of the hazard the data pertains to. The
relationship I see in this category is not quite as clear. I know I need to
know who provided the dataset and what hazard it pertains to. For example, I
may want to make a report with each hazard listed and which agency provided
which dataset. The confusion I have here is that I may have one dataset that
is only available for one City-not State, County or National. Do I give it
it's own Table?
The third thing I want to be able to group is the specific hazard. When I
think of what I would put in a Table for Hazards, I'm not coming up with
anything. But I do want to be able to pull a report that groups all datasets
under each hazard.
Here's another issue I'll need to consider - A county may provide data that
is originally sources from a state agency. That info can be found in the
metadata, and I'm thinking I need to pay attention to this. I don't want to
purchase/track the same data from the State and the County. Would I want to
have a field that says who THEIR source for the data is? Or is it best to
comb throught the metadata and only get the data from the primary source. I
just don't know if the second option is feasable.
Okay, my last question is this... am I asking for too much help here? Is
this something I can honestly do myself? When is it time to hire someone? I'm
on a shoestring startup budget, but also know that this is one of the most
important aspects of my operation. Thanks soooo much!
Sandra

 

[SandraRae2000]

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

 

[Fred]

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.

 

[Dorian]

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".

 

[Fred]

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.

 

[Steve]

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
(e-mail address removed)

 

[John... Visio MVP]

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
(e-mail address removed)

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

 

[SandraRae2000]

Thanks for the advice Fred. I AM feeling a bit overwhelmed, but feel like I'm
catching on slowly. I only have the knowledge I've gleaned from the books and
tutorials I've read this week. I'll be the only person using the database at
first. I'll then hopefully have someone else updating the database as well as
staying on top of the external data updates we'll be tracking.
I sort of get it that a report takes information from the table and makes it
'pretty'. I think I get it that a form just makes it easier to enter data,
but it still goes into the same table. I'm not sure what a Query does yet.
Is it possible to show you what I've done so far to see if I'm on the right
track? I'm not sure if that's out of bounds. I'm grateful just to try to
follow the instructions you are offering. I messed with the relationship
keys, but got a bit confused using the examples given. I'll play with that
again later.
As for when this needs to be up and running, that would be ASAP. I didn't
realize I needed something like this until it became a logistical nightmare
trying to figure out which agency to ask for what data. Now I'm a little
thwarted in the efforts needed to get the most basic aspect of the business
built - the data! Sigh. I AM a little overwhelmed. This forum is INCREDIBLE
though! I didn't realize there would be people as generous as you willing to
help me out. Thank you.

 

[Jeff Boyce]

These newsgroups are "staffed" by volunteers. The Code of Conduct for these
'groups precludes soliciting paid work.

If someone solicits paid work here, please consider whether you want to do
business with someone who ignores the rules.

Regards

Jeff Boyce
Microsoft Access MVP

 

[Keith Wilby]

If the responses you have gotten are all greek to you,

Patronising oaf.

I would like to offer tp build the database for you.

Would you hire someone who can't even check their own spelling?

I provide help with Access, Excel and Word applications for a small fee.
My fee to help you would be very reasonable.

Small or reasonable? The two are not the same. Either way it'll be money
for old rope.

Contact me

As a last act of desperation.

and let's discuss how we can work together to create your database.

No, let's discuss how to follow newsgroup rules.

 

[Fred]

Hi Sandra,

The confluence of two items (you are barely started on the big job of
learning Access and are in a big hurry to get this up and running then you
are probably going to need many hours of help from an expert.

If I were looking for such help, expert help, I scan back through this
discussion groupl for regular contributors who appear to be in the business
and who aren't the one person (who you've now met) that abuses this group by
hitting people up for money, and email a couple of them.


- - -

Alternatively, hit the books really hard and ask questions here. At the same
time that you are reading the books, here is your overall process:

1. Shut the computer off and decide the real world entities are that you
want to database. Use the discussion in this tread to guide you.

2. Design Tables and links between them to implement your decisions from#1

3. Make rudimentary queries to look at your individual tables. Not
essential, but a good next step. And (although not essential) make single
table queries which will be the middlemen (record sources) for your forms and
reports.

4. For cases where you need to see & enter the data from multiple linked
tables all at once, make a form with subform(s) to do so. For example, to
see an organization that sends datasets, then the show and enter their data
sets underneath them, make and "Organizaiton" form, and put a "Data set"
subform underneath it.

5. Make rudimentary reports for single tables

6. In a similar manner an situaiton as number 4, make reports with
subreports to show data.

 

[BruceM via AccessMonster.com]

What do you not understand about the nature of a volunteer newsgroup? If you
don't understand that basic concept you are not capable of understanding the
complexities and subtleties of relational database design.

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
(e-mail address removed)

Rookie user: Access 2007, Using "Picture Yourself Learning: Microsoft
Access

[quoted text clipped - 43 lines]

Any help would be extremely appreciated, and paid forward!

 

[Keith Wilby]

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.

FWIW I have a "hazardous materials" tracking database which you're welcome
to a copy of if you like. It may be of no use to you whatsoever, but then
again it might just help. Drop me a line if you'd like to take a look.

Keith.
www.keithwilby.co.uk

 

[Fred]

Sandra,

See my response of today, hidden several posts back in the above thread.

 

[SandraRae2000]

Thanks Fred!
I'm going to follow your instructions of how to approach this. Then, if that
fails, I'll consider your other suggestion. If I do go the professional
route, what is a typical fee structure like? While learning all of this and
starting from scratch is extremely fun and rewarding, I'm supposed to be
focusing on other aspects of my operation! But, I have more time than money!!
Thanks so much for your help and advice.
Sandra

 

[Armen Stein]

Alternatively, hit the books really hard and ask questions here. At the same
time that you are reading the books, here is your overall process:

1. Shut the computer off and decide the real world entities are that you
want to database.

This is really good advice. Every new database design should start
off as a pencil or whiteboard sketch of just the basic tables and how
they're related.

If you haven't already found it, a really good book for getting
started is Database Design for Mere Mortals by Michael Hernandez.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Fred]

I can't answer the fee structure question. I do this as a break from work
rather than to make money. Plus my area is data architecture and the
combination of databasing real world business processes. When it comes to
true Access developer stuff, you'll find me asking questions rather than
answering them.

If you're serious about getting more substantial help, I would do what I
recommended previously. Scan previous posts here for helpful people who
appear to be in the business, who haven't hit you up for money.