Excel query Access DB

[Hans]

I am using Excel to write invoices. The columns in the excel invoice
sheet are product ID, Name, Price, Quantity and Total price. All
product information is stored in a separate Access DB, but because I am
not a wiz I currently enter all data manually.

To minimize typing and errors I would like the spread sheet to
automatically retrieve specific values (name and price) from the
product table in access using the product ID as criteria. How can that
be done?

 

[Earl Kiosterud]

Hans,

Make a query to retrieve the data from Access. Data - Import External
Data - New database query.

I wonder why you're doing the invoice in Excel. It will probably be easier
to start your invoicing in the Access data base. Nice screen and print
layouts too, not limited to the layout of a worksheet.

Earl Kiosterud
mvpearl omitthisword at verizon period net

 

[Hans]

Earl, thanks for your suggestion, but I am still having trouble. I have
tried to use the query and I can connect just fine to access. My
problem is that I can't get the query to accept the criteria from the
reference cell (the one with the product ID). I was thinking maybe to
use one of the LOOKUP functions, but I am not sure they will do the
job.

I am actually quite happy with the layout of the invoice I have (even
though it is in Excel) and I would very much prefer to continue using
it.

Any additional suggestions you might have are most welcome!

 

[gocush]

Hans

Are you interested in permanently moving the name and price data to a
table in xl? If so then a vlookup would easily take care of your needs
and you would have all your application in one file.

If you still prefer the cross- application style try:

http://www.bygsoftware.com/

they have example xl workbooks which query an Access db.