Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    23

    Unanswered: need help on VBA

    Hi the Excess experts !

    I am looking up from Table1 to Table2 to match the OrderQty for the PartNumbers in Table2, using Join Properties. I insert another column and rename its Field as [W-PartNumberOrderQty].

    I want PartNumber in Table1 to look up for OrderQty in Table2 for only those PartNumbers that end with an alphabet "W", and populate the matched OrderQty values in this inserted Field name as [W-PartNumberOrderQty].

    I know that I can use Update Query Right$([PartNumber],1) or use Query criteria Like *W to work around, I would be grateful if someone can help me with a VBA solution.

    Thanks so much !!!

    Table1

    PartNumber
    0220-34567
    2442-12345
    1120-44234
    5587211


    Table2

    PartNumber OrderQty
    0220-34567 4
    0220-34567W 5
    2442-12345W 10
    1120-44234W 12
    5587211 8
    5587211W 3


    Output Table

    PartNumber OrderQty W-PartNumberOrderQty
    0220-34567 4 5
    2442-12345 0 10
    1120-44234 0 12
    5587211 8 3

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You were looking for something like this??
    Code:
    Parts	Normal	Wqty
    0220-34567	5	4
    1120-44234	12	0
    2442-12345	10	0
    5587211	3	8
    Well try this query:
    Code:
    SELECT 
    	IIf(Right([PartNumber],1)="W",
    		Left([PartNumber],Len([Partnumber])-1),
    		[Partnumber]) 				AS Parts, 
    	Sum(IIf(Right([PartNumber],1)="W",[orderqty],0)) 	AS Normal, 
    	Sum(IIf(Right([PartNumber],1)="W",0,[orderqty])) 	AS Wqty
    FROM 
    	Table1
    GROUP BY 
    	IIf(Right([PartNumber],1)="W",
    		Left([PartNumber],Len([Partnumber])-1),
    		[Partnumber]);
    Greetz

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select Replace(partNumber, 'W', ''),
    SUM(CASE WHEN partNumber Like '%W' Then orderQty Else 0 END) As "W-PartNumberOrderQty",
    SUM(CASE WHEN NOT partNumber Like '%W' Then orderQty Else 0 END) As "OrderQty"
    from table2
    group by Replace(partNumber, 'W', '')
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •