If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Column Names in Excel to Data in One Column
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, |
#2
|
|||
|
|||
Column Names in Excel to Data in One Column
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 "c8tz" wrote in message ups.com... 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, |
#3
|
|||
|
|||
Column Names in Excel to Data in One Column
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 "c8tz" wrote in message ups.com... 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, |
#4
|
|||
|
|||
Column Names in Excel to Data in One Column
"Steve" schreef in bericht ... 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 -- 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 |
Thread Tools | |
Display Modes | |
|
|