Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Red face Unanswered: Newbie.. Plz Help!!

    here 's a problem for you!

    This databse stores ITEMS , DEPARTMENTS , TRANSACTIONS : ( ISSUE & RECEIVE )

    Each department should have its own balance of items existing in it by issue and receive TRANSACTIONS date wise in a certain quantity .

    (Under reports..)All i require is:

    (1) To get the net quantity (after receiving / issue) of an item departmentwise.
    (2) To get the quantity of an item department + date wise.

    To acheive the two, I have made these tables:

    Items: itemid, description, name
    Department: deptid, name
    Transaction: transactionid, date, desc, itemid (foreign key from items table), deptid (foreign key from department)
    NetStatus
    netstatus: itemid (fk) deptid (fk) netQuantity

    Just guide a bit about maintaining that "netQuantity" of the departments )

    All i am confused about the 2 Reports ( sql queries, i mean ).. Any sort of help is more than welcome

    I gotta do this assignment :>


    (I have tried to study the inventory code as suggested by some forum participant but ended up with nuffin..)

  2. #2
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    The first problem is that you should never store a computed field in a table. The reason is that you have to update the computed field anytime one of the values that it is based on changes. Use a sql query to sum, total, or whatever when you do the reporting. Using the group by clause allows you to sum the items by group. For instance:

    select sum(QtyOrdered) from Orders
    group by Customer

    will give you a total for each customer
    Cathy

  3. #3
    Join Date
    Feb 2004
    Posts
    10

    The first problem is that you should never store a computed field in a table.
    Ok rightly so.. NetQuantity excluded

    Items: itemid, description, name
    Department: deptid, name
    Transaction: transactionid, transactionTYPE (Boolean: receive / issue), date, itemid (foreign key from items table), deptid (foreign key from department), TransactionAmount


    select sum(QtyOrdered) from Orders
    group by Customer

    will give you a total for each customer
    2ndly I can separate transactions department+datewise like this..


    SELECT
    Transactions.trn_date, Transactions.trn_type, Transactions.trn_dpt_id, Transactions.trn_itm_id, item.itm_name, SUM(Transactions.trn_itm_qty)

    FROM
    item INNER JOIN (dept INNER JOIN Transactions ON dept.dpt_id=Transactions.trn_dpt_id) ON item.itm_id=Transactions.trn_itm_id

    GROUP BY
    Transactions.trn_date, Transactions.trn_type, Transactions.trn_dpt_id, Transactions.trn_itm_id, item.itm_name;


    Now.. Need some further help about how can i separate 'Receive' from 'Issue' type transactions to sum them Department+Datewise as above. The difference of which is gonna tell me the net quantity for each item in a given department.

    Wait.... what if for the sake of simpicity.. how about replacing these two fields (TransactionType & TransactionAmount) with: ReceivedAmount and IssuedAmount

    ( Yes, it will certainly lead to null Values let me hear your opinion now ! )
    Last edited by hotspot; 02-24-04 at 14:48.

  4. #4
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    If I understand this correctly, your trn_types are 'Receive' and 'Issue'. If that's the case, you will get a row with the sum for each trn_date, trn_type ('Receive' or 'Issue'), trn_dpt_id, and trn_itm_id.

    Are you looking for one query to give you a net total (Received - issue) or do you want a recordset that you can traverse? What will you be doing with the result set? Do you store the issued amount as a negative number?
    Cathy

  5. #5
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    SELECT Transactions.trn_date, Transactions.trn_type as ‘Received’, 0 as ‘Issued’, Transactions.trn_dpt_id, Transactions.trn_itm_id, item.itm_name, SUM(Transactions.trn_itm_qty)
    FROM item INNER JOIN (dept INNER JOIN Transactions ON dept.dpt_id=Transactions.trn_dpt_id) ON item.itm_id=Transactions.trn_itm_id
    GROUP BY Transactions.trn_date, Transactions.trn_type, Transactions.trn_dpt_id, Transactions.trn_itm_id, item.itm_name
    Where Transactions.trn_type = ‘Received’

    Union All

    SELECT Transactions.trn_date, 0 as ‘Received’, Transactions.trn_type as ‘Issued’, Transactions.trn_dpt_id, Transactions.trn_itm_id, item.itm_name, SUM(Transactions.trn_itm_qty)
    FROM item INNER JOIN (dept INNER JOIN Transactions ON dept.dpt_id=Transactions.trn_dpt_id) ON item.itm_id=Transactions.trn_itm_id
    GROUP BY Transactions.trn_date, Transactions.trn_type, Transactions.trn_dpt_id, Transactions.trn_itm_id, item.itm_name
    Where Transactions.trn_type = ‘Issued’

    For a report - have a column for the 'Received' and a column for the 'Issued' fields. In the group footer, total the 'Received' and the 'Issued' columns. Subtract the issued from the received and you'll get the net amount. You can have multiple groups in the report so that you can display this all the way down to the date.
    Cathy

  6. #6
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Oops, put the order by last or the whole query will bomb.
    Cathy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •