Filtering Query Results from Access into Excel

[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.)

How do I form the SQL statement if I am importing data from Access into Excel using the Data | Import External Data | New database Query? How do I use the Microsoft Query application to isolate one requisition number at a time (hence generating one purchase order at a time?)

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

 

[PHV]

MS-Query admits Cell's value as parameter ...

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, so I would have to modify my query in order to place the Requisition number in a cell on the sheet and then use that as my parameter?

How do I use a cell as a parameter in a query?

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

 

[lespaul]

no answer to your question.....but one to you....why would you want to pull the information into Excel and create the Purchase order there instead of creating a Purchase order report in Access which can automatically create a PO for each unique requistion number?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[BajanPOET]

Well to answer that question, my boss asked for it in this format and I was trying to see if I could get it to work. There is already a formatted Excel Purchase Order that they wanted me to use.

I think the reason is that they wanted to email the completed Purchase Order straight from Excel to the Accounts Dept, who would probably want the Purchase Order in Excel format.

But I'll work it from that angle as well and see which one they like best.

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

 

[BajanPOET]

In fact, I have another question. 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.

If I can do this, both my problems will be over - I'll be able to reference the Req# for tracking purposes as well as use it in my query to isolate the purchase orders.

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

 

[PHV]

This is much an Excel question than a JetSQL one.
Better to ask here: forum707 or forum68

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