Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006
    Posts
    37

    Unanswered: Basic Query Question

    Guys,

    I have the following code set to run a query and give me the sum of inventory movements of a particular type. The problem is that I need it to show a value for all items from tblItems that have a material type = "RM" as shown. When I use the where clause on movement type "262" it only gives me totals for items that have that movement type. To reiterate: I need it to show a 0 value for items that didn't have any of that movement type that day. CAn anyone give me a hand here? I have tried using some fallbacks like the Nz() function but it doesn't seem to be working...

    Code:
    SELECT tblItems.Item, Sum(tblRawMaterialDataDump.Quantity) AS InvReturnFromProd
    FROM tblItems LEFT JOIN tblRawMaterialDataDump ON tblItems.Item = tblRawMaterialDataDump.Item
    WHERE (((tblItems.MaterialType)="RM") AND ((tblRawMaterialDataDump.MovementType)="262"))
    GROUP BY tblItems.Item;
    I am sure this is something simple I am missing...

  2. #2
    Join Date
    Sep 2006
    Posts
    265
    I would suggust taking tblRawMaterialDataDump.MovementType)="262" out of the Where and putting this into the Select as:

    Movement: iif(tblRawMaterialDataDump.MovementType="262",[tblRawMaterialDataDump.Quantity,0)

    ie only counts "262" movements

    Simon

  3. #3
    Join Date
    Feb 2006
    Posts
    37
    Quote Originally Posted by SimonMT
    I would suggust taking tblRawMaterialDataDump.MovementType)="262" out of the Where and putting this into the Select as:

    Movement: iif(tblRawMaterialDataDump.MovementType="262",[tblRawMaterialDataDump.Quantity,0)

    ie only counts "262" movements

    Simon
    Thank you so much that seems to be working quite well. I need to test it out in a few other places but I think that has it.

Posting Permissions

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