CONTOH QUERY STOKSALDO DENNGAN ACCESS

 

pada dasarnya semua bisa dilakukan dengan access, pertama buat table1 sbb;

table1

DetailID	trxID	custID	DEBET	KREDIT
1	1234	1	5	1
2	1234	2	4	5
3	1234	3	3	1
4	1235	1	2	2
5	1235	3	1	3
6	1235	5	0	1
7	1235	2	3	2

 

kemudian buat query sbb;

 

SELECT A.DetailID, A.trxID, A.custID, A.DEBET, A.KREDIT, IIf([DEBET]-[kredit]>=0,[DEBET]-[kredit],[debet]-[KREDIT]) AS MUTASI, Format(DSum("mutasi","query2","detailID <=" & [DetailID]),"Currency") AS SaldoFROM TABLE1 AS A
ORDER BY A.DetailID;

 

 

 

hasilnya sbb;

Query2

DetailID	trxID	custID	DEBET	KREDIT	MUTASI	Saldo
1	1234	1	5	1	4	Rp.4.00
2	1234	2	4	5	-1	Rp.3.00
3	1234	3	3	1	2	Rp.5.00
4	1235	1	2	2	0	Rp.5.00
5	1235	3	1	3	-2	Rp.3.00
6	1235	5	0	1	-1	Rp.2.00
7	1235	2	3	2	1	Rp.3.00

Header row to colomns

+-----------+----------+
| colHeader | value    |
+-----------+----------+
| Header1   | value 1  |
+-----------+----------+
| Header2   | value 2  |
+-----------+----------+
| Header3   | value 3  |
+-----------+----------+

Output:

+-----------+-----------+-----------+
| Header1   | header2   | Header3   |
+-----------+-----------+-----------+
| Value 1   | value 2   | Value 3   |
+-----------+-----------+-----------+

query

 create table myTable (id INT, colHeader varchar(10), value varchar(10));

insert into myTable values
(1,'Header 1','Value 1'),
(1,'Header 2','Value 2'),
(1,'Header 3','Value 3');

    SELECT * FROM myTable;
  
    SELECT
      GROUP_CONCAT(if(colHeader = 'Header 1', value, NULL)) AS 'Header 1',
      GROUP_CONCAT(if(colHeader = 'Header 2', value, NULL)) AS 'Header 2',
      GROUP_CONCAT(if(colHeader = 'Header 3', value, NULL)) AS 'Header 3'
    FROM myTable;

    SELECT
      id,
      GROUP_CONCAT(if(colHeader = 'Header 1', value, NULL)) AS 'Header 1',
      GROUP_CONCAT(if(colHeader = 'Header 2', value, NULL)) AS 'Header 2',
      GROUP_CONCAT(if(colHeader = 'Header 3', value, NULL)) AS 'Header 3'
    FROM myTable
    GROUP BY id;

max if

SELECT  KAMAR,
        MAX(IF(tanggalreservasi = '2014-12-01', NOMORRESERVASI, NULL)) '2014-12-01',
        MAX(IF(tanggalreservasi = '2014-12-02', NOMORRESERVASI, NULL)) '2014-12-02',
        MAX(IF(tanggalreservasi = '2014-12-03', NOMORRESERVASI, NULL)) '2014-12-03',
        MAX(IF(tanggalreservasi = '2014-12-04', NOMORRESERVASI, NULL)) '2014-12-04',
        MAX(IF(tanggalreservasi = '2014-12-05', NOMORRESERVASI, NULL)) '2014-12-05'
FROM     matrikreservasi
GROUP   BY IDmatrikreservasi

CONTOH ROW TO COLOMN

Table A

|  id|order|data|item|Price|
-----+-----+----------------
|   1|    1|   P| 1  | 50  |
|   1|    1|   P| 2  | 60  |
|   1|    1|   P| 3  | 70  |
|   1|    2|   Q| 1  | 50  |
|   1|    2|   Q| 2  | 60  |
|   1|    2|   Q| 3  | 70  |
|   2|    1|   P| 1  | 50  |
|   2|    1|   P| 2  | 60  |
|   2|    1|   P| 4  | 80  |
|   2|    3|   S| 1  | 50  |
|   2|    3|   S| 2  | 60  |
|   2|    3|   S| 4  | 80  |

TAMPIL 

| id|order1|order2|order3|item1|item2|item3|item4| -----+-----+---------------------------------------  
| 1| P | Q | | 50 | 60 | 70 | | | 2| P | | S | 50 | 60 | | 80 |

BAHASA QUERY 

SELECT  ID,
        MAX(IF(`order` = 1, data, NULL)) data1,
        MAX(IF(`order` = 2, data, NULL)) data2
FROM    TableA
GROUP   BY ID

KASUS REPLACE

KASUS

tabel 1 (user)

uid | nama_lengkap | username | password --------------------------------------------- 1 | Fery W | admin | admin 2 | Minato | minato | minato

tabel 2 (user_desc)

udid | uid | key | value ------------------------------------------- 1 | 1 | alamat | Pekalongan 2 | 1 | telp | 08756382927 3 | 1 | keterangan | - 4 | 2 | alamat | Konoha Gakure 5 | 2 | telp | 08575648274 6 | 2 | keterangan | -

TAMPILAN DATA

 uid | nama_lengkap | username | password | alamat         | telp        | keterangan
---------------------------------------------------------------------------------------
 1   | Fery W       | admin    | admin    | Pekalongan     | 08756382927 | -
 2   | Minato       | minato   | minato   | Konoha Gakure  | 08575648274 | -

BAHASA QUERYNYA 

SELECT nama_lengkap, username, `password`, 
 REPLACE(GROUP_CONCAT(CASE WHEN `key` = "alamat" THEN `value` END, ',') , ',', '') AS alamat , 
 REPLACE(GROUP_CONCAT(CASE WHEN `key` = "telp" THEN `value` END), ',', '') AS telp,
 REPLACE(GROUP_CONCAT(CASE WHEN `key` = "keterangan" THEN `value` END, ','), ',', '') AS keterangan
FROM USER JOIN user_desc
ON USER.uid = user_desc.uid 
GROUP BY user_desc.uid