Column Names in Excel to Data in One Column

[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,

 

[tina]

the best advice i can offer is to read up on relational design principles.
Access is not at all like Excel, and if you impose a spreadsheet design on
it, you're going to have nothing but headaches in trying to work with the
data. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Steve]

Start with the following tables:

TblLocation
LocationID
LocationName

TblPest
PestID
PestName

TblPestInLocation
PestInLocationID
LocationID
PestID

Note -- If you are thinkng of using a crosstab query for data entry, it
won't work. A crosstab query is not updateable.

With the above tables you use a form/subform for data entry. The
recordsource for the main form is TblLocation and the recordsource for the
subform is TblPestInLocation. On the subform you use a combobox or listbox
for entering PestID. The rowsource for a combobox or listbox would be
TblPest.

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