MS-Access: Calculate Stock From Multiple Table.

 Suppose we have 3 table in an MS-Access database.

Sample File Download Link.

1. Table 1 tblItems have items details information. Here ItemCode is Primary Key field.

2. Table 2 tblPurchase contain transactions of material purchasing.

3. Table 3 tblSales contain sales transactions.

Output Result:

Now we want to calculate stock position from these 3 tables. We will take items details from tblItems, purchase quantity from tblPurchase, sales quantity from tblSales for each item then minus sales quantity from purchase quantity to show stock quantity.

Below is query to calculate stock from these 3 table.

SELECT TblItems.ItemCode, TblItems.Description, TblItems.ItemClass, TblItems.ItemCategory, Nz(TblPurchaseAgg.pQuantity,0) AS PurchaseQuantity, Nz(TblSalesAggs.sQuantity,0) AS SalesQuantity, (Nz(TblPurchaseAgg.pQuantity,0)-Nz(TblSalesAggs.sQuantity,0)) AS Stock, TblItems.Unit FROM (TblItems INNER JOIN (SELECT TblPurchase.ItemCode AS ItemCode, Sum(TblPurchase.Quantity) AS pQuantity FROM TblPurchase GROUP BY TblPurchase.ItemCode)  AS TblPurchaseAgg ON TblItems.ItemCode = TblPurchaseAgg.ItemCode) LEFT JOIN (SELECT tblSales.ItemCode AS ItemCode, Sum(tblSales.Quantity) AS sQuantity FROM tblSales GROUP BY tblSales.ItemCode)  AS TblSalesAggs ON TblItems.ItemCode = TblSalesAggs.ItemCode;

Above query will show items which have purchase transactions as we use INNER JOIN to tblPurchase. If if we want to see all the items from tblItems which has purchase transactions or not then we have to use LEFT JOIN to tblPurchase. Basically left join will select all the items from tblItems and the items are equal to tblPurchase. So, query will be like below-

SELECT TblItems.ItemCode, TblItems.Description, TblItems.ItemClass, TblItems.ItemCategory, Nz(TblPurchaseAgg.pQuantity,0) AS PurchaseQuantity, Nz(TblSalesAggs.sQuantity,0) AS SalesQuantity, (Nz(TblPurchaseAgg.pQuantity,0)-Nz(TblSalesAggs.sQuantity,0)) AS Stock, TblItems.Unit FROM (TblItems LEFT JOIN (SELECT TblPurchase.ItemCode AS ItemCode, Sum(TblPurchase.Quantity) AS pQuantity FROM TblPurchase GROUP BY TblPurchase.ItemCode)  AS TblPurchaseAgg ON TblItems.ItemCode = TblPurchaseAgg.ItemCode) LEFT JOIN (SELECT tblSales.ItemCode AS ItemCode, Sum(tblSales.Quantity) AS sQuantity FROM tblSales GROUP BY tblSales.ItemCode)  AS TblSalesAggs ON TblItems.ItemCode = TblSalesAggs.ItemCode;

And if anyone want to see only those items which stocks are available. In an another word which stock is zero, we don't want see those in query result then use following query.

SELECT TblItems.ItemCode, TblItems.Description, TblItems.ItemClass, TblItems.ItemCategory, Nz(TblPurchaseAgg.pQuantity,0) AS PurchaseQuantity, Nz(TblSalesAggs.sQuantity,0) AS SalesQuantity, (Nz(TblPurchaseAgg.pQuantity,0)-Nz(TblSalesAggs.sQuantity,0)) AS Stock, TblItems.Unit FROM (TblItems LEFT JOIN (SELECT TblPurchase.ItemCode AS ItemCode, Sum(TblPurchase.Quantity) AS pQuantity FROM TblPurchase GROUP BY TblPurchase.ItemCode)  AS TblPurchaseAgg ON TblItems.ItemCode = TblPurchaseAgg.ItemCode) LEFT JOIN (SELECT tblSales.ItemCode AS ItemCode, Sum(tblSales.Quantity) AS sQuantity FROM tblSales GROUP BY tblSales.ItemCode)  AS TblSalesAggs ON TblItems.ItemCode = TblSalesAggs.ItemCode WHERE ((((Nz(TblPurchaseAgg.pQuantity,0)-Nz(TblSalesAggs.sQuantity,0)))>0));

Queries Download As Text File.

If you have any query then please let us know by comments. Also you can send mail to harun24hr@gmail.com

My question to stackoverflow.

Read More

How to make masked password character inputbox to MS-Excel/MS-Access.

Sometime we need to enter password via inputbox for many reason. Microsoft excel or access do not have any inputbox to take input password. It take input as clear text and any user can see the password while typing. We can make it in two way. We can create a data input form having Text Box and specify property to mask input character as start (*) or other symbol. Another way is to write custom VBA function to do this. I will show how to write custom VBA function to mask inputbox character. Lets start.

1. First open a new workbook.

2. Open VBA code window (Alt+F11).

3. Insert a new module (Follow the below screenshot).

4. Copy and paste below codes to this module. You can also download codes from this link.

'======================= Code Start ======================

Option Explicit

Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, _

    ByVal ncode As LongPtr, ByVal wParam As LongPtr, lParam As Any) As LongPtr

Private Declare PtrSafe Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As LongPtr

Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" _

    (ByVal idHook As LongPtr, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As LongPtr) As LongPtr

Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As LongPtr

Private Declare PtrSafe Function SendDlgItemMessage Lib "user32" Alias "SendDlgItemMessageA" _

(ByVal hDlg As LongPtr, ByVal nIDDlgItem As LongPtr, ByVal wMsg As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr

Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, _

ByVal lpClassName As String, ByVal nMaxCount As LongPtr) As LongPtr

Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As LongPtr

Private Const EM_SETPASSWORDCHAR = &HCC

Private Const WH_CBT = 5

Private Const HCBT_ACTIVATE = 5

Private Const HC_ACTION = 0

Private hHook As LongPtr

Public Function NewProc(ByVal lngCode As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr

    Dim RetVal

    Dim strClassName As String, lngBuffer As LongPtr

    If lngCode < HC_ACTION Then

        NewProc = CallNextHookEx(hHook, lngCode, wParam, lParam)

        Exit Function

    End If

    strClassName = String$(256, " ")

    lngBuffer = 255

    If lngCode = HCBT_ACTIVATE Then

        RetVal = GetClassName(wParam, strClassName, lngBuffer)

        If Left$(strClassName, RetVal) = "#32770" Then

            SendDlgItemMessage wParam, &H1324, EM_SETPASSWORDCHAR, Asc("*"), &H0

        End If

    End If

    CallNextHookEx hHook, lngCode, wParam, lParam

End Function

Public Function PasswordBox(Prompt, Title) As String

    Dim lngModHwnd As LongPtr, lngThreadID As LongPtr

    lngThreadID = GetCurrentThreadId

    lngModHwnd = GetModuleHandle(vbNullString)

    hHook = SetWindowsHookEx(WH_CBT, AddressOf NewProc, lngModHwnd, lngThreadID)

    PasswordBox = InputBox(Prompt, Title)

    UnhookWindowsHookEx hHook

End Function

'================= Code End ===================

5. Then make a public sub to call the PasswordBox function. You can call the function like below.

Sub TestPasswordBox()

    Range("A1") = PasswordBox("Enter password to test.", "Password Box")

End Sub

Sample File Download Link.

E-Mail: harun24hr@gmail.com

Read More

Excel-Word: অল্প সময়ে কয়েকশত students এর marksheet তৈরির video tutorial.

আমি আজকে দেখাবো কিভাবে Microsoft excel এবং word ব্যবহার করে অল্প সময়ে কয়েকশত students এর marksheet তৈরি করা যায়। উপরের download link এ ভিডিও সহ marksheet  এর sample এবং student দের তথ্য এবং  subject wise mark entry সহ excel file দেওয়া আছে।

Download: Tutorial materials download link.

কাজটি করার জন্য আপনাকে প্রথমে আপনার প্রতিষ্ঠানের marksheet এর sample তৈরি করতে হবে। একটি excel file এ logical manner এ সুন্দর করে ডাটা entry করতে হবে। এরপর ঐ excel file থেকে কিভাবে ডাটা এনে এক সাথে সব student দের marksheet automatically তৈরি করবেন তা ভিডিওতে বিস্তারিত দেখানো হয়েছে এবং আলোচনা করা হয়েছে। আশা করি tutorial টি দেখে আপনারা আপনাদের প্রতিষ্ঠানের কাজে ব্যবহার করতে পারবেন।

যেকোন পরামর্শ বা তথ্যের জন্য comment বা মেইল করুন   harun24hr@gmail.com

Read More

How to get all files name from a folder in Excel (VBA).

In this post I will show, how to get all files name from a folder. Some times we require to type all file names from a folder. Typing for many files a time consume. So to get easily all files name we may use following procedure. Sample File Download Link.

1.       Open a new excel file.

2.       Open Visual Basic code windows (Hit “ALT+F11”).

3.       Insert a new module.

4.       Copy and paste below codes and save the file as macro enable workbook.

‘******************************************************************************

Sub GetAllFiles()

    Dim fso As Object

    Dim objFolder As Object

    Dim objFolders As Object

    Dim objFile As Object

    Dim objFiles As Object

    Dim strFileName As String

    Dim strFilePath As String

    Dim FolderPath, FolderNme As String

   

        With Application.FileDialog(msoFileDialogFolderPicker)

            .Show

            FolderNme = .SelectedItems(1)

        End With

    FolderPath = FolderNme

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set objFolder = fso.GetFolder(FolderPath)

    Set objFiles = objFolder.Files

   

    For Each objFile In objFiles

        strFileName = objFile.Name

          strFilePath = objFile.Path

           ActiveCell.Value = strFileName

          ActiveCell.Offset(0, 1) = strFilePath

        ActiveCell.Offset(1, 0).Activate

    Next

    Set objFolder = Nothing

    Set objFile = Nothing

    Set fso = Nothing

    'Columns(ActiveCell.Column).AutoFit

    'Columns(ActiveCell.Offset(0, 1).Column).AutoFit

End Sub

‘******************************************************************************

5.       Now insert a form controls button from developer tab and assign above macro.

6.       Select cell A1 and run the macro by clicking the button.

7.       Macro will get all files from the folder you will select.

If there is any query about this post, you can comment or send mail to harun24hr@gmail.com

Read More

Excel Advance: Filtering data automatically based on condition using functions.

Sample file download: Filter_Data_Based_On_Condition      Zip File (10 KB)

How to filter data using function in Excel. In above screenshot there are some data in left table. In right table filtered data are shown.

There are some tasks list and different task is in different status. We want to filter tasks those are already complete. Whenever a task is mark as complete it will automatically appear in filtered list. To achieve these filtering use following formula.

=IFERROR(INDEX($B$4:$E$18,SMALL(IF($E$4:$E$18="Complete",ROW($E$4:$E$18)-ROW($B$3),""),ROWS($B$4:$B4)),COLUMNS($B$4:B$4)),"")

*** Note: This is an array formula. So, must press Ctrl+Shift+Enter after typing the formula.

As above screenshot put the formula in G4 cell and fill the formula right and down.

Read More

MS Access: কিভাবে Checkbox ও Option Button ব্যবহার করবেন।

Sample File & Video Download: Checkbox_Optionbutton_Program   Zip File (44 KB)

Video Link: Checkbox_Optionbutton_Program

*** নিচের কোন লাইন না বুঝতে পারলে ভিডিওটা ভাল করে দেখবেন।

(1)    প্রথমে একটি নতুন অ্যাকসেস ফাইলে একটি নতুন Form নেই।

(2)    ফরমে তিনটি টেক্সট বক্স, একটি Option Group এবং তাতে তিনটি Option button ও তিনটি Checkbox নেই। টেক্সট বক্স তিনটির নাম দেই যথাক্রমে txtRed, txtGreen, txtBlue. Checkbox গুলোর নাম দে্ই যথাক্রমে chkRed, chkGreen, chkBlue.

(3)    এখন Option Group এর Click Event এ নিচের কোডসমূহ লিখি।

Private Sub FrameColor_Click()

    If Me.FrameColor.Value = 1 Then

        Me.Detail.BackColor = vbCyan

    ElseIf Me.FrameColor.Value = 2 Then

        Me.Detail.BackColor = vbMagenta

    ElseIf Me.FrameColor.Value = 3 Then

        Me.Detail.BackColor = vbYellow

    End If

End Sub

(4)    Checkbox সমূহে যথাক্রমে নিচের কোডগুলো লিখি।

Private Sub chkBlue_Click()

    If Me.chkBlue.Value = True Then

        Me.txtBlue.BackColor = vbBlue

    Else

        Me.txtBlue.BackColor = vbWhite

    End If

End Sub

Private Sub chkGreen_Click()

    If Me.chkGreen.Value = True Then

        Me.txtGreen.BackColor = vbGreen

    Else

        Me.txtGreen.BackColor = vbWhite

    End If

End Sub

Private Sub chkRed_Click()

    If Me.chkRed.Value = True Then

        Me.txtRed.BackColor = vbRed

    Else

        Me.txtRed.BackColor = vbWhite

    End If

End Sub

ফরমটি রান করিয়ে Option button ও Checkbox সমূহ সেলেক্ট করে program গুলো টেস্ট করি।

কোন জিজ্ঞাসা থাকলে কমেন্টে জানাবেন।

Read More

Excel Advance: How to filter data of a column that have specific word?

Suppose I have list of animal (See screenshot). I want to filter data which have word cat. So, for filtering data with word cat use the following formula.

=IFERROR(INDEX($A$5:$A$14,SMALL(IF(ISNUMBER(SEARCH("Cat",$A$5:$A$14)),ROW($A$5:$A$14)-ROW($A$4)),ROWS($A$5:$A5))),"")

*** Note: This is an array formula. So, you have to must press ‘Ctrl+Shift+Enter’ after typing formula.

Download sample file.

Read More