Need help Writing a Query

[NJ]

I need to calculate the Amount in the Main table using the other three
tables; if you prefer then I can send a small mdb file.

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 havint trouble writing this query; please help.

Thanks

 

[KARL DEWEY]

I see several things that need to be changed. Two of your tables are
organized like spreadsheets and not loke relational database. Here is how
they need to be structured.
Summary:
AccSeg DEPT Amount
411000 1-Executive 34500
413000 1-Executive 27500
414000 1-Executive 27500
411000 2-Compliance 58900
413000 2-Compliance 12850
414000 2-Compliance 12850
411000 3-Comm 75000
413000 3-Comm 68000
414000 3-Comm 68000

Allocation:
Alloc Type Percent
101 Type1 35.50%
102 Type1 78.20%
103 Type1 5.20%
101 Type2 4.30%
102 Type2 6.10%
103 Type2 3.00%
101 Type3 5.60%
102 Type3 7.00%
103 Type3 2.50%

Here is a query to be able to join the tables --
NJ_AccSeg--
SELECT NJ_Main.*, Right(Left([Account],10),6) AS AccSeg
FROM NJ_Main;

SELECT NJ_AccSeg.*, NJ_Summary.DEPT, NJ_Department.Alloc,
NJ_Allocation.Type, NJ_Summary.Amount, NJ_Allocation.Percent,
[Percent]*[NJ_Summary].[Amount] AS Calculation
FROM ((NJ_AccSeg INNER JOIN NJ_Summary ON NJ_AccSeg.AccSeg =
NJ_Summary.AccSeg) INNER JOIN NJ_Department ON NJ_Summary.DEPT =
NJ_Department.Department) INNER JOIN NJ_Allocation ON NJ_Department.Alloc =
NJ_Allocation.Alloc
WHERE (((NJ_Department.Alloc)="101") AND ((NJ_Allocation.Type)="Type1"));

Another problem is that there are two projects that have 411000 and there is
no data combination as you laid out in your example.