Filtering Access records into an Excel Spreadsheet

[BajanPOET]

I have been working on an external query from Access into Excel where I want to pull data from 3 fields (Description, Qty and Unit Cost) but have the data filtered on Requisition Number.

Currently I have the desired cells in my Excel spreadsheet populated with data from the Access table. However, since I have several test requisitions, their details are all showing up in the Purchase Order query. I need the SQL statement to be written in such a way that I can generate one purchase order for each requisition (filtered by Requistion number.)

Is there a way to include the Requisition number in my query and place it in a different cell than the range I have the original 3 items in? The Qty, Description and EstPrice are logically one behind the other, but I do have to put the requisition number on the Purchase order form for tracking purposes. The problem is that I need to place the Req# at someplace like E16, but the description etc goes into the range B20-D20.

I'm new to VBA, so go easy!

GOD is in charge, though men may say, "Not so!

 

[PHV]

Create your query in another sheet.
In the SQL code add a parametized criteria, eg:
SELECT [Req#], Qty, Description, EstPrice
FROM yourTable
WHERE [Req#] = [Which Requisition number ?]

Then in E16 and B20-D20 you simply reference the cells of the first row of data returned by the query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Ok, this sounds interesting. If I wanted to create an Purchase Order based on the last entered requisition I would need to isolate the last Requisition number so that I could send it to the query. How do I store the last Req # in a variable that I can pass to this query (to answer the 'Which Requisition number?' question)

GOD is in charge, though men may say, "Not so!

 

[PHV]

How, in SQL, do you retrieve the last Requisition number ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Can I copy the Req. # to a variable and then pass that variable as a parameter from Access to the Excel query?

GOD is in charge, though men may say, "Not so!