Calculate sum based on criteria in different field

[wes]

First let me apologize if I'm posting in the wrong place, I have been trying to do this by queries but maybe need something else ... I appreciate any guidance. :-)

I need to be able to track and calculate two different types of funding, and the years they are allocated. Some projects will have no funding so all fields would be null; some may have just one of the types; and some will have both types.

Here is an example of my table, I hope it translates ok. Sample DB is attached as well.

Project / DesignYear / Design$ / PrintYear / Print$
A / 11 / 1,000 / 12 / 2,000
B / [null] / [null] / 11 / 100


C / 12 / 500 / [null] / [null]
D / 11 / 200 / 11 / 300

I need to be able to see data in different ways.

1) The total funds needed by year. I'd like to see at a glance that Year11 totals are 1,600, Year12 totals are 2,500, Year13 totals are (etc). I don't need to see the project names on this one.

2) Funding needed for a particular year, and the projects which require those funds. I need to see that in Year11 Project A needs 1,000, Project B needs 750, and Project D needs 500; in Year12 Project A needs 2,000 and Project C needs 500. (Maybe this should be a report? But I figured it might need to be populated from a query.)

2) The total funds needed per project, regarless of year. So Project A needs 3,000 total; Project B needs 100 total; Project C needs 500 total; project and Project D needs 500 total.

I can do simple queries but this is beyond me. I can't figure out how to get it to calculate properly across columns.

Thanks for your patience!

[William McKinley]

Well, my solution for #1 doesn't like the null fields that you have included, but it seems to work.

SELECT DSum("Design$","Table1","DesignYear=" & [DesignYear])+DSum("Print$","Table1","PrintYear=" & [DesignYear]) AS RequiredFunding, Table1.DesignYear
FROM Table1
GROUP BY Table1.DesignYear;

[ssanfu]

My solution is to normalize your table. Then the queries are very simple.

See attached mdb (I have A2K3)

[wes]

Thanks for the replies, glad I checked in tonight! @William, you have helped me understand the application for DSum and Select As, which I've been trying to get straight. I need to play with that in my real database, but am glad to have a real example, thanks!

@ssanfu, Here I was thinking I _did_ have a normalized structure because I had all of my main info in one ProjectOverview table, but now I know better! Thank you so much for the revised sample DB, I understand so much better now. And of course, it works! Thanks!

I clearly need to rethink my approach. I'm having a similar problem tracking which people are assigned to which projects, because a person can be in any of 3 positions--TeamLead, TeamMember1, or TeamMember2. I made a separate table with Personnel, using the person's name as the primary key, and set this up as a lookup table in my main ProjectOverview table. How can I easily see the names of each project a person is assigned to? It's kind of the same approach, but technically a different question, so I can move it to another thread if need be.

Thanks again!

[ssanfu]

<snip> but technically a different question, so I can move it to another thread if need be.

Usually, its one question (or related questions) per thread.

<snip> I made a separate table with Personnel, using the person's name as the primary key,<snip>

What happens if you get two John Smiths?? Or a woman gets married/divorced and changes her name?? I use an Autonumber field as the primary key.

Without knowing all of your tables/relationships or requirements, maybe use a junction table between Personnel & Projects. Can a person be TeamLead and TeamMember1 on the same project at the same time?

But this question should be in a new thread.

[wes]

Thanks Steve, I'll put some thought into how to ask the question logically, and will start a new thread.