URGENT: Help needed for Access.

[AccessDodo]

I am new at MS Access but have been tasked to create a
Database for my company.

Background:

7 subsidiaries have submitted their Excel spreadsheets
with the following headings,

Institution_Name Material_Group Product_Description
Vendor_Name Amount_Paid


Problem:

I need to be able to cross search all 7 spreadsheets
utilizing either Institution Name, Material group etc.

1). Should I integrate all 7 spreadsheets into ONE lengthy
table? Can't do it in excel... too long. Don't know how to
integrate the tables in Access.

2). Or, can Access be used to cross search all 7
spreadsheets? If so, pls tell me how, thanx.



Your help in this matter is greatly appreciated.

Thank you and best regards.


Mark

 

[John Nurick]

Hi Mark,

A lot depends on how tidy the spreadsheets are. If they contain
headings, sub-headings, subtotals, merged cells and so on - or if the
columns have a mix of numeric and text data - it can be slow and
frustrating process importing them into Access. On the other hand if
they include nothing but one row of column names and the block of data,
with no mixed data types, they will usually import easily.

The thing to do is to create a new Access database. In that, create one
table with the fieldnames below plus one more field to identify the
source of that particular row of data: let's call it Subsidiary. If you
want to keep a record of when the data was imported, add another field
(let's call it TimeStamp) and give it a default value of
Now()

Next, import one of the 7 spreadsheets to a new, temporary table. Then
create an Append query (see Help) to append all the records in the
temporary table to the main table - plus a calculated field like this:
Subsidiary: "XXX"
where XXX is the name of the subsidiary. Run this query; delete the
records from the temporary table and import the next spreadsheet to the
empty table; and repeat until done.

Finally, compact your database (to retrieve the space occupied by the
temporary data), build a form to display the data, and study "Filter by
Form" and "Filter by Selection".