Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Queries / Excel
You folks always have lots of good insights - thanks very much for your
past responses... hopefully you'll be able to point me in a good direction for this dillemma. I do a lot of work between Excel and a Sybase database. One of the sheets I put together queries our database for a list of items where the shipped quantity is less than the requested quantity. I'd like to limit the date range from which it pulls - which is possible if I go into the data menu and physically edit the query every time. What I'd like to do is to make it easier - so that I don't have to modify it all the time for the people who use it. Is there a way for me to place a text field on the spreadsheet and a query button, so that I can have the user enter the date into the text box, click the query button, and have Execel go out to the database, query it based on the original string, which looks like this: -------------- SELECT v_TransferWorksheetData.WRK_Name, v_TransferWorksheetData.ITW_RequestDate, v_TransferWorksheetData.ITW_SupplyDate, v_TransferWorksheetData.WRK_TimestampCommitted, v_TransferWorksheetData.ITD_RequestQuantity, v_TransferWorksheetData.ITD_SupplyQuantity, v_InventoryMaster.INV_ScanCode, v_InventoryMaster.INV_ReceiptAlias FROM ecrs.v_InventoryMaster v_InventoryMaster, ecrs.v_TransferWorksheetData v_TransferWorksheetData WHERE v_TransferWorksheetData.ITD_INV_FK = v_InventoryMaster.INV_PK AND ((v_TransferWorksheetData.WRK_Name Like '%HQ%') AND (v_TransferWorksheetData.WRK_Status Like '%Shipped%') AND (v_TransferWorksheetData.ITD_SupplyQuantity=0) AND (v_InventoryMaster.STO_Name Like '%Headquarters%') AND (v_InventoryMaster.PI2_Description Like 'WH%') AND (v_TransferWorksheetData.WRK_TimestampCommitted{t s '2005-06-01 00:00:00'}) OR (v_TransferWorksheetData.WRK_Name Like '%HQ%') AND (v_TransferWorksheetData.WRK_Status Like '%Committed%') AND (v_TransferWorksheetData.ITD_SupplyQuantity=0) AND (v_InventoryMaster.STO_Name Like '%Headquarters%') AND (v_InventoryMaster.PI2_Description Like 'WH%') AND (v_TransferWorksheetData.WRK_TimestampCommitted{t s '2005-06-01 00:00:00'})) ORDER BY v_TransferWorksheetData.WRK_Name ---------------- You'll note the date / time stamp at the very end here... that's what I need to modify. I already have the sheet set up to use filters, so the user can (if they're savvy enough) just filter for anything past a certain date... but not all of my office users are even that savvy. Sorry if the sql is a little messy - the msquery tool tends to create some redundant code. If anyone has some suggestions as to texts or sites that provide good in-depth Excel / SQL integration tips, please feel free to pass them on. I'm always looking for new things. Ben |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Queries / Excel
Nick Hodge has an example of setting up a parameter query using a query
tables. You don't say what mechanism you are using, but this may be useful. There should be an option to get the value from the parameter from a worksheet cell. http://nickhodge.co.uk/gui/datamenu/...taexamples.htm -- Regards, Tom Ogilvy "Ben Adler" wrote in message om... You folks always have lots of good insights - thanks very much for your past responses... hopefully you'll be able to point me in a good direction for this dillemma. I do a lot of work between Excel and a Sybase database. One of the sheets I put together queries our database for a list of items where the shipped quantity is less than the requested quantity. I'd like to limit the date range from which it pulls - which is possible if I go into the data menu and physically edit the query every time. What I'd like to do is to make it easier - so that I don't have to modify it all the time for the people who use it. Is there a way for me to place a text field on the spreadsheet and a query button, so that I can have the user enter the date into the text box, click the query button, and have Execel go out to the database, query it based on the original string, which looks like this: -------------- SELECT v_TransferWorksheetData.WRK_Name, v_TransferWorksheetData.ITW_RequestDate, v_TransferWorksheetData.ITW_SupplyDate, v_TransferWorksheetData.WRK_TimestampCommitted, v_TransferWorksheetData.ITD_RequestQuantity, v_TransferWorksheetData.ITD_SupplyQuantity, v_InventoryMaster.INV_ScanCode, v_InventoryMaster.INV_ReceiptAlias FROM ecrs.v_InventoryMaster v_InventoryMaster, ecrs.v_TransferWorksheetData v_TransferWorksheetData WHERE v_TransferWorksheetData.ITD_INV_FK = v_InventoryMaster.INV_PK AND ((v_TransferWorksheetData.WRK_Name Like '%HQ%') AND (v_TransferWorksheetData.WRK_Status Like '%Shipped%') AND (v_TransferWorksheetData.ITD_SupplyQuantity=0) AND (v_InventoryMaster.STO_Name Like '%Headquarters%') AND (v_InventoryMaster.PI2_Description Like 'WH%') AND (v_TransferWorksheetData.WRK_TimestampCommitted{t s '2005-06-01 00:00:00'}) OR (v_TransferWorksheetData.WRK_Name Like '%HQ%') AND (v_TransferWorksheetData.WRK_Status Like '%Committed%') AND (v_TransferWorksheetData.ITD_SupplyQuantity=0) AND (v_InventoryMaster.STO_Name Like '%Headquarters%') AND (v_InventoryMaster.PI2_Description Like 'WH%') AND (v_TransferWorksheetData.WRK_TimestampCommitted{t s '2005-06-01 00:00:00'})) ORDER BY v_TransferWorksheetData.WRK_Name ---------------- You'll note the date / time stamp at the very end here... that's what I need to modify. I already have the sheet set up to use filters, so the user can (if they're savvy enough) just filter for anything past a certain date... but not all of my office users are even that savvy. Sorry if the sql is a little messy - the msquery tool tends to create some redundant code. If anyone has some suggestions as to texts or sites that provide good in-depth Excel / SQL integration tips, please feel free to pass them on. I'm always looking for new things. Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel queries dsn | Excel Discussion (Misc queries) | |||
Excel queries | Excel Worksheet Functions | |||
Excel Queries | Links and Linking in Excel | |||
Excel Queries | Excel Programming | |||
Web queries and Excel | Excel Programming |