Hi Geeks,

I have developed an Invoice application with stock maintenance using access database and vb 2005. Everything is fine in the part of transaction wise other reports and all. But I am unable to derive the stock statement and I doesn't know how to derive the stock statement.

I have tables for OpeningStock, Purchases, Sales. When a user selects stock statment i want to show the stocks filtered by the selected godown, product, Start and End Date

The calculation part of the report is the big issue to me.
if a user login to a particular godown and searching for stock report based on start and end date on all products available, the data should be compiled as below

Report format will be as

  Sl    Product OP.STOCK    PURCHASE    SALES   CLO.STOCK
   1    MANGO     200        300         350       150
   2    APPLE     150         50         200         0
   3    BANANA    300        350         500       150

For Op.Stock
1. Every Products Quantity in the OpeningStock table < Start Date (+)
2. Every Products Quantity in the Purchases table < Start Date (+)
3. Every Products Quantity in the Sales Table < Start Date (-)

For Purchases
1. Every Products Quantity in the Purchase table Between >=Start Date and <=End Date

For Sales
1. Every Products Quantity in the Sales table Between >=Start Date and <=End Date

For Clo.Stock
Op.Stock + Purchases-Sales

How can I derive the report?
Please help me to derive the report. Thanks

Recommended Answers

All 2 Replies

As has been said numerous times before, we DO NOT do your homework for you! Make an effort to write the code, post it here, and we will consider helping you.

Sorry for the above,
This is the case what i am facing:

I have 5 datatables which having each 3 columns (ID, Brand, Quanitiy) to derive the opening, purchase, sales and closing stock.

  1. The First 3 datatables should be merged and sum up to derive the opening stock as on given period.
  2. 4 th datatable is for Purchases done for the given period.
  3. The last one Datatable is for Sales done for the given period.

Here is my question:
1. How can i merge the 3 datatables and using the first 2 columns and summing the 3rd column values?
2. How can i use the tables in crystal reports?

Here is my code for datatable derived for the above.

 Dim con As New ClassConnection 
 If con.Conn.State = ConnectionState.Closed Then con.Conn.Open()

  Dim sql As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum (tblOp_Details.Net_Qty) AS Quantity FROM tblOp_Stock INNER JOIN (tblBrand INNER JOIN" & _
    " tblOp_Details ON tblBrand.B_ID = tblOp_Details.B_ID) ON tblOp_Stock.Stk_ID = tblOp_Details.Stk_ID" & _
    " WHERE tblOp_Stock.God_ID = @GID GROUP BY tblBrand.B_ID, tblBrand.B_Name"

    'To get Purchases < start date and adding to the opening stock
    Dim sql1 As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum (tblPur_Details.Net_Qty) AS Quantity FROM tblPurchase INNER JOIN (tblBrand INNER JOIN" & _
    " tblPur_Details ON tblBrand.B_ID = tblPur_Details.B_ID) ON tblPurchase.Pur_ID = tblPur_Details.Pur_ID" & _
    " WHERE tblPurchase.God_ID = @GID AND tblPurchase.Rec_Date < @SDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"

    'To get Sales < Start date and subtracting to the above
    Dim sql2 As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum (tblSales_Details.Net_Qty) AS Quantity FROM tblSales INNER JOIN (tblBrand INNER JOIN" & _
    " tblSales_Details ON tblBrand.B_ID = tblSales_Details.B_ID) ON tblSales.Sale_ID = tblSales_Details.Sale_ID" & _
    " WHERE tblSales.God_ID = @GID AND tblSales.Sale_Date < @SDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"
    'The above 3 condition is for deriving opening stock as on given date

    'To get Purchases >= Start Date and <= Start Date
    Dim sql3 As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum(tblPur_Details.Net_Qty) AS Quantity FROM tblBrand INNER JOIN (tblPurchase INNER JOIN" & _
    " tblPur_Details ON tblPurchase.Pur_ID = tblPur_Details.Pur_ID) ON tblBrand.B_ID = tblPur_Details.B_ID" & _
    " WHERE tblPurchase.God_ID = @GID And tblPurchase.Rec_Date >= @SDate And tblPurchase.Rec_Date" & _
    " <= @EDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"
    'The above condition is for deriving Purchases as on given date

    'To get Sales >= Start Date and <= Start Date
    Dim sql4 As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum(tblSales_Details.Net_Qty) AS Quantity FROM tblBrand INNER JOIN (tblSales INNER JOIN" & _
    " tblSales_Details ON tblSales.Sale_ID = tblSales_Details.Sale_ID) ON tblBrand.B_ID = tblSales_Details.B_ID" & _
    " WHERE tblSales.God_ID = @GID And tblSales.Sale_Date >= @SDate And tblSales.Sale_Date <= @EDate" & _
    " And tblSales_Details.S_Active = @SAct GROUP BY tblBrand.B_ID, tblBrand.B_Name"
    'The above condition is for deriving Sales as on given date

    Dim da As New OleDb.OleDbDataAdapter(sql, con.Conn)
    Dim da1 As New OleDb.OleDbDataAdapter(sql1, con.Conn)
    Dim da2 As New OleDb.OleDbDataAdapter(sql2, con.Conn)
    Dim da3 As New OleDb.OleDbDataAdapter(sql3, con.Conn)
    Dim da4 As New OleDb.OleDbDataAdapter(sql4, con.Conn)

    da.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da1.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da1.SelectCommand.Parameters.AddWithValue("@SDate", Me.dtpStart.ToString)
    da2.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da2.SelectCommand.Parameters.AddWithValue("@SDate", Me.dtpStart.ToString)
    da3.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da3.SelectCommand.Parameters.AddWithValue("@SDate", Me.dtpStart.ToString)
    da3.SelectCommand.Parameters.AddWithValue("@EDate", Me.dtpEnd.ToString)
    da4.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da4.SelectCommand.Parameters.AddWithValue("@SDate", Me.dtpStart.ToString)
    da4.SelectCommand.Parameters.AddWithValue("@EDate", Me.dtpEnd.ToString)
    da4.SelectCommand.Parameters.AddWithValue("@SAct", "Yes")

    Dim dt As New DataTable
    Dim dt1 As New DataTable
    Dim dt2 As New DataTable
    Dim dt3 As New DataTable
    Dim dt4 As New DataTable

Thanks in advance.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.