Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Insert and referencing other database values (Read 2953 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Insert and referencing other database values
15.02.06 at 14:57:52
Noel from Danemark asked the following question.
I'm probably trying to do something which cannot be done, i.e. to put a lookup formula in a paradox table field. As I tried to
explain, the database I am using is the back-end to a stock management program called Samstock. I do not have Oracle installed - only the BDE as supplied with Samstock. The program allows for a link to an electronic cash register and enables the user to manipulate both stockkeeping data and financial data deriving from cash register transactions. For the initial set-up, though, the system is very cumbersome, as it only allows for data entry for one product at a time via a GUI. With something like 3000
products* each with its own product code (a 13-digit EAN barcode), it would take many days to populate the tables using this GUI. That's why I needed a tool to insert product data (supplied by the manufacturer as XL files) batchwise into the database, and SQL*XL has proved very useful for doing just this.
In one of the GUI dialogs (Orders), there are entry fields for the product code, quantity, price and so on, and an optional "Reference" text field. There is one field in the Product table which gives a unique, if cryptic, description of the product as displayed on the cash register, and I thought we could use this as the Reference field in the Orders table - hence my question.  Instead of having to type in the reference for each line in the order, it would be so much simpler to let the program find the reference in the Product table, where it is uniquely linked to the product code. Now do you understand the scenario?
To summarise, you want to insert rows into a table where one column needs to be looked up in another table. As an example consider the following tables:
table order: order_id, product_id, price
table product: product_id, price
Suppose you want to fill the table orders. We would like to enter order_id and product_id and we want the product.price to be looked up.
You can tackle this in two ways:

  • Using the SQLQueryValue SQL*XL Excel formula:
    For details about the SQLQueryValue function see:
    Enter the formula for each row to get the appropriate product price. Example:
    A1: order_id      B1: product_id      C1: price
    A2: 1234567      B2: 987654321      C2: =SQLQueryValue(concatenate("select price from product where product_id=",B2)
    A3: 1112222      B3: 333344444      C3: =SQLQueryValue(concatenate("select price from product where product_id=",B3)
    SQL*XL will display the value in cells C2 and C3 so it is all ready for uploading using SQL*XL's Multiple Rows Insert feature.
    This method would work fine for small rowsets. If the number of rows gets larger it will take some time for the queries to execute and you could better use the next technique.
  • Using the VLookup Excel formula:
    Using the same example you can use the VLookup Excel function to achieve the same.  
    First dump the product table in a sheet (for instance sheet2). Use the SQL*XL SQL dialog to run: select * from product;
    Suppose the product data fills the range Sheet2!A2:B6 with in column A the product_id and in column B the prices.
    Then use the vlookup function in Excel to lookup the price values from the dumped product table in sheet2:
    A1: order_id      B1: product_id      C1: price
    A2: 1234567      B2: 987654321      C2: =VLOOKUP(A2,Sheet2!A2:B6,2)
    A3: 1112222      B3: 333344444      C3: =VLOOKUP(A2,Sheet2!A2:B6,2)
    Excel will display the price value in cells C2 and C3 so it is all ready for uploading using SQL*XL's Multiple Rows Insert feature.
Back to top
« Last Edit: 15.02.06 at 15:18:14 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1