Calculating the Sum IF fields equal criteria

I have expense fields classified as "parts" or "labor".  I need to
create a formula to look at ExpenseType fields 1-10, determine if they
are labor, and then calculate the Total fields sum of the labor field
expenses into a field called TotalLaborCost.  Then I need to do the
same process to calculate the sum of expenses classified as Parts in
the TotalPartsCost field.

Fields in the form:
"ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
"Quantity1", "Quantity2"..... (how many of each item or how many
hours)
"Cost1" ....(what is the per unit cost)
"Total1"....(the extended price)
"TotalLaborCost" (for the sum of all the labor "Totalx" fields
"TotalPartsCost" (for the sum of all the parts "Totalx" fields

0
serviceenvoy
4/21/2007 4:13:55 PM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
1441 Views

Similar Articles

[PageSpeed] 34

You will need to redesign this table to get the results you want.

Whenever you have repeating fields, such as:
    ExpsnseType1, ExpenseType2, Cost1, Cost2, ...
it always means you need a related table where you can have many records 
instead of using many fields in this one table.

One expense can have several line items (parts or labor) in it. There is 
therefore a one-to-many relation between the expense and the line items in 
it. So, you need to build this as 2 tables, like this:

Expense table:
    ExpenseID        AutoNumber        primary key
    ExpenseDate    Date/Time            when this expense was incurred.
    ...

ExpenseDetail table:
    ExpenseDetailID  AutoNumber    primary key
    ExpenseID          Number           Which expense record is line item is 
for.
    ExpenseTypeID                          Labor or Parts.
    Quantity              Number           how many units
    UnitCost              Currency         how much each.

If that doesn't make sense, open the Northwind sample database that installs 
with Access. Open the Relationships window (Tools menu), and study the 
Orders and OrderDetails table. You will see that the approach is similar to 
what you need.

Ultimately you will end up with a form bound to the Expense table, with a 
subform bound to the ExpenseDetail table (similar to the Orders form in 
Northwind.) You will create a query to use as the source for the subform.

In the query, type this expression into the Field row:
    LaborCosts: CCur(IIf([ExpenseTypeID] = 'Labor', Nz([Quantity] * 
[UnitCost],0), 0))
In the next column, type this expression into the Field row:
    PartsCost: CCur(IIf([ExpenseTypeID = 'Labor', 0, Nz([Quantity] * 
[UnitCost],0)))

Now in your form, you will be able to sum these fields to display the 
totals. (Note: It is really important that you do not store these totals in 
either table.)

You now have a normalized data structure that avoids 2 of the big mistakes 
newbies make, i.e.:
- repeating fields, and
- storing dependent data.
If you want to read more about this, the key word is normalization. Here's a 
starting point:
    http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<serviceenvoy@gmail.com> wrote in message
news:1177172035.772696.97190@q75g2000hsh.googlegroups.com...
>I have expense fields classified as "parts" or "labor".  I need to
> create a formula to look at ExpenseType fields 1-10, determine if they
> are labor, and then calculate the Total fields sum of the labor field
> expenses into a field called TotalLaborCost.  Then I need to do the
> same process to calculate the sum of expenses classified as Parts in
> the TotalPartsCost field.
>
> Fields in the form:
> "ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
> "Quantity1", "Quantity2"..... (how many of each item or how many
> hours)
> "Cost1" ....(what is the per unit cost)
> "Total1"....(the extended price)
> "TotalLaborCost" (for the sum of all the labor "Totalx" fields
> "TotalPartsCost" (for the sum of all the parts "Totalx" fields 

0
Allen
4/21/2007 4:49:12 PM
serviceenvoy@gmail.com wrote:

>I have expense fields classified as "parts" or "labor".  I need to
>create a formula to look at ExpenseType fields 1-10, determine if they
>are labor, and then calculate the Total fields sum of the labor field
>expenses into a field called TotalLaborCost.  Then I need to do the
>same process to calculate the sum of expenses classified as Parts in
>the TotalPartsCost field.
>
>Fields in the form:
>"ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
>"Quantity1", "Quantity2"..... (how many of each item or how many
>hours)
>"Cost1" ....(what is the per unit cost)
>"Total1"....(the extended price)
>"TotalLaborCost" (for the sum of all the labor "Totalx" fields
>"TotalPartsCost" (for the sum of all the parts "Totalx" fields


Sorry, but spreadsheet calculations like that are extremely
complex in a database.  You really need to understand how to
design tables using the rules of Normalization (Google it).
One fundamental rule is that a table should not have
repeating fields like Quantity1, Quantity2, ...

Barring you ability to do that, you might have some hope of
getting a reasonable result by creating a subform based on a
query that calculates the desired totals, but it won't be
easy.

-- 
Marsh
MVP [MS Access]
0
Marshall
4/21/2007 5:28:11 PM
On Apr 21, 12:28 pm, Marshall Barton <marshbar...@wowway.com> wrote:
> serviceen...@gmail.com wrote:
> >I have expense fields classified as "parts" or "labor".  I need to
> >create a formula to look at ExpenseType fields 1-10, determine if they
> >are labor, and then calculate the Total fields sum of the labor field
> >expenses into a field called TotalLaborCost.  Then I need to do the
> >same process to calculate the sum of expenses classified as Parts in
> >the TotalPartsCost field.
>
> >Fields in the form:
> >"ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
> >"Quantity1", "Quantity2"..... (how many of each item or how many
> >hours)
> >"Cost1" ....(what is the per unit cost)
> >"Total1"....(the extended price)
> >"TotalLaborCost" (for the sum of all the labor "Totalx" fields
> >"TotalPartsCost" (for the sum of all the parts "Totalx" fields
>
> Sorry, but spreadsheet calculations like that are extremely
> complex in a database.  You really need to understand how to
> design tables using the rules of Normalization (Google it).
> One fundamental rule is that a table should not have
> repeating fields like Quantity1, Quantity2, ...
>
> Barring you ability to do that, you might have some hope of
> getting a reasonable result by creating a subform based on a
> query that calculates the desired totals, but it won't be
> easy.
>
> --
> Marsh
> MVP [MS Access]

I am a novice so I guess I don't understand some of the dangers of
doing things the way I'm attempting.  I need to track expenses of each
record so I don't understand why I can't store the total expenses in
that record.  I'm also not sure how to setup a subform as you both
have suggested.  Please explain to a beginner.

0
serviceenvoy
4/21/2007 8:27:39 PM
serviceenvoy@gmail.com wrote:

>On Apr 21, 12:28 pm, Marshall Barton wrote:
>> serviceen...@gmail.com wrote:
>> >I have expense fields classified as "parts" or "labor".  I need to
>> >create a formula to look at ExpenseType fields 1-10, determine if they
>> >are labor, and then calculate the Total fields sum of the labor field
>> >expenses into a field called TotalLaborCost.  Then I need to do the
>> >same process to calculate the sum of expenses classified as Parts in
>> >the TotalPartsCost field.
>>
>> >Fields in the form:
>> >"ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
>> >"Quantity1", "Quantity2"..... (how many of each item or how many
>> >hours)
>> >"Cost1" ....(what is the per unit cost)
>> >"Total1"....(the extended price)
>> >"TotalLaborCost" (for the sum of all the labor "Totalx" fields
>> >"TotalPartsCost" (for the sum of all the parts "Totalx" fields
>>
>> Sorry, but spreadsheet calculations like that are extremely
>> complex in a database.  You really need to understand how to
>> design tables using the rules of Normalization (Google it).
>> One fundamental rule is that a table should not have
>> repeating fields like Quantity1, Quantity2, ...
>>
>> Barring you ability to do that, you might have some hope of
>> getting a reasonable result by creating a subform based on a
>> query that calculates the desired totals, but it won't be
>> easy.
>>
>
>I am a novice so I guess I don't understand some of the dangers of
>doing things the way I'm attempting.  I need to track expenses of each
>record so I don't understand why I can't store the total expenses in
>that record.  I'm also not sure how to setup a subform as you both
>have suggested. 



Another rule of relational databases is to never store data
that can be recalculated from other values.  This kind of
total should be calculated in a query so it is is guaranteed
to be correct whenever the query runs.

Using a subform on this kind of table structure would just
be a hack at best and, as I said before, it will not be
easy. Very few people have extensive experience doing things
the wrong way so detailed help is not readily available.

Allen provided a nice explanation of how you might
profitably start a normalized design of your data.  I am
sure your time, energy and learning curve would be much
better served by pursuing a better table structure than
figuring out a messy workaround that will do nothing to help
deal with the innumerable other problems your current
structure will cause.

-- 
Marsh
MVP [MS Access]
0
Marshall
4/21/2007 11:16:50 PM
On Apr 21, 6:16 pm, Marshall Barton <marshbar...@wowway.com> wrote:
> serviceen...@gmail.com wrote:
> >On Apr 21, 12:28 pm, Marshall Barton wrote:
> >> serviceen...@gmail.com wrote:
> >> >I have expense fields classified as "parts" or "labor".  I need to
> >> >create a formula to look at ExpenseType fields 1-10, determine if they
> >> >are labor, and then calculate the Total fields sum of the labor field
> >> >expenses into a field called TotalLaborCost.  Then I need to do the
> >> >same process to calculate the sum of expenses classified as Parts in
> >> >the TotalPartsCost field.
>
> >> >Fields in the form:
> >> >"ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
> >> >"Quantity1", "Quantity2"..... (how many of each item or how many
> >> >hours)
> >> >"Cost1" ....(what is the per unit cost)
> >> >"Total1"....(the extended price)
> >> >"TotalLaborCost" (for the sum of all the labor "Totalx" fields
> >> >"TotalPartsCost" (for the sum of all the parts "Totalx" fields
>
> >> Sorry, but spreadsheet calculations like that are extremely
> >> complex in a database.  You really need to understand how to
> >> design tables using the rules of Normalization (Google it).
> >> One fundamental rule is that a table should not have
> >> repeating fields like Quantity1, Quantity2, ...
>
> >> Barring you ability to do that, you might have some hope of
> >> getting a reasonable result by creating a subform based on a
> >> query that calculates the desired totals, but it won't be
> >> easy.
>
> >I am a novice so I guess I don't understand some of the dangers of
> >doing things the way I'm attempting.  I need to track expenses of each
> >record so I don't understand why I can't store the total expenses in
> >that record.  I'm also not sure how to setup a subform as you both
> >have suggested.
>
> Another rule of relational databases is to never store data
> that can be recalculated from other values.  This kind of
> total should be calculated in a query so it is is guaranteed
> to be correct whenever the query runs.
>
> Using a subform on this kind of table structure would just
> be a hack at best and, as I said before, it will not be
> easy. Very few people have extensive experience doing things
> the wrong way so detailed help is not readily available.
>
> Allen provided a nice explanation of how you might
> profitably start a normalized design of your data.  I am
> sure your time, energy and learning curve would be much
> better served by pursuing a better table structure than
> figuring out a messy workaround that will do nothing to help
> deal with the innumerable other problems your current
> structure will cause.
>
> --
> Marsh
> MVP [MS Access]

Well, now I am really confused.  You recommended what Allen said but
then told me I shouldn't use a subform.  Allens subform seemed like
overkill for what I want.  Let's start from scratch.  I'll explain
what I want to accomplish and you tell me the best way and how to
create it.

We have individual records for each service call we run.  Each record
has multiple expenses broken down into labor or parts.  There might be
3 different labor expenses and may be 5 different parts expenses and
each one must be listed seperately to ensure we reimburse various
involved parties.  Since we reimburse others for parts & labor we have
no inventory and the price for the same part/labor varies every time
we pay for it.  Because this varies so much I don't know that using a
subform is feasible.  I need to be able to determine the total amount
of parts and the total amount of labor spent on every record.  I
already have a field that calculates the extended price of each
expense line (lines 1-10)(all related fields are progressively called
QTY1, COST1, TOTAL1, then 2, 3, etc.).  I just need to have a query or
something that looks at all the expense fields, and adds the extended
price for all the expenses marked parts and show me the total in a
field called TotalPartsCost.  Then the same for all the labor
expenses.  I am a novice so please help me understand the logic behind
your solution as well as the solution itself.  I'm open to doing it
the best way for the long term health of my database assuming I
understand what the heck you're telling me.

0
serviceenvoy
4/22/2007 3:01:04 PM
serviceenvoy@gmail.com wrote:

>On Apr 21, 6:16 pm, Marshall Barton  wrote:
>> serviceen...@gmail.com wrote:
>> >On Apr 21, 12:28 pm, Marshall Barton wrote:
>> >> serviceen...@gmail.com wrote:
>> >> >I have expense fields classified as "parts" or "labor".  I need to
>> >> >create a formula to look at ExpenseType fields 1-10, determine if they
>> >> >are labor, and then calculate the Total fields sum of the labor field
>> >> >expenses into a field called TotalLaborCost.  Then I need to do the
>> >> >same process to calculate the sum of expenses classified as Parts in
>> >> >the TotalPartsCost field.
>>
>> >> >Fields in the form:
>> >> >"ExpenseType1" "ExpenseType2"..... (type of expense: labor or parts)
>> >> >"Quantity1", "Quantity2"..... (how many of each item or how many
>> >> >hours)
>> >> >"Cost1" ....(what is the per unit cost)
>> >> >"Total1"....(the extended price)
>> >> >"TotalLaborCost" (for the sum of all the labor "Totalx" fields
>> >> >"TotalPartsCost" (for the sum of all the parts "Totalx" fields
>>
>> >> Sorry, but spreadsheet calculations like that are extremely
>> >> complex in a database.  You really need to understand how to
>> >> design tables using the rules of Normalization (Google it).
>> >> One fundamental rule is that a table should not have
>> >> repeating fields like Quantity1, Quantity2, ...
>>
>> >> Barring you ability to do that, you might have some hope of
>> >> getting a reasonable result by creating a subform based on a
>> >> query that calculates the desired totals, but it won't be
>> >> easy.
>>
>> >I am a novice so I guess I don't understand some of the dangers of
>> >doing things the way I'm attempting.  I need to track expenses of each
>> >record so I don't understand why I can't store the total expenses in
>> >that record.  I'm also not sure how to setup a subform as you both
>> >have suggested.
>>
>> Another rule of relational databases is to never store data
>> that can be recalculated from other values.  This kind of
>> total should be calculated in a query so it is is guaranteed
>> to be correct whenever the query runs.
>>
>> Using a subform on this kind of table structure would just
>> be a hack at best and, as I said before, it will not be
>> easy. Very few people have extensive experience doing things
>> the wrong way so detailed help is not readily available.
>>
>> Allen provided a nice explanation of how you might
>> profitably start a normalized design of your data.  I am
>> sure your time, energy and learning curve would be much
>> better served by pursuing a better table structure than
>> figuring out a messy workaround that will do nothing to help
>> deal with the innumerable other problems your current
>> structure will cause.
>>
>
>Well, now I am really confused.  You recommended what Allen said but
>then told me I shouldn't use a subform.  Allens subform seemed like
>overkill for what I want.  Let's start from scratch.  I'll explain
>what I want to accomplish and you tell me the best way and how to
>create it.
>
>We have individual records for each service call we run.  Each record
>has multiple expenses broken down into labor or parts.  There might be
>3 different labor expenses and may be 5 different parts expenses and
>each one must be listed seperately to ensure we reimburse various
>involved parties.  Since we reimburse others for parts & labor we have
>no inventory and the price for the same part/labor varies every time
>we pay for it.  Because this varies so much I don't know that using a
>subform is feasible.  I need to be able to determine the total amount
>of parts and the total amount of labor spent on every record.  I
>already have a field that calculates the extended price of each
>expense line (lines 1-10)(all related fields are progressively called
>QTY1, COST1, TOTAL1, then 2, 3, etc.).  I just need to have a query or
>something that looks at all the expense fields, and adds the extended
>price for all the expenses marked parts and show me the total in a
>field called TotalPartsCost.  Then the same for all the labor
>expenses.  I am a novice so please help me understand the logic behind
>your solution as well as the solution itself.  I'm open to doing it
>the best way for the long term health of my database assuming I
>understand what the heck you're telling me.


I guess my comments about using a subform with your current
table structure got confused with Allen's idea of using a
Normalized table structure and a subform.  The core issus is
not the use of a subform, but the proper structure of your
data tables.  If you don't get rid of the multiple cost and
total fields in a single table, you will have a terrible
time making a useful application.

If you follow Allen's guidlines about using a separate table
for each separate "entity" (ExpenseType, ExpenseDetail,
etc), then the use of subforms is a natural consequence that
parallels the tables.

It would be benificial for you to take some time out to do
some homework re Normalization.  You can get a brief
overview at http://support.microsoft.com/kb/283878/en-us
For more extensive discussions use Google and concentrate on
just the first three normal forms of relational databases.
I just searched for:  Relational Normal Forms
and quickly found this nice article (of more than a million)
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

-- 
Marsh
MVP [MS Access]
0
Marshall
4/22/2007 4:36:17 PM
Reply:

Similar Artilces:

Number formats and fields
Hi All! I have a list of equations in a word document that I need to add a reference (the first equation in the document will be equation A, the second will be equation B, etc). So I need a numbering field. However, when I get to equation 28 I want the format to be AB, then 29 to be AC, not BB, CC like the default A, B, C… numbering gives you. However, I will be adding other equations randomly throughout the document and referring to the equation reference in text paragraphs, so I would like them to auto update when I add another equation in the middle of the document. Doe...

Calculate home equity in Money?
I have an asset account for my house. It is tied to a mortgage loan account. I also made a down payment from my checking account. How do I calculate the equity in the house using Money since it has appreciated and I have paid down some of the mortgage? Job #1: figure out what your house is worth. Nothing internal to Money will provide much help with that. Job #2, with a calculator, subtract sum of the current balance of all outstanding loans from the value determined in step 1. The remainder is the equity. "Taylor" <123@456.com> wrote in message news:Yp76g.184$Qq.73@...

Calculate height of listbox?
Is there a forumla to calculate the needed height of a listbox in order to contain a given number of lines of a given point size? Not without a bunch of GDI API work. The AutoSizeTextBox solution on my site shows you how. A far simpler solution, that is almost as accurate would be to use code/logic like: Assumes ListBox control named lbHeight Assumes TestBox control named Text11 Assumes CommandButton named CmdSize Enter a desired number of rows value in Text11 and then click on the CommandButton. Private Sub cmdSize_Click() On Error GoTo Err_cmdSize_Click Dim x As Integer ' conv...

Calculate Formulas in Highlighted Cells Only--not whole sheet
In office 97 I was able to recalc only the cells highlighted (control + L) as opposed to the entire sheet or workbook. In Excel 2002, how do I recalc only the cells I have highlighted? Thanks I don't recall this shortcut in xl (any version). I'm guessing that you had a macro that did something special. In xl2002, you could use a macro like this (assigned to ctrl-shift-l) Option Explicit Sub testme() Selection.Calculate End Sub But I think I've read posts that calculating a single range can be bad--it can screw up the calculation dependencies. I think that Charles Will...

calculation
Hi, I work with 3 large excel files (5000 KB). File A is my summary and is linked with the 2 others. I use a lot of reference cells and I discovered that some reference cells does not work. Some cells do not calculate automatically. I saved everything on the C drive in order to minimize the lenght of the formula but I still have problems. Any idea what else I could do? Thanks in advance. Can you share some examples of the cells that don't calculate? What is the formula in one of them? What's different between that cell and one that does recalculate? -- Regards, Tu...

How stop duplicate fields when creating a Report in Access?
no metter what method I use to create the report - it throws double fields into the report. You manually delete one of each and proceed and it continues to add double fields. We did not have this issue in 2003 version. WHat is the problem and how do we fix it? Are you talking about the record source (a query) for the report listing fields twice? If so, check the underlying query and see if it has an asterisk (return all fields) in SELECT clause of the query. It is a property of queries in design view (Output all fields). Set this to No to eliminate the fields showing ...

Time calculation #3
How can I calculate average time using hundredths of a second? For example: 1:43:23,88 is 1 hour, 43 minutes, 23 seconds and 88 hundredths of a second. If I want to divide this with, say 20, it's not so difficult to do by excluding the hundredths. But how is it done including it? (I want to use it to calculate average lap time running tracks). // Roger (Sorry for my poor english...) Roger, If you haven't done so, format it to include hundredths. Format - Cells - Number - Custom: hh:mm:ss,00 hh:mm:ss,000 -- Earl Kiosterud mvpearl omitthisword at verizon period net --------...

How to add custom field to Task Form in Outlook2k?
I have created custom fields for task and want to add them to the form that pops up when I create a new task. I have added my custom fields to the Simple View & others where task are listed... But I want to be able to input custom data when I create a task... And I want to be able to modify that custom data later if necessary. How can I make my Custom Task Fields appear in the standard Task Form ? thanks for any help. tmb Start by going to http://www.slipstick.com/dev/forms.htm to see about Outlook forms design. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Abso...

Query Criteria 05-24-07
Finding it hard to get my head round this so help needed! I have a query based on form fields. There is date range field (always selected) and 2 other optional criteria needing 4 WHERE/OR statements to get the required results. I want to add another optional criteria field which i think will leave me with 11 WHERE/OR statements and a big headache! Is there a simple way around this? ...

How do I join 2 fields to limit criteria?
I am trying to set a criteria with an OR clause in between 2 fields. Let's call the 2 fields Field1 and Field2. I'm trying to do this example: Only show the records that have this criteria: Field1 = A, and Field2 = B,C,D,E,F,G These 2 fields contain the same type of data, and we need to limit the criteria together between the 2 fields?? Any ideas on how to do this when using excel to build a query in Microsoft query? Christen You could use an advanced filter. Assuming field1 and field2 refer to columns A and B, type this into an open cell (say M22) outside the data table: ...

fields
anyone know if you can put in a formula to automatically populate a field. I created the attributes in deployment manager.. added the fields. but the third field I want to be a product of the two? E.g. total or margin! Thanks for any help. Hi Joe, As far as I am aware there is no support for calculated fields. It would be possible to trigger an OnChange event, but in order to do so, you would need to link it to a picklist (combo box) on the form. This could then perform a calculation and assign it to the field that you want. Pretty ordinary solution, I know... but you work with wha...

Inactive Field in IV00101
Hello: There is a field in the IV00101 table labeled "INACTIVE". Does anyone know if this field is used anywhere? Other than discontinuing an item, I don’t know of any way to inactivate an item. And, I haven’t found any time that this field gets updated. It might just be a field for use in future releases, perhaps? Thanks! childofthe1980s ...

Why does my excel spreadsheet show ### in place of a sum?
For some reason, some autoSUM cells in my spreadsheet are showing up as "#" symbols even though they are correctly calculated in the function window... Any ideas? Try making the column wider. "S. Teichman" wrote: > For some reason, some autoSUM cells in my spreadsheet are showing up as "#" > symbols even though they are correctly calculated in the function window... > Any ideas? Typically the # characters indicate that the column is not wide enough to display the result. Try making the column wider. -- Cordially, Chip Pearson Microsoft MVP - E...

Changing the calculation order
Can the order that Excel recalculates be changed to start fro the lower right? Excel automatically adjusts its final calculation order depending on the dependency chain in such a way as to get the correct result regardless of formula sequence. Why would you want to change this? Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "A Process Modeler" <A Process Modeler@discussions.microsoft.com> wrote in message news:4D17FF7C-D763-4FAB-980C-463DB8A75FCA@microsoft.com... > Can the order that Excel recalculates be changed to ...

adding values if the criteria in two columns are met
I need a formula to add values with two criteria in a column. I am trying to add the values if two criteria are met. For example below, I want to add the total of the amount that are for 2008 and has a Status of No. The answer would be $86,750. I could use the Pivot Table, but It's not what I want to use. Is there another way to do it with a formula? Amount Percent Year Status 75,000 3.00% 2008 No 6,750 3.00% 2008 No 9,100.00 2.00% 2009 Yes 620,000 2.00% 2009 No 2,800 2.00% 2009 No thank you, mayasmom Try this: =3DSUMPRODUCT(--(C2:C10=3...

recipient field
one of my users if having a problem, when she starts to compose an e-mail, and starts to type in the recipient user name, the user name repeats itself. for example, if she e-maled something to me, and started to type my name, instead of one copy of my name in the TO: field, there are 2 copies, and sometimes even 3... she has a copy of outlook 2003, and we are using exchange 2003 with AD throughout our enterprise, with windows 2000 as the desktop and the server end OS ...

How to add more field on My Work
Dear all, In PWA, as a Project Manager, when I go to Server Setting, then Manage Views and select "My Work" in order to manage the view of "My Tasks" for any users, It seems some fields are not available like the "Baseline Finish" or "HREF". However, they do appear on "Project" for GANT purpose. As I need to customize My Task view, how is it possible to add this fields? Thanks for your reply, P.S I use 2007 SP1, cannot migrate to SP2 cause of many errors with our architecture IT. 1) Check if you can create a new Enterprise D...

Data Fields Changing
I have a Master Table in a Master DB linked to a few sub databases. The input forms (to update Master table) are in the sub databases based on queries also in the sub db's. The field names in the Master table change names occasionally. Is there an efficient way to update code in modules, forms, and control/fields in the form(s) when the fields change in the Master table. I can update the control source/fields in the form for the new fld names - but I noticed the old/previous field names still in the dropdown window (at top left of Font window). Also do I have to update a...

Reference a Popup field
In my main form (frmMain) there are several text fields that utilize the hyperlinks option. Each hyperlink brings up a different popup form which are all copies of each other with the only difference being the content of a label. Ideally I would like to utilize a single popup form and programically change the label to reflect some type of phrase indicative to the text box in frmMain. Is there a easy way to do this? In your code opening the popup, change the Caption property of the label. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www....

Criteria Row
Good afternoon, I have a question problem that I can't seem to solve. It primarily regards the usage of "and" and "or". I have a table that looks like the following: www,4,rrr, 1,2,3,4 xxx,4,eee,0,3,6,7 yyy,4,fff,0,0,0,0 zzz, 4,ddd,40,40,40,40 ggg,4,jjj,4,0,7,0 kkk,4,ddd,51,8,0,9 What I would like to do is select all rows with non zero values less than 50 in the 4th, 5th, 6th , and 7th column. In the above example I would want the www, xxx, zzz, and ggg rows because all the values are not zero and none of the values are greater than 50. I do not want yyy because th...

Autofill Regarding Field in Follow-up tasks
I am trying to create follow-up tasks from an appointment that I had and they do not automatically associate with the appointment. i.e. every new task I create is not regarding the appointment or anything else for that matter. I would like this to automatically be populated for each follow-up, but still have the option of changing it if I want to relate it to an opportunity or a campaign(campaign activity). Any advice would be great. Sample coding would be best. THANKS! I would like to know how to auto-fill the "sender" of phone calls to be the same as the owner of the task...

Can't trim white space from large varchar field
Hi, I am reading two very large varchar fields (2600) from the sql server 2005 database of our ERP. This reading is being done by a cursor. In the cursor select statement I rtrim and ltrim the field. No matter what I do the length of the field is always 2600. I can see that there is some sort of white space to the right of the text. Whatever that white space is, it can't be trimmed by ltrim or rtrim. I tried running the sql from the cursor as a query and had the same problem. Any help would be appreciate it. fig000 If you do a RTrim of a varchar column, the...

field not by that name
the following piece of code is intended to requery a subform (frmcustomerlist) after a new customer is added. The following error occurs -- there is not field named forms!frmsearchkeynamedoslook!frmcustomerlist in the current record when this code is reached docmd.requery "forms!frmsearchkeynamedoslook!fromcustomerlist It is true there is not a field by that name because that is the subform i am wanting to requery. I even tried forms!frmsearchkeynamedoslook and the same error occured -- there is not a field named forms!frmsearchkeynamedoslook what am I missing. ...

Caption Criteria
Is it possible to set the caption property of a form based on criteria selected from several other forms? eg: from a Categories form, I click a cmd button to open a results form displaying records for the chosen category and set the results form caption to that category (this works ok for single caption criteria) as well as from a payees form, open the same results form and set the caption to the selected payee. In other words, set the caption to various other form criteria. Thanks in advance. -- Don, Sydney Australia You can programatically set the form's caption to whatever you ...

parse data from text field
I currently have a field in a form that contains the exact path to files stored on my computer (ex. C:\file1\file2\file3\samplefile.doc). The path to the file is different for most of the files. I have changed some things in the database and I would like to parse just the file name and extension (ex. samplefile.doc). I have tried the Mid, Left, Right, and InStr functions but I have not been able to get the desired data. Any help with code to parse the data correctly would be appreciated. The names of files are not equal in length. Thank you accessuser1308, You kinda ...