help logic 2

[zsyed]

I've a scenario where I need summary by account group. here are two tables that I need to connect.
1) journallines having fields glaccountid and amount
2) accountgroup having fields groupid, glaccountidm, glaccountidl and glaccountidoh
(m-material, l-labour and oh-overhead)
journallines can have at any point of time (each record) either of glaccountidm, glaccountidl and glaccountidoh and amount. I would like to summarize information by account group for each category (m, l and oh). How do I connect tables and summarize data. If I'm vague in exaplining the scenario then please let me know.
many thanks in advance.

 

[Golom]

Just based on the field names that you listed, it does not appear that the two tables have any fields in common that you can use to relate them. Perhaps some sample data and expected results would make matters clearer.

 

[zsyed]

please find below for illustration:
Journallines
glaccountid amount
101 11
101 12
102 21
102 25
103 45
103 54
104 41
104 14
105 12
105 11
106 21
106 22

account group
groupid glaccountidm glaccountidl glaccountidoh
abc 101 102 103
xyz 104 105 106

expected result
group material labour overhead
abc 21 46 99
xyz 55 23 43

 

[dhookom]

You seem to have a math error for abc Material which should be 23, not 21.

However, first create a union query quniAccountNormalized that normalizes your Account Group table:

SELECT GroupID, glaccountidm AS AcctID, "Material" AS Acct
FROM [Account Group]
UNION ALL
SELECT GroupID, glaccountidl, "Labour"
FROM [Account Group]
UNION ALL SELECT GroupID, glaccountidoh, "Overhead"
FROM [Account Group];

Then create a crosstab query with SQL of:

TRANSFORM Sum(JournalLines.amount) AS SumOfamount
SELECT quniAccountNormalized.GroupID
FROM quniAccountNormalized INNER JOIN JournalLines ON quniAccountNormalized.AcctID = JournalLines.glaccountid
GROUP BY quniAccountNormalized.GroupID
PIVOT quniAccountNormalized.Acct;

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Try this

SELECT A.GroupID As [Group], 

       (Select SUM(Amount) FROM [JournalLines] As J
        WHERE J.glaccountid= A.glaccountidm) As [material),

       (Select SUM(Amount) FROM [JournalLines] As J
        WHERE J.glaccountid= A.glaccountidl) As [labour),

       (Select SUM(Amount) FROM [JournalLines] As J
        WHERE J.glaccountid = A.glaccountidoh) As [overhead)

FROM [account group] As A

 

[zsyed]

Thanks dhokom and golom for the codes. Golom's code is simple and easy to understand.