Jump to content

Search the Community

Showing results for tags 'ms-access'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • W3Schools
    • General
    • Suggestions
    • Critiques
  • HTML Forums
    • HTML/XHTML
    • CSS
  • Browser Scripting
    • JavaScript
    • VBScript
  • Server Scripting
    • Web Servers
    • Version Control
    • SQL
    • ASP
    • PHP
    • .NET
    • ColdFusion
    • Java/JSP/J2EE
    • CGI
  • XML Forums
    • XML
    • XSLT/XSL-FO
    • Schema
    • Web Services
  • Multimedia
    • Multimedia
    • FLASH

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Languages

Found 1 result

  1. I have two tables with primary key and foreign key (ItemId) Table 1 (Items) ItemId ItemName OPStock OPStockValue 1 Pen 100 1000 2 Pencil 50 5000 Table 2 (Inventory) TransactionType VchNo ItemID ItemName Qty TaxableAmt EntryDate Sale 2 1 KFL UREA NEEM COTED 70 16940 16-04-2020 Sale 1 5 IPL MOP 60 49200 16-04-2020 Sale 3 2 IPL DAP (IMPORTED) 60 58800 16-04-2020 Sale 4 2 IPL DAP (IMPORTED) 10 9800 16-04-2020 Sale 4 5 IPL MOP 50 41000 16-04-2020 Sale 5 2 IPL DAP (IMPORTED) 10 9800 23-04-2020 Purchase 1 67 PADDY SEEDS DAYAL GOLD 30 201000 23-04-2020 Purchase 1 97 PADDY SEEDS DAYAL GOLD 15 100500 23-04-2020 Sale 8 2 IPL DAP (IMPORTED) 10 9800 24-04-2020 Purchase 2 1 KFL UREA NEEM COTED 1600 381008.16 24-04-2020 Purchase 3 7 CASTER OIL CAKE ORGANIC 500 261250 24-04-2020 Purchase 4 38 PADDY SEEDS MANSURI 175 682500 02-05-2020 Sale 19 39 PADDY SEEDS MTU-1001 5 20750 02-05-2020 Sale 19 40 PADDY SEEDS MTU7029 1.25 4812.5 03-05-2020 Now When I Pass @FromDate and @Todate I want to output like below: EntryDate ItemName OpQty PurchQty SaleQty ClosingBal database link What I have tried: I am trying to solve using Left Join But the result is not correct SELECT I.ItemID, I.ItemName, I.OPStock AS OpStock, I.OPStockValue AS OpValue, SUM(SP.Qty) AS InQty, SUM(SP.TaxableAmt) AS InValue, SUM(SS.Qty) AS OutQty, SUM(SS.TaxableAmt) AS OutValue FROM items AS I LEFT JOIN inventory AS SP ON SP.ItemID = I.ItemID LEFT JOIN inventory AS SS ON I.ItemID = SS.ItemID WHERE SP.TransactionType = 'Purchase' AND SS.TransactionType = 'Sale' GROUP BY I.ItemID ORDER BY I.ItemName ASC
×
×
  • Create New...