"three dimensional" data in Access

[caboaddict]

My subject title might be a little misleading. This isn't about having a
"graphical" 3D representation of data, but about how to get data from an
excel spreadsheet imported and useful in Access. I am using Access 2007 to
design the database, but saving it in a 2003 format. The basic description
of the spreadsheet is:

The first two dimensions are the column and Row headings while the third is
multiple worksheets with the same information. The columns contain dollar
amounts that correspond to each row for each year (Year 0, Year 1, Year 2,
etc) and Row values correspond to certain areas like data center, Training,
Development, and Implementation. Each one of those areas contain sub
elements such as hosting, contract, system support etc. These values are
specified for multiple vendors, each in their own workbook.

As far as I can tell (understand/know) is that each vendor will have to have
their own table with the rows being the section totals without being able to
include the sub sections unless I want to add some major complexity to the
database.

I hope I have clarified my issue adequately to warrant a response from this
group.

Thanks,

Jason

 

[Evan Keel]

My subject title might be a little misleading. This isn't about having a
"graphical" 3D representation of data, but about how to get data from an
excel spreadsheet imported and useful in Access. I am using Access 2007 to
design the database, but saving it in a 2003 format. The basic description
of the spreadsheet is:

The first two dimensions are the column and Row headings while the third is
multiple worksheets with the same information. The columns contain dollar
amounts that correspond to each row for each year (Year 0, Year 1, Year 2,
etc) and Row values correspond to certain areas like data center, Training,
Development, and Implementation. Each one of those areas contain sub
elements such as hosting, contract, system support etc. These values are
specified for multiple vendors, each in their own workbook.

As far as I can tell (understand/know) is that each vendor will have to have
their own table with the rows being the section totals without being able to
include the sub sections unless I want to add some major complexity to the
database.

I hope I have clarified my issue adequately to warrant a response from this
group.

Thanks,

Jason

How about?

Vendors (VendorID(PK), VendorName, etc)
Expense(ExpenseCode(PK), ExpenseDescription, etc)
VendorExpenses(VendorID(PK), ExpenseCode(PK), ExpenseYear(PK),
ExpenseAmount)

I'm not sure "expense" is the right word, but you get the idea...

Evan

 

[Jeff Boyce]

As Evan points out, you need one table with multiple vendors (and a vendorID
in your related table as a foreign key), rather than one-table-per-vendor.
That approach might be necessary if you were sticking with a spreadsheet,
but Access is a relational database.

You won't get the advantages (or ease of use) of Access'
relationally-oriented features/functions if you feed it 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP