Multiple Sql Queries Same Field

I have a table (Inventory_Line) where LID is auto increment and IID is a single number referring to the inventory date, PID is a numeric part#. We use this table for inventory.

LID IID NAME PID QTY --- --- ------ --- --- 1 1 Part A 213 12 2 1 Part B 200 15 3 2 Part A 213 9 4 2 Part B 200 7

We also have a table Order_Line

OLID OID NAME PID QTY ---- --- ------ --- --- 1 217 Part A 213 12 2 217 Part B 200 15 3 218 Part A 213 9 4 218 Part B 200 7

My goal is to show

((Previous Inventory Qty (Inventory_Line.IID=1)) AS PREV_INV + (ORDERED Qty (Order_Line.OID = 217 AND 218)) AS ORDERED - (Current Inventory Qty Inventory_Line.IID=2) AS CURRENT_INV) AS SOLD WHERE PID = X

The output would be like this:

PID NAME PREV_INV ORDERED CURRENT_INV SOLD --- ------ -------- ------- ----------- ---- 213 Part A 12 21 9 24

We are using MS Access and I have some experience with Joins but I am kind of stuck on how to pull this off. Any help would be appreciated.

-------------Problems Reply------------

Are you thinking of something on these lines?

SELECT t.pid,
Nz([pi].[qty], 0) + Nz([o].[qty], 0) - Nz([s].[qty], 0) AS dat
FROM (((SELECT DISTINCT pid
FROM inventory_line) AS t
LEFT JOIN (SELECT *
FROM inventory_line
WHERE iid = 2) AS pi
ON t.pid = pi.pid)
LEFT JOIN (SELECT *
FROM inventory_line
WHERE iid = 3) AS s
ON t.pid = s.pid)
LEFT JOIN (SELECT *
FROM order_line
WHERE oid = 217
OR oid = 218) AS o
ON t.pid = o.pid
WHERE (( ( t.pid ) = [Enter:] ))

The above needs some work, but what should be done would be clearer with more input on the desired output.



Re comments

SELECT t.pid,
t.name,
pi.qty,
o.q,
s.qty,
Nz([pi].[qty]) + Nz([o].[q]) - Nz([s].[Qty]) AS sold
FROM (((SELECT DISTINCT pid,
[Name]
FROM inventory_line) AS t
LEFT JOIN (SELECT pid,
qty
FROM inventory_line
WHERE iid = 1) AS pi
ON t.pid = pi.pid)
LEFT JOIN (SELECT pid,
qty
FROM inventory_line
WHERE iid = 2) AS s
ON t.pid = s.pid)
LEFT JOIN (SELECT pid,
SUM(qty) AS q
FROM order_line
WHERE oid = 217
OR oid = 218
GROUP BY pid) AS o
ON t.pid = o.pid
WHERE (( ( t.pid ) = [Enter:] ))

I couldn't work out the where the 24 in the SOLD column comes from but here is another solution for the rest

SELECT Order_Line.PID, Order_Line.NAME, DLookUp("[QTY]","[Order_Line]","[OID]=" & [OID]-1 & " AND PID=" & [PID]) AS PREV_INV, DSum("[QTY]","[Order_Line]","[PID]=" & [PID]) AS ORDERED, Order_Line.QTY AS CURRENT_INV FROM Order_Line;

Category:sql Views:0 Time:2012-01-19
Tags: sql ms access

Related post

  • Combining multiple SQL Queries into one statement 2012-01-14

    I am looking for a way to combine multiple SQL queries to save having to keep calling the database. I have three tables, one with a clubs details, one with the club facilities and the third combines them. Here is a simple version of the tables: t1 id

  • Best practice to execute multiple SQL queries using PHP PDO 2011-03-02

    What is the best practice to execute multiple SQL queries using PHP PDO? I have 4 tables and each of them is running on MyISAM. As such they do not have Foreign Key support. It's a one-to-many design whereby there is 1 main table and the other table

  • merge multiple sql queries 2011-11-02

    In the header of the pages of my site, I have multiple SQL queries. For example: $sql_result = mysql_query("SELECT blah2 FROM misc WHERE id='1'", $db); $rs = mysql_fetch_array($sql_result); $blah2 = $rs[blah2]; $sql_result = mysql_query("SELECT * FRO

  • Rails multiple SQL queries in one statement 2011-12-09

    In Rails, is there a way to run multiple SQL queries in one statement, say something like: UPDATE `dvd_actor` SET actor = replace(actor, 'Ó', 'Ó'); UPDATE `dvd_actor` SET actor = replace(actor, 'Á', CHAR(193)); UPDATE `dvd_actor

  • How to combine multiple SQL queries into a single one? 2012-03-10

    I have these multiple sql queries that I want to bundle together into one single query, so that I can avoid sending multiple requests to the database from my application ( I want to receive all of these data in one single shot) : 1) select pin, offic

  • PHP: multiple SQL queries in one mysql_query statement 2008-12-06

    So I have an SQL dump file that needs to be loaded using mysql_query(). Unfortunately, it's not possible to execute multiple queries with it. -> It cannot be assumed that the mysql command-line client (mysql --help) is installed -- for loading the

  • Testing for Performance in Multiple SQL Queries 2012-03-26

    I'm working to improve the efficiency of some SQL Queries on SQL-Server-2008. There are different ways of performing each query and I want to find the fastest of them. However, the issue that I'm having is that I am having trouble determining which i

  • How do I run multiple SQL queries per call to "query" using Zend_Db_Adapter? 2011-09-12

    This is a Zend_Application_Resource that I wrote to update the schema automatically when changes are made in a deploy. <?php /** * Makes sure the current schema version matches what we're expecting for this * particular version of the application.

  • nHibernate result from multiple SQL queries is empty 2011-11-10

    I'm grouping several SQL queries in a single multiquery. The queries are successfully executed. The result of multiQuery.List() is an ArrayList of ArrayLists. So far so good. However, the individual ArrayLists contain values of type {object[0]} or, i

  • How to excecute multiple SQL queries to pandas dataframes in parallel 2013-07-28

    Hi all Python Pandas gurus. I'm looking for a way to run some SQL in parallel with Python, returning several Pandas dataframes. I have code similar to below that serially runs 4 SQL queries against a MS SQL server database. Two of the queries have mu

  • PHP: Multiple SQL queries vs one monster 2009-07-03

    I have some really funky code. As you can see from the code below I have a series of filters that I add to query. Now would it be easier to just have multiple queries, with it's own set of filters, then store the results in an array, or have this mes

  • Dealing with multiple SQL queries within single find_by_sql 2010-11-28

    Please note that I understand I could do the following in a single SQL query that's not the point of the question however... I'm more curious about how rails deals with multiple queries. Let's say I have a Movies model with attributes such as title,

  • How to run multiple sql queries through one call in Mysql2 Gem in IRB? 2011-01-31

    I am playing around with mysql2 ( and mysql 5) gem and the results and the Enumerable results. I can run queries such as results = client.query("select now()") And I can also run queries such as results = client.query("select version()") But what I w

  • How to execute multiple SQL Queries (oracle) using Enterprise library within a single call 2011-07-15

    In our project , we are using Enterprise library for the data acess layer. And the back end database is Oracle. I have to execute multiple Queries (batching and no stored procedure) within a single call to the db to reduce the number of round trips .

  • queuing multiple SQL queries as one string, server behaviour? 2011-07-15

    When you have eg. 3 queries in a row that you submit to a SQL database as a single string, separated by semicolons, and another user does that exact same thing at the same time, will our queries be seen as a "group" or "queue" of queries which will a

  • perform multiple sql queries when clicking a submit button on PHP Page 2011-10-05

    I have a PHP page, that on clicking the submit button process a few MySQL queries. in MySQL PHPMyAdmin the query works 100% and both queries execute. However, when in my PHP Code the queries do not execute. Any help would be appreciated, I bet this i

  • Combining multiple SQL Queries 2009-05-15

    I want to make a query to list cats that took longer than average cats to sell? I have five tables: Animal, Sale, AnimalOrderItem, AnimalOrder, and SaleAnimal Animal table: AnimalID, Name, Category (cat, dog, fish) SaleAnimal table: SaleID, AnimalID,

  • how to sum multiple sql queries together? 2009-11-13

    I'm trying to run multiple queries on multiple tables- similar to "select count(*) from TableA where x=1" per table. What I'd like to do, is get all of the count(*) values that are returned and sum them into a single value... Any ideas? -------------

  • Combining columns from multiple SQL queries 2010-08-17

    I have more than one sql query(25 to be exact) and want to return the results as one row of results. for example... --Get the Barcodes that have a EnvPrint Record in the Database select count(distinct jtbarcode) as CountEP from jobtracker with(nolock

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.105 (s). 11 q(s)