I need to calculate the Amount in the Main table using the other three tables.

Main:
Account                Desc        Amount
544-411000-000000-100-999    Services    ???
544-413000-000000-101-999    Expenses    ???
544-414000-000000-104-999    Bonus        ???
560-411000-000000-050-999    Services    ???
560-413000-000000-060-999    Expenses    ???
560-414000-000000-070-999    Bonus        ???


Summary:
AccSeg        1-Executive    2-Compliance    3-Comm
411000        34,500        58,900        75,000
413000        27,500        12,850        68,000
414000        27,500        12,850        68,000


Department
Dept            Center        Alloc
1-Executive        100        102
2-Compliance        104        101
3-Communication        158        102


Allocation:
Alloc    Type1    Type2    Type3
101    35.5%    4.3%    5.6%
102    78.2%    6.1%    7.0%
103    5.2%    3.0%    2.5%



544-411000-000000-100-999
544    = Type1
411000 = AccSeg
100    = Center

From the above example, get data for 411000 in Summary (1-Executive,
2-Compliance, 3-Comm). First one is 1-Executive, so get the 34500 then go to
Department table and get the Alloc for 1-Executive having the Center 100 and
then go to Allocation table and get the percentage value for Alloc 100 and
Type1. Multiply this percentage value by 3400 that we got earlier.

I am having trouble writing this query; please help.


Thanks

Unless you provide proper DDL and sample data we can only join you in having
lots of trouble writing the query.

Read this:
http://www.aspfaq.com/etiquette.asp?id=5006


ML

---
http://milambda.blogspot.com/

Can I email you an MS Access 2003 database file (zipped)?

Show quoteHide quote

MS Access? Maybe you should first try the Access newsgroup.


ML

---
http://milambda.blogspot.com/

how to: INSERT IGNORE INTO?
dynamic sql in UDF
Timeout sql+asp
URGENT !!! SELECT * returning different set of records
TSQL bit manipulation up to 128 bits???
Pls help! Recursive retrival function
Inner join of one table with two aliases
SQL Server 2000 conversion of varchar to smallint
Dictionary Sorting Weirdness
SQL Parser