|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
August 28th, 2004, 04:02 AM
|
|||
|
|||
SQL-Additional fields
a)SQL-Additional fields
Hi - hopefully this is an easy query for any experts out there! I need to query a Microsoft Access database with the following fields Field 1-Invoice_Date Field 2-OrderNo Field 3-CustomerName Field 4-Invoice_Amount 1)Can I extract CustomerName, Invoice_Amount and OrderNo where the Invoice_Date is more than 30 days and 60 days old (overdue from date of invoice) as below: CustomerName |OrderNo | > 30 DYS | > 60 DYS | Total --------------------------------------------------------- ShopMart |A1200 | 2,000 | 100 | 2,100 Handyware |CF002 | 5,200 | 200 | 5,400 --------------------------------------------------------- | Total | 7,200 | 300 | 7,500 ================================================== ======= b)SQL-Query Access by Date I need to query a Microsoft Access database with the following fields Field 1-Date Field 2-ItemCode Field 3-Stock_In_qty Field 4-Stock_Out_qty 1)Can I extract the following info that has an additional field that display the quantity balance as below: Date | ItemCode | Stock_In_qty | Stock_Out_qty | *Bal_qty ------------------------------------------------------------------ 12/10/2003 | A100 | 20 | 0 | 20 25/10/2003 | A100 | 0 | 10 | 10 29/10/2003 | A100 | 15 | 0 | 25 10/11/2003 | A100 | 0 | 10 | 15 12/11/2003 | A100 | 2 | 0 | 17 22/11/2003 | A100 | 0 | 5 | 12 02/12/2003 | A100 | 20 | 0 | 32 30/12/2003 | A100 | 0 | 2 | 30 16/01/2004 | A100 | 20 | 0 | 50 05/02/2004 | B100 | 150 | 0 | 150 17/02/2004 | B100 | 0 | 50 | 100 2)Can I extract the following info that show the monthly *stock balance as below: Period Oct03 | Nov03 | Dec03 | Jan04 | Feb04 | Total ------------------------------------------------------------ ItemCode A100 25 | 12 | 30 | 50 | 0 | 117 B100 0 | 0 | 0 | 0 | 100 | 100 ---------------------------------------------- 25 | 12 | 30 | 50 | 100 | 217 ============================================== *Note:stock balance is the quantity balance at the end of each month can anyone help? Thanks From  ebbie
|
|
| Viewing: ASP Free Forums > Database > SQL Development > SQL-Additional fields |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
