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)

 

[StopThisAdvertising]

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)

--
This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3450+ pageloads, 2275+ first-time visitors (these figures are rapidly increasing)

Why is this ???
Because Steve is the ONLY person here who continues to advertise in the groups.

It is not relevant whether he advertised in *this* particular post or not...
==> We want him to know that these groups are *not* his private hunting grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR