Linking Excel and Access

E

Eliezer

Hello... I'm trying to set up an excel sheet in such a
manner that if a certain record exists (based upon it's
primary keys) in an Access database, the cell will return
X, and if not, it will return Y. Is there a method to hook
up access and excel like this? I tried using PivotTables
(based upon Excel help), but I'm not very experienced with
them and they did not produce the desired outcome. Thanks -
Eliezer
 
O

onedaywhen

For something this straightforward, take a look at SQL.REQUEST in the
Excel help files.
 
P

Paul Falla

Dear Eliezer
From the Excel Menu bar select Data/External Data/New
database query --- Select Ms Access Database --- Select
the database you want to query --- MS Query will open. Add
the required table(s) to the design grid and pull in the
required fields. Add any criteria and sorting that you
want and then select File/Return Data To Microsoft Excel.
Once the data has been returned to Excel you can add a
column to data to do the checking for you (possibly an if
statement)

Hope this helps

Paul
 
E

Eliezer

Paul - I tried to do it via this method already, but it
seems that Excel has trouble importing from a union select
query (which is the query I'm trying to work with in
Excel). Onedaywhen's suggestion seems to be working for me
so far... I posted a related question in the
thread "Variables within SQL.REQUEST." THanks for your
help!

Eliezer
 
O

onedaywhen

I have no trouble using a UNION when MS Access is the source e.g.

SELECT RefID FROM PersonalDetails
UNION
SELECT RefID FROM EarningsHistory
ORDER BY 1

worked for me. Note the usual gotcha: you can't use column names for
the ORDER BY clause, you must instead use their ordinal position.

If you want to use MS Query, post your schema so someone (maybe me)
can get it to work for you.
 
Top