My Access database is going to undo years of expensive therapy.

[ellezeebub]

Hi, Please forgive my long sad story of confusion and near despair,
but I hope you can hang in there with me and help me figure this out.
I don't mean to be long winded, I just want to get all the details in
here so I can get some meaningful replies.

In my field, natural resources, lots and lots of folks use Access.
So, innocently, I set out to learn Access by using it. Although I
work with a lot of long time professionals, when I told them I wanted
to use Access they all looked kind of horrified, as though no one in
their right mind would use Access when they can use Excel. Well, I
want to use a powerful tool that makes me more valuable as an
employee, but I am beginning to understand why these guys have so much
antipathy for the program.

I think I understand the elementary principles of primary key, fields,
columns, field types, etc., and I have a stack of Access books on my
desk. What I apparently do not understand very well is how to
organize my data in tables so that I can do meaningful select queries
or update queries or any other truly useful function.

I set up the database from scratch, using the principles suggested in
my Learn Access classes 1 and 2. I have a table for sample locations,
a table that keeps track of sample numbers and a few other
miscellaneous tables that record ambient water quality information.

Where I hit a major snag is setting up the analytical results/sample
concentrations table. Originally I put the sample concentrations in a
table specific to each type of sample, i.e. alkalinity, TSS, dissolved
metals, etc. However, I spoke with someone who has more Access
experience in my department than most, and she promptly told me that
in a well-normalized database all the results go into a single table
(like a flat file??). So, I spent the better part of a day trying to
get all my results information into a single table that varied by
analyte and other relative information, but was uniform in its
presentation of data.

Then, today I tried to do an update query so that I could use some of
the information from the sample location table to add a "type" field
to my results table. The idea is to separate the results into three
categories: saltwater, freshwater, and intermediate (waters at the
border of the freshwater outlet and the ocean). The sample location
table has the location, date of sampling, and sample bottle numbers.
Some of the sample bottles are analyzed for multiple constituents,
i.e. the ACS sample is analyzed for alkalinity, chloride and sulfate.
So, the results table is related to the sample location bottle by the
sample bottle number--in the sample location table the sample bottle
numbers are unique, but in the sample results table the sample bottle
number is repeated.

My problem before I joined all the results into one table was that if
I tried to run a query that would return all the samples run on a
particular day at a particular site, I would only get two of eight
records back because one of the analytes only had two samples per day
while all the others had eight. I tried changing the joins around,
but no luck. So, now my problem is that although I have all the
results in one table, now my queries return nothing because it wants
my location to find all the analytes (for example, the SQL looks for
all the analyte fields with AND statements).

I don't know if anyone can understand why I am having so many
problems, but I am running out of time to get this project done.
Access was supposed to make this easy (according to the teachers), but
I would have to say, "not so far."

Thanks in advance,

Ellen.

 

[DAVID]

Post your SQL. Open the query in design view, change to
SQL view, copy the text, paste it into a message here.

(david)

 

[Steve]

You went way wrong listening to your colleague who told you in a
well-normalized database all the results go into a single table. That is
absolutely the antithesis of correctly designing your tables. A good
database MUST have a set of correctly designed Normalized tables. You won't
get there with the advise from your colleague as you are already
experiencing.

The crux of designing tables is to be able to identify one-to-many
relationships. One to many relationships require at least two tables. Second
most important is to limit the fields in a table to one topic. Here's an
example. You have many locations from where you draw samples and at each
location there can be multipe samples drawn. Locations and samples are two
topics so right away you know you need two tables. Also each location can
have many samples so there is a one-to-many relationship between location
and samples. That tells you you need at least two tables. There are
different sample types so sample type is a topic and you need a table for
that. So you need:
TblLocation
LocationID
LocationName
<<Maybe other fields about a location>>

TblSampleType
SampleTypeID
SampleType

TblSample
SampleID
LocationID
SampleTypeID
SampleDate

If you need help getting the project done, I can provide whatever help you
need for a very reasonable fee. Contact me at my email address below.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Steve]

David,

No offense to you but trying to get her query working is like putting a
bandaid on the problem. Her root problem is her tables are wrong because she
listened to ill-advise from a colleague. Her tables need revised.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[DAVID]

I think you have made assumptions about her project.

The classic example is multiple annual tables,
normalised to one table with a date field.

When she has posted her SQL, we will see.

(david)

 

[Gina Whipp]

Ellen,

Your first issue is the person that told you to put everything ito a single
table. Do you have a back-up? If no, then you might be going thru the
painstaking chore of breaking your data back into seperate tables. Not only
will it be easier to run your query but it will be easier in the long run.
(The reason folks looked at you funny about using Access is because it does
have a steep learning curve unlike Excel which one can figure out in about
10 minutes.) I have been doing this for some years now ad I am still
learning!)

If you need help setting up tables and the like there are MORE then enough
people/developers in this FREE Microsoft newsgroup who will help you for
FREE!

 

[tina]

Gina, your sig line is hilarious! <g>

Ellen,

Your first issue is the person that told you to put everything ito a single
table. Do you have a back-up? If no, then you might be going thru the
painstaking chore of breaking your data back into seperate tables. Not only
will it be easier to run your query but it will be easier in the long run.
(The reason folks looked at you funny about using Access is because it does
have a steep learning curve unlike Excel which one can figure out in about
10 minutes.) I have been doing this for some years now ad I am still
learning!)

If you need help setting up tables and the like there are MORE then enough
people/developers in this FREE Microsoft newsgroup who will help you for
FREE!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[Gina Whipp]

Thank you....

 

[tina]

as Gina says, Access has a steep learning curve. whereas in Excel data
storage and data presentation are pretty much the same thing, in Access
they're completely separate. and learning to use the Access software is only
*half* the battle - and it's the SECOND half, at that. if you're willing to
invest the time and effort to learn how to use the software after FIRST
learning relational design principles, then you'll find Access a very
powerful tool for data manipulation and analysis - and be the envy of your
colleagues!
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Maelinar]

I also work in the field of natural resources

I don't think you are necessarily having as big a problem as you think you
are in, if you want to see only information related to your three categories
(saltwater, freshwater, and intermediate), then why don't you just write
queries to filter to only those categories ?

Field [name]
Table [name]
Criteria = "saltwater"

etc.

It's not necessarily the 'best' way to do it, but it will work.

 

[John Marshall, MVP]

Steveo these newsgroups are for FREE peer to support. Your solicitations are
unwelcome.

John... Visio MVP

 

[Andy Hull]

Hi Ellen

There is a lot of useful advice in the responses you have had so far but I'm
not sure if you have heard what you need to solve your problem yet!

I think there may be confusion over what was / is meant when your colleague
suggested putting all results in one table.

Usually, when we hear that type of comment we cringe because it sounds like
the usual mistake of treating Access like Excel and ending up with lots of
duplication and a database that is hard to maintain as well as losing the
relational advantages of a well designed database.

Having said that, I'm not convinced you received bad advice. It would be
very useful if you could post the SQL of the query you are having issues with
as, suggested by David, along with a re-statement of what you want. We will
be able to see if your table design is causing your problems.

To me, it seems like it was good advice. As far as I can tell, you used to
have results for alkalinity in one table and results for TSS in another and
results for dissolved metals in yet another etc...

Your colleague advised you to put all these results into one table with, I
assume, a column specifying the test type (alkalinity, TSS, etc) - Please
excuse where I have your terminology wrong - I am not familiar with your
field!

This is sound advice unless each type of test has very different attributes.
If each test type has the same columns (or very similar) then, yes, put them
in one table. If you don't, then it will be more difficult to show
alkalinity test results alongside TSS test results.

You also spoke about the location type (I think) - Saltwater, Freshwater,
Intermediate. This is an attribute of the location so I would expect you to
have a location table with this column. I am imagining you (should) have
tables like...

Table: tblLocation
Columns: LocationID, LocationType (eg Saltwater), ...other location
attributes...

Table: tblSampleLocation
Columns: SampleLocationID, BottleNumber, SampleDate, LocationID, etc...

{maybe SampleLocationID is not required as I understand BottleNumber is
unique and effectively describes a single sample at a single location? -
although some would still go for a non-meaningful autonumber primary key}

Table: tblResults
Columns: SampleLocationID, TestType (eg Alkalinity, TSS, etc), ...other
results attributes...


Then you can show results against samples by joining tblResults to
tblSampleLocation using SampleLocationID {or BottleNumber if that's what you
used as a primary key}

You can further analyse this by Saltwater, Freshwater or Intermediate by
adding tblLocation to the query and joining it to tblSampleLocation using
LocationID.

I too apologise for the length of this post!

Hope it helps in some way

Andy Hull

 

[Jeff Boyce]

In a courtroom setting, the term "hearsay" might come up.

In addition to posting the SQL statement you are using (per responses
else-thread), consider posting the table structure you ARE using. For
example, if you were registering students for classes, your table structure
might look like:

tblStudent
StudentID
FirstName
...

tblClass
ClassID
ClassTitle
...

trelRegistration
RegistrationID
StudentID
ClassID
RegistrationDate
...

How 'bout posting yours?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Edward Reid]

On Thu, 26 Jul 2007 13:04:26 -0700, (e-mail address removed) wrote:

Love your email address.

So, innocently, I set out to learn Access by using it.

I'll try not to duplicate the good advice you've already received. One of
your problems is indicated by this statement. "Learn Access by using" is
not a good idea unless you already have considerable experience with
databases and programming, or a lot of time. As someone else pointed out,
it's very different from Excel -- storage and presentation are far more
distinct in Access, forcing new abstractions on you. Also, things which
most users are accustomed to being straightforward, are not so in Access.
For example, Access reports are powerful but you have a lot of up-front
learning to use that power compared with Excel or Word. (This isn't just a
database problem; a lot of the user interface in Access isn't nearly as
well designed as it should be. I've been programming since 1966 and have
plenty of experience with databases, yet I still find many parts of Access
to be terminally obtuse.)

Another problem I see is that you are trying to fit a fairly messy real
world situation into a database system, which doesn't cater to the
messiness and exceptions of working out in the swamp. If you see a blank
cell on your paper records, you can let your eye skip over it, at least
until you have to deal with it in the statistical analysis. For the most
part the same applies to Excel. But once you've trying to get at your data
in Access, you have to painstakingly deal with every missing data
situation. Sure, there are ways to deal with it, but as a beginner it takes
a long time to come up to speed on all this, during which time you are
sitting on your butt rather than getting the samples or writing up the
research.

Where was I ... it's one thing to set up, say, an inventory or bank
accounts in a database. There will be exceptions, but in theory it's a
closed system. You don't say, well, we couldn't find the balance on this
account so we'll skip it. But out in the swamp, things come up all the time
which no one anticipated. Databases are *very* poor at dealing with
unanticipated situations -- and that's in general, not just Access. Excel
is much more forgiving in terms of letting the messy parts slide until you
are ready to deal with them.

Edward