Ms-access – query for showing columns from various tables, grouping and showing calculated field in each row of the result

computed-columngroup byms access

I am doing a stock management system.

In one table I store items:

ITEM<br>
id_item<br>
item_name<br>

ORDER<br>
id_order<br>
date<br>

ORDER_ITEM<br>
id_ord_item<br>
id_order : foreign key to ORDER id_order primary key<br>
id_item : foreign key to ITEM table.<br>
qty_ordered

STOCK_IN : this table reflects item entries for related item quantities for orders<br>
id_entry<br>
id_ord_item: foreign key to ORDER_ITEM table.<br>
description: optional<br>
in_qty : In of a specific quantity for a specific item in a specific order<br>
in_date: the date where the item quantity arrives to warehouse<br>

Having this structure: I wanted to show a report or query table like this containing the following fields:

item_name<br>,
date (order date)<br>,
description,<br>
qty_ordered,<br>
in_qty,<br>
in_date.<br>
qty_left (calculated field) qty_ordered-sum of in_qty to date<br>

Not sure if I should store the sum of arrive quantities to date in one column and use that to calculate the quantity left then.
Next to those, I want to show a calculated field that in each row that is the pending qnantity left to arrive, which should be the aty_ordered – the sum of arrived item quantity to date (in_qty).
Also, my goal was to show only the items that have a quantity left to arrive for a selected order. Items can come in different shippings, not all quantity ordered at once, and I want to register the date each shipping comes and the quantity of arrival of each item and the date.

I was thinking of not storing calculated values in table fields but I'm not sure if I should.
Also I thought of showing all this in a single query, but I'm not sure if this is possible.
Maybe I should make an agregate query for showing the order and it's item with the item quantity ordered, it's total quantity arrived to date and then in a separate query list all records of the ins of that item and their dates.

I can't figure out a way to do this propery. I thogght I could show a list of items grouped by items inside those orders, and then below list all entries.

I hope someone can though some light on this to me. I'm not sure how to approach this problem.

Best Answer

Basic is that you have all Tables set and also defined the relationship.

Then you add a new query.

Then you pull in all Table that you need, if your relationships are correctly set, all tables should ha a connection.

Now you pull all needed columns into the part below

and add a column that has the difference betwenn qty ordered and inqty

Now you have a corect query to run.

As SQL it looks like

SELECT ITEM.item_name, STOCK_IN.description, ORDER.date_order, ORDER_ITEM.[qty_ordered]
, STOCK_IN.in_qty, STOCK_IN.in_date, [qty_ordered]-[in_qty] AS qty_left
FROM ([ORDER] 
      INNER JOIN (ITEM 
       INNER JOIN ORDER_ITEM ON ITEM.id_item = ORDER_ITEM.id_item) ON ORDER.id_order = ORDER_ITEM.id_order) 
       INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item;

For your query to have the complete quantity for all orders

SELECT ITEM.item_name, STOCK_IN.description, ORDER.date_order, ORDER_ITEM.[qty_ordered], STOCK_IN.in_qty, STOCK_IN.in_date, [Sum1] AS qty_left
FROM 
    ([ORDER] INNER JOIN (
                                           ( ITEM INNER JOIN (SELECT ORDER_ITEM.id_item
                                                                       ,SUM( [qty_ordered]-[in_qty]) AS Sum1
                                                                       FROM ([ORDER] INNER JOIN ORDER_ITEM ON ORDER.id_order = ORDER_ITEM.id_order) INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item
                                                                       GROUP BY ORDER_ITEM.id_item) qtysum ON qtysum.id_item = ITEM.id_item
                                           )
INNER JOIN ORDER_ITEM ON ITEM.id_item = ORDER_ITEM.id_item
) ON ORDER.id_order = ORDER_ITEM.id_order) 
INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item;

Result:

Related Solutions

Pivoting data for stacked charts (grouping and creating new columns for each distinct value)

Another way to do this if the columns are known is using an aggregate and a CASE statement:

SELECT d,
  sum(case when loc = 'Baltimore' then v else 0 end) as Baltimore,
  sum(case when loc = 'Houston' then v else 0 end) as Houston,
  sum(case when loc = 'Chicago' then v else 0 end) as Chicago
FROM test
group by d
order by d;

See SQL Fiddle with Demo

If you want to do this dynamically, then you can create a procedure to generate the SQL statement:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select d ';

    begin
        for x in (select distinct loc from test order by 1)
        loop
            sql_query := sql_query ||
                ' , sum(case when loc = '''||x.loc||''' then v end) as '||x.loc;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from test group by d order by 1';

        open p_cursor for sql_query;
    end;
/

Then to call it:

variable x refcursor
exec dynamic_pivot(:x)
print x

the result is:

D                                BALTIMORE    CHICAGO    HOUSTON
------------------------------- ---------- ---------- ----------
01-JAN-12 12.00.00.000000 AM            36         55         36
01-FEB-12 12.00.00.000000 AM            49         51         58
01-MAR-12 12.00.00.000000 AM            11         18         13

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

Best Answer

Related Solutions

Pivoting data for stacked charts (grouping and creating new columns for each distinct value)

Sql-server – SQL join query to show rows with non-existent rows in one table

Related Question