Lm th? no ? t?o m?t truy v?n crosstab v?i nhi?u gi tr? tr?ng

D?ch tiu ? D?ch tiu ?
ID c?a bi: 304458 - Xem s?n ph?m m bi ny p d?ng vo.
V?a ph?i: Yu c?u c b?n v? m, m? ha, v kh? nng tng tc k? nng.

Bi vi?t ny ch? p d?ng cho m?t c s? d? li?u Microsoft Access (.accdb v .mdb). ?i v?i m?t phin b?n Microsoft Access 2000 c?a bi vi?t ny, xem 209143.
?i v?i m?t Microsoft truy c?p 97 Phin b?n c?a bi vi?t ny, xem 109939.
Bung t?t c? | Thu g?n t?t c?

TM T?T

Trong m?t truy v?n crosstab Microsoft Access, b?n c th? ch? ?nh ch? m?t trong nh?ng l?nh v?c ho?c tnh ton nh l gi tr?. B?n c th? mu?n hi?n th? nhi?u hn m?t gi tr? trong truy v?n.

V d? sau cho th?y hai c?t thng tin m?i cng ty, B t?c c?a s? l?ng n ?t hng, v t?ng s? th? t? cho m?i nm:
   Company Name  1998 Orders  1998 Total  1999 Orders  1999 Total
   --------------------------------------------------------------
   ABC Company   12           $855.00     15           $1010.25
   XYZ Company   1017         $22045.57   1050         $25345.29
				
Lo?i truy v?n ny i khi ?c g?i l m?t l?nh v?c gi tr? nhi?u truy v?n.

THNG TIN THM

? t?o m?t truy v?n crosstab nhi?u l?nh v?c gi tr?, b?n ph?i t?o m?t truy v?n ring bi?t crosstab cho m?i gi tr? m b?n mu?n ? tnh ton. B?n sau c th? s? d?ng m?t truy v?n ch?n tham gia cc truy v?n ny crosstab ? hi?n th? cc k?t qu? m b?n mu?n.

V d? sau s? d?ng cc m?u c s? d? li?u Northwind.mdb ? t?o ra m?t truy v?n hi?n th? cc k?t qu? tng t? nh cc V d? trong ph?n "Tm l?c" tr?c trong bi vi?t ny. N cho th?y s? bn hng v t?ng s? cho m?i nm cho m?i cng ty.
  1. M? c s? d? li?u m?u Northwind.mdb, v sau t?o ra cc Theo truy v?n crosstab d?a trn n ?t hng, ?t hng chi ti?t, v cc Khch hng b?ng:
       Query: Order Total
       ------------------
       Type: Crosstab Query
       Join: Customers.[CustomerID] <-> Orders.[CustomerID]
       Join: Orders.[OrderID] <-> Order Details.[OrderID]
    
       Field: CompanyName
          Table Name: Customers
          Total: Group By
          Crosstab: Row Heading
    
       Field: Expr1: Year([OrderDate]) & " " & "Order Total"
          Table Name:
          Total: Group By
          Crosstab: Column Heading
    
      Field: Expr2: Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])))
          Table Name:
          Total: Expression
          Crosstab: Value
    					
  2. Lu truy v?n ny nh ?t hng T?ng c?ng.
  3. T?o ra truy v?n crosstab sau y ?c d?a trn cc n ?t hng v khch hng b?ng:
       Query: Order Count
       ------------------
       Type: Crosstab Query
       Join: Customers.[CustomerID] <-> Orders.[CustomerID]
    
       Field: CompanyName
          Table Name: Customers
          Total:      Group By
          Crosstab:   Row Heading
    
       Field: Expr1: Year([OrderDate]) & " " & "Order Count"
          Table Name:
          Total: Group By
          Crosstab: Column Heading
    
    
       Field: OrderID
          Table Name: Orders
          Total:      Count
          Crosstab:   Value
    					
  4. Lu truy v?n ny nh ?t hng Count.
  5. T?o m?t truy v?n m d?a trn t?ng s? th? t? v cc Th? t? Count crosstab truy v?n. S? d?ng CompanyName, t?ng s? th? t? v ?t hng ?m cc l?nh v?c trong nhi?u nm qua m k?t qu? b?n mu?n xem. Sau y V d? s? d?ng trong hai nm qua ?t hng t?ng v tr?t t? Count trong Microsoft Truy c?p.
       Query: Multiple Values
       ----------------------
       Join: Order Total.[CompanyName] <-> Order Count.[CompanyName]
    
       Field: CompanyName
          Table Name: Order Count
    
       Field: 1997 Order Count
          Table Name: Order Count
    
       Field: 1997 Order Total
          Table Name: Order Total
    
       Field: 1998 Order Count
          Table Name: Order Count
    
       Field: 1998 Order Total
          Table Name: Order Total
    					
  6. Khi b?n ch?y truy v?n nhi?u gi tr?, b?n s? th?y m?t b?ng trng tng t? nh b?ng d?i y:
                             1997 Order  1997 Order  1998 Order  1998 Order
       Company Name            Count       Total       Count       Total
       --------------------------------------------------------------------
       Alfred's Futterkiste        3      $2,022.50        3      $2,022.50
       Ana Trujillo                2        $799.75        1        $514.10
       Antonio Moreno              5      $5,960.78        1        $660.00
    					
    LU ?: B?n ph?i thm tn b?ng bi?u th?c n?u l?nh v?c m ?c ch? ?nh cho n?i l m?t l?nh v?c trong nhi?u hn m?t trong nh?ng b?ng bi?u gia nh?p vo cc truy v?n. V d?, trong b?c 1 b?n s? thay ?i:
       Year([OrderDate]) & " " & "Order Total"
    					
    ?:
       Year([Orders].[OrderDate]) & " " & "Order Total"
    					
    N?u b?n thm tn b?ng ? cc B?ng hng, b?n t?o ra m?t l?i c php. N?u b?n ? l?i b?ng tn hon ton, b?n t?o ra m?t l?i tham kh?o m h?.

THAM KH?O

Cho thng tin thm v? crosstab truy v?n, b?m Tr? gip Microsoft Access trn cc Tr giup tr?nh n, lo?i crosstab truy v?n trong vn ph?ng Tr? l? ho?c thu?t s? tr? l?i, v sau nh?p vo Tim kim ? xem cc ch? ? tr? l?i.

Thu?c tnh

ID c?a bi: 304458 - L?n xem xt sau cng: 17 Thang Chin 2011 - Xem xt l?i: 3.0
p d?ng
  • Microsoft Office Access 2007
  • Microsoft Access 2002 Standard Edition
T? kha:
kbquery kbhowto kbmt KB304458 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:304458

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com