Using MS Access/SUMIF in MS ACCESS 2010

Advertisement


Question
QUESTION: Hello Scottgem,

I have table named "tbl_TranProductivity" that has the following field:

strEmpName
dteDate
strTranType
intNum

I have another table named "tbl_SummProductivity" that has the following field:

strEmpName
dteDate
intProUnits|Inv
intInb
intOuB
inStmt

What i'm hoping is to have a query that will populate the "tbl_SummProductivity" with:

intProUnits|Inv - is computed with SUMIF the dteDate(tbl_TranProductivity)= dteDate(tbl_SummProductivity) AND if  strEmpName(tbl_TranProductivity) = strEmpName(tbl_SummProductivity)AND if strTranType(tbl_TranProductivity)<> "Statement"

Is this possible? I am hoping you can help me out..

Thank you
Hudas

ANSWER: First, you have design issues that should be corrected. You should not be using employee names as a key field. You should have an employee table where the employees are assigned a numeric key. And that should be the foreign key in any table where you need to identify an employee. Names may not be unique and text fields perform more poorly than numeric fields in joins. The same thing is true of TranType. You should have a lookup table for TranTypes and store a numeric code.

Second, as a general rule we don't store calculated values. So if the intProUnits|Inv field is a calculation it shouldn't be stored, but rather calculated in a query. You can create a Group By query that groups by employee and date and sums the value where the TranType equals some value.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

Query
Query  
QUESTION: Hi Scottgem,

Thank you for the advise. I was able to create 4 separate queries that sums the Number of transaction type. Now my problem is how do I put them into one query. Just like the "Inventory" query on the northwind database. I can't make it work. Can you please help me understand how the inventory query works?

Thank you very much for your help.
Hudas

Answer
Again, you should be able to use a Group By query. Group by Transtype and Sum the amount.

Not sure what query you are referring to. If you post the SQL of the query I can advise further.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.