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] 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.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~-->
Yahoo! Groups gets a make over. See the new email design.
http://us.click.yahoo.com/XISQkA/lOaOAA/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/
