need help for a VBA solution

[alienscript]

need a query on looking up for strings ended with "W"

Hi the Access experts!

Hope to have a query to solve this old pain in my neck..

I am looking up from Table1 to Table2 to match the OrderQty for the PartNumbers, using Join Properties. I insert another Field and name it as [W-PartNumberOrderQty]. Can I have the query such that Table1 PartNumber will look for OrderQty in Table2 for only those PartNumbers that end with a "W" and output the values in this inserted Field name as [W-PartNumberOrderQty] ?

Thanks a lot!

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

 

[Pat Hartman]

Create a query that selects only partnumbers that end in "w". Then join this query to the other table.

query1:

Select ...
From YourTable
Where Right(PartNum, 1) = "w";

 

[IanH]

You can also do this as a single query, using a more intricate join criteria (either in the 'FROM' or as a 'WHERE' clause ...


SELECT Table1.PartNumber, Sum(IIF(RIGHT(Table2.PartNumber,1) <> "W", Table2.OrderQuantity, 0)) AS OrderQty, Sum(IIF(RIGHT(Table2.PartNumber,1) = "W", Table2.OrderQuantity, 0)) AS [W-PartNumberOrderQty]
FROM Table1, Table2
WHERE Table1.PartNumber = IIF(RIGHT(Table2.PartNumber,1) = "W", LEFT(Table2.PartNumber,LEN(Table2.PartNumber)-1), Table2.PartNumber)
GROUP BY Table1.PartNumber;


This gives you a sum of order quantities where there is no "W" (from table 2) on the right end of you part number as OrderQty, and a sum of quantity where there is a "W" for [W-PartNumberOrderQty]
The WHERE clause allows you to join the two tables whether there is a "W" or not.

Regards

Ian

 

[alienscript]

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

 

[namliam]

You were looking for something like this??

Parts	Normal	Wqty
0220-34567	5	4
1120-44234	12	0
2442-12345	10	0
5587211	3	8

Well try this query:

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

 

[Pat Hartman]

What was wrong with the other solutions?
http://www.access-programmers.co.uk/forums/showthread.php?t=69528

You will get a lot better help from this forum if you don't post the same question multiple times.

 

[alienscript]

Thank you so much to all of you here who offered me the help...
Thansksssss !