FastStone Photo Resizer - Convert and Rename images in batch mode

  Mei 18, 2016            No comments
FastStone Photo Resizer is an image converter and renaming tool that intends to enable users to convert, rename, resize, crop, rotate, change color depth, add text and watermarks to images in a quick and easy batch mode. Drag and Drop mouse operation is well supported.

Aplikasi ini bisa di download gratis di: https://www.faststone.org/FSResizerDetail.htm



Ranking Queries in MS Access SQL

  Mei 11, 2016            No comments
Introduction
A query to rank or number the results is often requested. In more powerful database management systems such as Microsoft SQL and DB2, there are often functions to do this. However, in Microsoft Access, no such function exists.

Options
In Access, there are several workarounds:
  1. A running sum in a report
  2. VBA code called in the SELECT clause
  3. A subquery in the SELECT clause
  4. A DCount in the SELECT clause
  5. Joining the table to itself and using a COUNT

Option 1 is probably the easiest if you want to number the rows consecutively but is the least flexible of the options.

Options 2, 3, and 4 require each row to be evaluated separately and can be slow for large data sets.

Option 5 is the most complicated to understand but can often be the most efficient. That is the option I will be discussing in this article.

Examples
Given the following table and data:
Expand|Select|Wrap|Line Numbers
  1. ID Salesperson Division    NumberSold
  2. 1  Robert      Electronics 99
  3. 2  Jenny       Electronics 54
  4. 3  Billy       Appliances  54
  5. 4  Karen       Appliances  102
  6. 5  Kim         Appliances  30
For the first example, let's say you want to rank all the salespeople by number of items sold, you can join the table to itself on the number sold and do a count.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) + 1 AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.NumberSold < t2.NumberSold
  11. GROUP BY
  12.    t1.Salesperson,
  13.    t1.Division,
  14.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         2
  3. Jenny       Electronics 54         3
  4. Billy       Appliances  54         3
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         5
Note that this gives ties the same rank. If what you want is to number the rows rather than rank them, you will need to use a unique field.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.NumberSold < t2.NumberSold OR
  11.          (t1.NumberSold = t2.NumberSold AND
  12.          t1.ID <= t2.ID)
  13. GROUP BY
  14.    t1.Salesperson,
  15.    t1.Division,
  16.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         2
  3. Jenny       Electronics 54         4
  4. Billy       Appliances  54         3
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         5
If you want to break out the rankings or numbering by grouping field(s), you can do that by including them in the JOIN clause.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.Division = t2.Division AND
  11.          t1.NumberSold <= t2.NumberSold
  12. GROUP BY
  13.    t1.Salesperson,
  14.    t1.Division,
  15.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         1
  3. Jenny       Electronics 54         2
  4. Billy       Appliances  54         2
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         3
Note that this ranks from highest to lowest. Going from lowest to highest merely requires flipping the less than operator to a greater than operator. 
sumber : https://bytes.com/topic/access/insights/954764-ranking-queries-ms-access-sql

RANK in MS Access

  Mei 11, 2016            No comments
Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.
However same result can be achieved using a correlated query.
e.g. Consider a dataset of student marks below.
Students Marks
Tony 34
Bob 32
Thor 48
Jack 42
Tom 41
Kate 45
Sid 26
Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

SELECT Students,
       (SELECT COUNT(T1.Marks)
          FROM
                 [Table] AS T1
         WHERE T1.Marks >= T2.Marks) AS Rank
FROM
      [Table] AS T2
ORDER BY Marks DESC

Following is the result of the query.

Students Rank
Thor 1
Kate 2
Jack 3
Tom 4
Tony 5
Bob 6
Sid 7
If,  rank 1 is to be assigned to the lowest value, then in that case the query above can be slightly modified to:

SELECT Students,
       (SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks <= T2.Marks) AS Rank 
FROM 
      [Table] AS T2 
ORDER BY Marks 

 sumber : https://usefulgyaan.wordpress.com/2013/04/23/ranking-in-ms-access/

Build Custom Functions for Your Access Applications

  Mei 11, 2016            No comments

Build Custom Functions for Your Access Applications

Why Build Custom Functions?

If you came to Access after using Excel you may have noticed that Access seems a little short on calculating power. Many users get this first impression of Access. Excel is principally a tool for calculating, whereas the prime function of Access is the storage and retrieval of data.
Excel has a vast library of built-in functions. Access also has a sizeable collection of functions but smaller than that of Excel. One reason for this is that much of the calculation done in Access is by means of queries. Queries are written in a standard language called SQL (Structured Query Language) which is used by all database programs. Access's built-in functions have to be compatible with SQL, which tends to limit its calculating power.
Sooner or later most Access users find that Access doesn't have a built-in function to do the calculation they want. What can you do?
Once again VBA comes to the rescue. If Access doesn't have the function you need, you could try writing one of your own. This tutorial shows you how to build and use a custom function and shows you how to use it in forms and queries. You will often see custom functions referred to as UDFs (User-Defined Functions).
The tutorial is suitable for Access versions 97, 2000 and XP (2002). Microsoft introduced the Visual Basic Editor, a separate program that runs from within Access, with Access 2000. In some places I have provided separate instructions for Access 97.

Creating a New Custom Function

Step 1: Open a New Module

The first step is to open a new code module. A module is simply a container for a collection of VBA code.
Move to the Modules section of the Access Database Window and click the [New] button. Access 97 opens a new code module window (you will notice that the toolbar and menus change to provide the appropriate VBA editing tools). In Access 2000/XP the Visual Basic Editor window opens with a new module named Module1.
The Access 2000/XP VB Editor with a new code module
If you are working with Access 2000/XP you should make sure that you can see the Project Explorer pane and the Properties pane on the left of the Visual Basic Editor window. You can open them from the View menu if necessary.
It is a good idea to give your module a meaningful name. You can keep all your code in one module or organise it in different ones - it's up to you. I have spent so much time searching through modules named Module1, Module2 etc. trying to find a particular piece of code that I usually rename mine. In Access 2000/XP you can rename a module from the properties pane. Click on the module name in the Project Explorer pane, then change "Module1" in the Properties pane to your chosen name (usual object naming rules... no spaces!). This one will contain functions so I've named it "Functions".
Renaming the module in the Access 2000/XP VB Editor
If you are working in Access 97 you can name now by clicking the Save button and typing your chosen name in the dialog box. Alternatively you can wait until you close and save your finished module, when you will be prompted for a name.  

Step 2: Enter a Name for the Function

I'll start with an easy one!  This function calculates a person's age from their date of birth. In the code window type: 
   Public Function Age(DoB As Date)
... and press ENTER. When you do this the program automatically enters the closing line End Function and places your cursor in the space between.
What does it mean?
  • The keyword Public makes the function available outside its host module. This means you can make use of it in other modules, and elsewhere in Access (e.g. in queries, forms and reports).
  • The name Age is the one you will use when addressing the function. Try to make your function names descriptive but keep them short. Function names can not have spaces, so separate words with underscores or capitals, e.g. Age_in_years or AgeInYears.
  • (DoB As Date) declares the function's arguments. An "argument" is a piece of external information that the function needs to do its calculation. In this case there is a single argument DoB, which has to be a date.

Step 3: Enter the Function Code

This function is quite simple and requires just one line of code. Type the following line in the space between the Public Function and End Function statements:
   Age = Int((Date-DoB)/365.25)
What does it mean?
  • Date-DoB uses the VBA function Date which always returns the current date (like Excel's TODAY() function) and subtracts from it the supplied date of birth (DoB). Windows treats dates as numbers based on the "1900 System" where day 1 was January 1 1900. So, subtracting the date of birth from the current date gives the person's age in days.

    Supposing today was January 20 2002 (day 37276) and I was born on September 27 1950 (day 18533) I would be 37276-18533 or 18743 days old today.
  • (Date-DoB)/365.25 divides the result by the average number of days in a year (365.25). The brackets ensure that the sum is done before division. The result is the person's age in years.

    To continue the example, that makes me 18743/365.25 or 51.3155373 years old today.
  • When considering someone's age we are usually interested only in whole years so I have used the Int() function to convert the calculation to a whole number (i.e. an integer) which it does by rounding down.

    So my age today is 51 (I know, but I've had a hard life!).
Your finished code should look like this:
Public Function Age(DoB As Date)
   Age = Int((Date - DoB) / 365.25)
End Function

Step 4: Check Your Code

You should always check your code before you use it for real. There is a quick and easy way to check for errors in syntax, undeclared variables, and typos that the VB editor didn't spot when you were typing your code. This process, called "compiling" the code, effectively carries out a "dummy-run" of the code without actually changing any data.
To compile your code module go to Debug > Compile (Database Name). If there are any problems the compiler will point them out. It's up to you do figure out what's wrong and to put it right (i.e. to "debug" your code). Here the compiler has found a word that it doesn't recognise. It turns out to be a typo, Dste instead of Date...
Using the compiler to test your code
If you find any errors, fix them and compile again. If nothing happens when you compile, it means that no errors were found. If you check the Debug menu you will find the Compile command is greyed-out...
The greyed-out command shows that the code has been compiled

Step 5: Test Your Function

You can often test functions without leaving the VB editor, by making use of the Immediate Window. The purpose of the Immediate Window is to allow you to try out code by entering it directly into the window, or by having your code write its results directly to the Immediate Window using the Debug.Print command.
In Access 2000/XP go to View > Immediate Window (keys: CTRL+G). A separate pane labelled Immediate appears at the bottom of the code editing window.
In Access 97 go to View > Debug Window. A new window opens, the lower section of which is the Immediate pane.
To try out your Age function click in the Immediate Window and type (you choose a date*):
   ?Age("27/09/50")
... and press Enter. Access will calculate the function and show you the result in the line below. As you type the functions arguments are displayed as a pop-up tip...
Required arguments are displayed.   Testing a custom function in the Immediate Window.
*NOTE: Working with dates in VBA can be confusing. When referring to dates in code VBA will expect you to use the mm/dd/yy system. Here, however, the function is behaving as if it were taking the date from a field in a table, so it expects you to use your computer's default system. My example shows the date in dd/mm/yy format because I work in the UK and that is how I normally enter dates.

Using Your Custom Function in a Form

You can use a custom function in the same way as you would a built-in function. Forms and reports work the same way. Here's how to add a text box displaying a person's age on a form:

Step 1: Draw an Unbound Text Box

The text box toolIn design view, draw a new text box on your form using the Text Box tool. This is an "unbound" text box because it is not linked to a field in the form's underlying table. In design view the text box currently displays the word "unbound" but in form view it appears blank...
An unbound text box is added to the form

Step 2: Enter the Function into the Text Box

In form design view click in the unbound text box and enter the function text as follows:
=Age([DoB])
Note that here I have entered the name of the field that contains the person's Date of Birth. I usually use the abbreviation "DoB" when referring to date of birth. Don't be confused by the fact that I also named my function's argument "DoB". You should enter the appropriate fieldname is square brackets. For example, if your Date of Birth field is called "Birthdate" then you would type =Age([Birthdate]).
Enter the function into the text box
You can also enter the function by typing it into the Control Source section of the text box's Properties Window like this...
The function displayed in the Properties Window
TIP: When you have a calculated field on a form, help your users by setting that field's Tab Stop property to No. You'll find the Tab Stop property in the Other category of the text box's properties window. This will exclude the text box from the form's tab order, so that the user will not visit it when tabbing through the form. It also locks the text box so that the user can not type over the calculated result.

Step3: Save the Changes and View the Form

Save your changes and switch the form into form view. Your new text box will display the result of the calculation that the function performs. This data is not stored anywhere. The function calculates it as the form displays each record...
The finished form displaying the calculated age

Using Your Custom Function in a Query

It is very easy to perform a calculation using a query. Create a new query in design view and choose the field that you want to display. To add a new, calculated field type a name for the field, followed by a colon (:) in the top (field) row of an empty column of the query design grid. In the example below I have used the name "Age" (Note: You must not use the name of an existing field.)
After the colon type the function expression. You don't need to type an equals sign (=). As in the form example above, my field containing the Date of Birth data is called "DoB". You can choose whether or not to display this field. The function will work whether the query displays the source data or not. I have chosen to leave it out...
The function is entered in the query design grid
Run the query to display the function's results...
The query result displays the calculated age

Where Else Can You Use Your Custom Functions?

Custom functions can be used in Access reports in the same way as in forms, by creating an unbound text box in the design view of the report and entering the function expression as you would on a form.
You can also make use of your custom functions in any VBA procedures you write. Many VBA functions are interchangeable between Access and Excel, although they might need slight modification to suit the host program.

Membuat Menu Ribbon Sendiri Di Access 2007

  Mei 11, 2016            No comments
Bagi yang sering membuat aplikasi menggunakan visual basic for application dengan microsoft access 2007 pasti ingin membuat tampilan menu programnya lebih terorganisir rapi dan cantik. Membuat Custom Ribbon menu Sendiri merupakan pilihan yang patut dipertimbangkan, Untuk membuatnya inilah step by stepnya

1) Buka Project VBA anda, kemudian buatlah sebuah tabel dengan struktur seperti berikut , kemudian beri nama UsysRibbons
 





2) Buka Text Editor seperti Notepad, kemudian ketiklah xml berikut dalam notepad tersebut


<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="tab-1" label="Menu Applikasi">
<group id="group-1" label="Data" >
<button id="btnBarang" label="Data Barang" imageMso="CreateTableTemplatesGallery" size="large" onAction="=BukaForm('f_barang')"/>
<button id="btnPenjualan" label="Data Penjualan" imageMso="Chart3DColumnChart" size="large" onAction="=BukaForm('f_data_penjualan')"/>
<button id="btnPembelian" label="Data Pembelian" imageMso="DatabaseSqlServer" size="large" onAction="=BukaForm('f_data_pembelian')"/>
</group>
<group id="group-2" label="Transaksi">
<button id="btnJual" label="Transaksi Jual" imageMso="BarcodeInsert" size="large" onAction="=BukaForm('f_jual')"/>
<button id="btnBeli" label="Transaksi Beli" imageMso="SlideNew" size="large" onAction="=BukaForm('f_beli')"/>
</group>
<group id="group-3" label="Laporan">
<button id="btnRepBarang" label="Report Barang" imageMso="FilePrint" size="large" onAction="=BukaReport('r_barang')"/>
<button id="btnRepJual" label="Report Jual" imageMso="FilePrint" size="large" onAction="=BukaReport('r_jual')"/>
<button id="btnRepBeli" label="Report Beli" imageMso="FilePrint" size="large" onAction="=BukaReport('r_beli')"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>


Anda bisa menyesuiakan isi menu dengan yang diinginkan, setelah itu masukkan pada tabel UsysRibbons tadi dengan cara copy dan paste pada colom RibbonXML





3) Buat macro vba untuk menghandle fungsi yang dipakai dalam menu seperti BukaForm dan BukaReport dengan cara masuk ke visual basic editor menggunakan tombol ALT+F11, Setelah visual basic editornya terbuka tambahkan module dengan mengklik menu Insert > Module dan ketikkan fungsi berikut

Option Compare Database
Function BukaForm(strForm as String)
     DoCmd.OpenForm strForm, acNormal
End Function
Function BukaReport(strReport As String)
     DoCmd.OpenReport strReport, acViewReport
End Function

Setelah itu tinggal mengaktifkan menu ribbon melalui office menu > Access Options > Current Database > Pilih Ribbon name Custom1 klik ok tutup dan buka lagi access nya untuk melihat efeknya



Sumber:

    DOKUMEN UNTUK Verifikasi Akademik – Uang Kuliah Tunggal (UKT) – Peserta Khusus Bidik Misi

      Mei 11, 2016            No comments

    DOKUMEN UNTUK
    Verifikasi Akademik – Uang Kuliah Tunggal (UKT) – Peserta Khusus Bidik Misi
    SNMPTN Universitas Lampung, Tahun 2016

    Dokumen Untuk Verifikasi Akademik Dimasukan dalam Berwarna Biru, Sebagai Berikut :

    1. Tanda Pendaftaran SNMPTN Asli;
    2. STK, SKHU, dan Raport ASLI (dibawa dan diambil kembali);
    3. Kartu Tanda Pengenal (Identitas) yang Sah dan Masih Berlaku (KTP/SIM/KARTU SISWA);
    4. Salinan (Fotocopi) STK, SKHU, dan Raport (Semester 1 s.d 5) yang Telah Dilegalisir Oleh Kepala Sekolah Sebanyak 2 Lembar.

    Dokumen Data Siswa untuk Penentuan Uang Kuliah Tunggal (UKT) Dimasukan dalam Map Merah :

    1. Print Out Data Pokok Siswa (hasil cetakan dari http://simanila.unila.ac.id/);
    2. Print Out Surat Pernyataan orangtua dan Siswa yang telah di tandatangani dan dibubuhi materi 6000,- (hasil cetakan dari http://simanila.unila.ac.id/);
    3. Data pendukung :
      • Fotocopy Kartu Tanda Penduduk Kedua Orang tua/Wali;
      • Foto bersama keluarga yang masih dalam tanggungan orangtua/wali;
      • Foto rumah tampak depan;
      • Foto rumah ruang tamu;
      • Foto rumah ruang keluarga;
      • Foto kamar tidur;
      • Foto dapur rumah;
      • Foto kamar mandi;
      • Foto Semua STNK Kendaraan bermotor;
      • Fotocopy Kartu Keluarga terbaru;
      • Asli & Fotocopy rekening listrik 3 bulan terakhir;
      • Asli & Fotocopy rekening telepon 3 bulan terakhir;
      • Asli & Fotocopy air 3 bulan terakhir;
      • Asli / fotocopy legalisir slip gaji bagi pegawai atau surat keterangan penghasilan orang tua / wali dari kepala desa/lurah;
      • Fotocopy pembayaran PBB tahun 2014;
      • Asli / Fotocopy legalisir aplikasi kredit (bila punya hutang);

    Dokumen Verifikasi Peserta Bidik Misi Dimasukan dalam Map Hijau :

    1. Fotocopy Kartu Tanda Penduduk Kedua Orang tua/Wali;
    2. Foto bersama keluarga yang masih dalam tanggungan orangtua/wali;
    3. Foto rumah tampak depan;
    4. Foto rumah ruang tamu;
    5. Foto rumah ruang keluarga;
    6. Foto kamar tidur;
    7. Foto dapur rumah;
    8. Foto kamar mandi;
    9. Foto Semua STNK Kendaraan bermotor;
    10. Fotocopy Kartu Keluarga terbaru;
    11. Asli & Fotocopy rekening listrik 3 bulan terakhir;
    12. Asli & Fotocopy rekening telepon 3 bulan terakhir;
    13. Asli & Fotocopy air 3 bulan terakhir;
    14. Asli / fotocopy legalisir slip gaji bagi pegawai atau surat keterangan penghasilan orang tua / wali dari kepala desa/lurah;
    15. Fotocopy pembayaran PBB tahun 2014;
    16. Asli / Fotocopy legalisir aplikasi kredit (bila punya hutang);
    17. Kartu jaminan sosial/ Jamkesmas/ Gakin/ Sejenisnya yang dikeluarkan pemerintah atau Keterangan beasiswa miskin/bina lingkungan dari sekolah (jika ada);
    18. Surat Keterangan tidak mampu dari kepala desa/ lurah;
    19. Deskripsi Tentang Detail Pekerjaan orang tua (ayah dan ibu).
    Keterangan :
    * Penyerahan Dokumen Verifikasi Tanggal 9 Juni 2015 Pukul 08.30 s.d. 12.30 WIB di Gedung Serba Guna (GSG) Universitas Lampung;
    * Peserta SNMPTN Non Bidik Misi Menyerahkan 2 (Dua) Map, yaitu Warna Biru dan Merah. Sedangkan Khusus Bidik Misi Meneyerahkan 3 (Tiga) Map, yaitu Warna Biru, Merah, dan Hijau.
    Info Lengkap, kunjungi http://simanila.unila.ac.id/pengumuman-hasil-snmptn-2016/
    Langganan: Postingan (Atom)