Column Names in Excel to Data in Access

[c8tz]

Hi,

We have a pests Spreadsheet which is used to record the types of
pests
found at different locations. If the certain pest is found there,
then
there is a 1 if not 0.
I would like to switch to Access.


Problem:
In Excel where there is the name of each pest in each column and then
the records follow through.
I would like to combine the pests into one "Pests" Table but still
have these linked to the main table of records.


I have created a "Pests" table such that each pest is a column name.
But when it comes to creating a crosstab report , I'll have to create
a crosstab for each pest instead of using a parameter.


I would appreciate some assistance,


Thanks,

 

[Guest]

Putting a pest in each column works in Excel; however, it is wrong in a
database like Access. For example if you ever add a new pest, you will need
to redesign every query, form, or report based on that table!

You need at least three tables:

Locations: LocID, LocName, LocStreet, LocCity, etc. LocID will be the
primary key and an autonumber.

Pests: PestID, PestName, .... and other information about the pest. PestID
will be the primary key and an autonumber.

LocationOfPest: LOP_ID, LocID, PestID, and other needed info.
LOP_ID will be the primary key and an autonumber.
LocID and PestID together will be a unique index to prevent duplicate entries.
LocID and PestID are both foriegn keys from their respective tables.

Now in the LocationOfPest table you can pick a LocID and PestID in a record
which says that a particular pest is in a particular location.

Why go through all this? Because a pest can be at many locations AND a
location can have many pests. It's a classic many-to-many relationship that
needs to be broken down into two one-to-many relationships. M-M doesn't work
well in a relational database; however 1-M's do.

If all the above sounds like gibberish, it could be that I didn't explain it
clearly. Or it could mean that you need some training. If so I highly
recommend getting some relational database training at a community college or
reading "Database Design for Mere Mortals" by Hernandez before proceeding any
further on this database.

One other thought: Have you looked at trade journals to see if there is
already a product out there which will do what you want? Even if it seems
expensive, it's usually cheaper in the long run to buy something off the
shelf unless (1) your business needs are very uniquer or (2) you want to
learn some new skills the hard way.