Yes/No in tables

[Tom]

I have an Excel worksheet with Names down the side and tasks across the top.
And yes/no if person can perform task.
I have an Access database with the tasks (same tasks as the Excel worksheet)
How can I put the Excel table in Access that will allow me to connect the 2
tables information
There are 100 names and 1000 different tasks
Can I view an Access table just like the Excel worksheet?

Thanks for any advice.

 

[Jerry Whittle]

An Access table can only have 255 fields across. Therefore you're going to
run into problems with 1000 fields. Are you using Excel 2007 as earlier
versions of Excel would only all 255 columns.

 

[Jeff Boyce]

Tom

It sounds like you are attempting to commit spreadsheet on Access. Access
is a relational database, not a "spreadsheet on steroids".

You may have to unlearn some of what's worked for you in Excel before you
can get Access to work the way it's designed to work.

For example, in Access, you could create tables like:

tblPerson
PersonID
FirstName
LastName

tblTask
TaskID
TaskTitle
TaskDescription

trelAbleToPerform
AbleToPerformID
PersonID
TaskID

and use that third table to record ONLY those tasks that a person can
perform.

With the design you described, you'd have to modify the table (and any
related queries, forms, reports, code, ...) EVERY time you added a new task.

With the above design, you'd simply add a new task to the Task table, then
use it as needed for new records in the AbleToPerform table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

When moving from Excel to Access, you have to change you entire mindset
when it comes to how you think about your data. Access is not a
spreadsheet on steriods, it is a relational database engine.

First, a table can have a maximum of 255 fields, and even that is optimistic
because the size of a record cannot exceed 4000 characters regardless of the
number of fields. The later limit doesn't apply to OLE or Memo fields.
They count for either 4 or 8 bytes, but I can't remember for sure.

The correct way to model your data in Access would be to have 3 tables. One
table would contain a record for each name. One table would contain a
record for each task. The third table would be a junction table that would
identify a person and a task. Each of the first two tables should have a
unique primary key. The third table should have its own primary key, but
its data would have a foreign key field that would contain the value of the
primary key field of the name with the task and another such field with the
value of task's primary key field. Using this model, you don't have a whole
lot of empty fields like you would have empty cells in Excel. You have one
record that identifies every name/task combination.

Now, to present your data, you use queries that join the tables on the
primary/foreign key combinations.