davaran
دوشنبه 20 آذر 1391, 22:38 عصر
سلام من یه لیست دارم که چند مدل سورت می شود یکیش دستوره زیره میخوام همه شرکت هایی که وضعیت انها با حرف "م" شروع می شود را نشان دهد در دستورات زیر فقط نتایج مساوی با "مرجع" را نشان می دهد
SELECT tblAccount.AccountID, tblAccount.AccountName, tblAccount.AccountStatus, Format(Count(tblOpportunity.OpportunityID),"#,##0") AS [# تعداد کار], Format(Sum(nz(tblOpportunity.OppDollars)),"#,##0") AS [پیش بینی], Format(Count(tblInvoice.InvoiceID),"#,##0") AS [#تعداد سفارش], Format(Sum(nz([Qty]*[Rate])+(nz([tblInvoice].[TaxPercent]/100)*Iif([tblInvoiceLine].[Taxable]="T",[tblInvoiceLine].[Qty]*[tblInvoiceLine].[Rate],0))),"#,##0") AS [جمع فاکتورها]
FROM (((tblAccount LEFT JOIN tblInvoice ON tblAccount.AccountID = tblInvoice.AccountID) LEFT JOIN tblInvoiceLine ON tblInvoice.InvoiceID = tblInvoiceLine.InvoiceID) LEFT JOIN tblLookupState ON tblAccount.StateID = tblLookupState.StateID) LEFT JOIN tblOpportunity ON tblAccount.AccountID = tblOpportunity.AccountID
GROUP BY tblAccount.AccountID, tblAccount.AccountName, tblAccount.AccountStatus
HAVING (((tblAccount.AccountStatus)= '"مرجع"'))
SELECT tblAccount.AccountID, tblAccount.AccountName, tblAccount.AccountStatus, Format(Count(tblOpportunity.OpportunityID),"#,##0") AS [# تعداد کار], Format(Sum(nz(tblOpportunity.OppDollars)),"#,##0") AS [پیش بینی], Format(Count(tblInvoice.InvoiceID),"#,##0") AS [#تعداد سفارش], Format(Sum(nz([Qty]*[Rate])+(nz([tblInvoice].[TaxPercent]/100)*Iif([tblInvoiceLine].[Taxable]="T",[tblInvoiceLine].[Qty]*[tblInvoiceLine].[Rate],0))),"#,##0") AS [جمع فاکتورها]
FROM (((tblAccount LEFT JOIN tblInvoice ON tblAccount.AccountID = tblInvoice.AccountID) LEFT JOIN tblInvoiceLine ON tblInvoice.InvoiceID = tblInvoiceLine.InvoiceID) LEFT JOIN tblLookupState ON tblAccount.StateID = tblLookupState.StateID) LEFT JOIN tblOpportunity ON tblAccount.AccountID = tblOpportunity.AccountID
GROUP BY tblAccount.AccountID, tblAccount.AccountName, tblAccount.AccountStatus
HAVING (((tblAccount.AccountStatus)= '"مرجع"'))