Is Access the right Program

[Guest]

I am trying to set it up where I can do a search on 5 separate excel
spreadsheets that are all maintained by different people. Primarily I would
be searching for specific people using a name, birthdate or employee ID#. I
would need to be able to identify the spreadsheet the name came from but
other than that all the info needed would be contained within that
spreadsheet.

I don't normally need any reports but in the future it may be required. I
have limited experience with Access although have used Excel quite a bit. I
am looking for the most efficient way to perform these type searches. Can
anyone give me some direction on a way to do this in Access or should I
revert to Excel and create a giant spreadsheet pulling from all the above.

I appreciate any and all suggestions.

Thanks

 

[Guest]

I had a similar need, except I had 32 spreadsheet to check, and converted
them into a access database. I would definitely recommend this approach. It
will be labor intensive initially, but in the medium to long-term will be
greatly worth the investment of time. You'll be capable of doing much more,
with far less efforts. You will also be in a position, as you stated, to
generate reports in a flash, create custom queries, setup security.... The
list goes on!

 

[John W. Vinson]

I am trying to set it up where I can do a search on 5 separate excel
spreadsheets that are all maintained by different people. Primarily I would
be searching for specific people using a name, birthdate or employee ID#. I
would need to be able to identify the spreadsheet the name came from but
other than that all the info needed would be contained within that
spreadsheet.

As Daniel suggests, this application may be better suited to Access than to
Excel. Excel's a spreadsheet, a top-notch one; Access is a database. Searching
for, sorting and reporting on large sets of data is really more a database
than a spreadsheet task.

What you can do (and will need to do if these spreadsheets are being
dynamically edited) is use File... Get External Data... Link to link to the
Excel spreadsheets. This will give you a linked Table in Access, on which you
can base queries.

In particular, you'll need a UNION query to do the search you're describing.
If you've linked five spreadsheets (DeptA, DeptB, ... etc.) you can create a
new Query based on any one of them. Select all the fields that you want to see
and/or search; then select View... SQL from the menu. You'll see something
resembling

SELECT [Employee ID#], [Name], [birthdate], <other fields> FROM DeptA;

Edit this - in the SQL window, you can't do it in the grid - to

SELECT "DeptA" As Department, [Employee ID#], [Name], [birthdate], <other
fields> FROM DeptA
UNION ALL
SELECT "DeptB" As Department, [Employee ID#], [Name], [birthdate], <other
fields> FROM DeptB
UNION ALL
SELECT "DeptC" As Department, [Employee ID#], [Name], [birthdate], <other
fields> FROM DeptC
UNION ALL

<etc etc>

The number and datatypes (but not the fieldnames) of the fields must match
exactly from one SELECT to the next, so if the spreadsheets don't all have the
same structure you may need to select only those fields that they have in
common.

You can then base a Query *on this query* and put criteria on the fields. Post
back if you have problems!

John W. Vinson [MVP]