I'm sorry, but I still don't get it. Your sample data appears to have 5 fields, but you have 7 or 8 columns listed across the top. Is 56789AC a part of 12345AB? Is there a separate row for 45678AC? Then you go on to talk about 62345AB that's not in the first data sample. I need to see an example of the raw data followed by the exact result you expect. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access/
_____ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of M Brovage Sent: Tuesday, June 27, 2006 4:42 AM To: [email protected] Subject: [ms_access] Replying to John Viescas - Trim. Mid Another Question Ok, this is the problem: This is how the imported database looks L1 L2 L3 L4 End Part Part Qty Price 1 12345AB 12345AB 2 $0.00 2 12345AB 56789AC 2 $0.00 3 12345AB 98765AE 3 $0.00 4 12345AB 3790845 2 $4.00 Levels 2,3,4 roll up into the end number 12345AB. Thus to get 12345AB I multiply Level 4 = 2*4 Level 3 = 3*2*4 Level 2 = 2*3*2*4 Level 1 = 2*2*3*2*4 - which is the end item cost -> $96.00 ---------------------------------------------------------- Qty*Price 1 62345AB 62345AB 1 $0.00 $0.00 2 62345AB 56789AC 1 $0.00 $0.00 3 62345AB 98765AE 1 $0.00 $0.00 4 62345AB 3790845 2 $4.00 $8.00 If everything was quantity 1, except level 4, I would do the above with a query, then sum the right column by grouping the end item number in another separate query and get $8.00 for end item 62345AB. Most of the parts are like this, but there is a large minority that is like the top structure. The only way I can think of is to create a separate column and put a manual adjustment for quantity so I can do the same as the bottom example. I know Excel is a better choice, but I would overwhelm the rows and the resources of the pc if I put everything in it. I'm talking 800 pages of these structures. 1b. Re: Trim, mid, etc now working - thanks for the help ; Another ques Posted by: "John Viescas" [EMAIL PROTECTED] <mailto:JohnV%40Viescas.com> com jlviescas Date: Mon Jun 26, 2006 12:12 am (PDT) What info in the table tells you bnmhj is a sub-part of abcde? How do you know how many of bnmhj go into a abcde? John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas. <http://www.viescas.com/> com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs. <http://blogs.msdn.com/access/> msdn.com/access/ [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> Check out the new improvements in Yahoo! Groups email. http://us.click.yahoo.com/6pRQfA/fOaOAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
