How to average two fields row by row and not the overall column average??

I have a form that I would like to calculate a qualitative risk score but cannot seem to do so. I have one field "Risk Impact" and another field "Risk Probability" I would like to average these two fields in a third field "Risk Score" The values for each of the source fields will always be 1-5. No matter how I write the expression now, I get the total average for all records of the source field. I would like each row to show the average for only the fields in that row and not a total.

Any tips?

Post your answer or comment

comments powered by Disqus

I'm an archivist who's relearning how to build databases after several years of not building. I'm trying to figure out how to link two fields in the same table.

I have a field that lists the name of a national park (lkpParkName) and another field that lists the park acronym (lkpParkAcronym). Each park has a unique acronym, so I'm using the acronym as the primary key field. Both of these fields are stored in a lookup table that's called lkpParks.

I have another table called tblCollections which uses two drop down fields (ParkName and ParkAcronym) to look up the parks and their acronyms. Right now I have to independently select the park name and the park acronym. I'd like to arrange things so that if you select a park in the ParkName field, it's correpsonding acronym is automatically filled into the ParkAcronym field. I'd also like the reverse to be true: selecting an acronym from the ParkAcronym drop down menu would automatically fill in the correct park in the ParkName field.

How do I go about setting this up?

Many thanks.

I would like to Average multiple fields in a query. For example I would like to Avg [Field1], [Field2] & [Field3].

How can I do it?

Hi all,

I have a form that is setup to display in data sheet view records returned from a query. Lets say there are two fields, called "QuantityMade" and "StopTime." I want the "StopTime" field to be updated to the value Now() when the "QuantityMade" field is updated.

I first thought to use the OnUpdate() event procedure to run an UPDATE...SET...WHERE statement to set StopTime, but I always get the error "Too Few Parameters: Expected 1." It seems you get that error if you specify a column name that does not exist in the table, but I've checked 1000 times and my column names are correct.

Next I then decided to change the UPDATE...SET...WHERE to a SELECT...FROM...WHERE and open a RecordSet. (Which the JET engine doesn't complain about, so I don't know why JET complained about the UPDATE.) I then try to edit the record set, but when it gets to rs.update, it says that another user is already editing the row. Well, I guess that makes sense because the query used to populate the form would have the record open, and I'm trying to update the record in a different spot. So I understand this behavoir.

So my question the heck can I do this update? I'm probably missing something really obvious, but I can't seem to figure it out.

Maybe I'm going about this the complete wrong way? If there's a better way to go about this, I'm all ears.

Thank you very kindly,


Hi all,

Iím really in need of some help from some Access experts! Iím a newbie to Access, but I got myself busy with quite a big project... Iím trying to map out the product supply to shops in the whole country. Iíve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops donít have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3. Finally, I have some products which are the same but have slightly different descriptions and therefore different product codes. Is there a way to link them all under one (existing, one of the descriptions) code without having the other codes showing up anymore?

I would really appreciate it if anyone can help me with this, Iím desperate!!

Thank you so much,

Access 2010 user


I have created a form and I want to multiply two fields with each other and after this put the result in another database field (total field).

I typed in, in the standard value box, the database fields and a * from multiply but nothing happens.

Why is this?

I also want the total field change when one of the to be multiplied fields change. How (where) do I this?

Please help.



I have both an old copy and new copy of my MS Outlook contacts imported into two Access tables (OldContacts and NewContacts). The fields in both tables are identical. I'm missing 50 records in the NewContacts table. By using a query how can I determine which records are missing?

Thanks in advance for any ideas,

I've been trying (with no luck) to combine two fields in a form.

The 1st field is TD1 and the 2nd is TD2.

I've tried all type of combinations but none of them work except for the following:

=[TD1] + [TD2]

The contents of these fields are 10 and 20 respectively.

BUT, it creates 1020 and I'd like the results to be 30 (add them together) instead.

I've looked all over the place but can't find anything that addresses this.

I also tried to do this within the query by adding a new field TD1TD2 but I also couldn't figure out how to get that to work.

Any help would be appreciated.


hi!I need help, please. I have problems on how to do a query where i can sum two values of two different fields. Let me explain you better...

Column1 Column2 Column3 Column4
Lunes 1 2 4
martes 2 4 2
miercoles 1 1 1

select column1 where (column2+column3)

How to combine two tables in one table such that values are neither duplicated nor missed?

please, see the attached screen shot that show my question

i wanted to join Table5 and table6 such that the values of Field1 are all populated in the resulted table but neither duplicated nor overlapped!

How can i perform this kind of combination?

thank you in advance,


Jamal Attached Thumbnails † Attached Files (66.0 KB, 0 views) Reply With Quote 10-28-2011,†06:39 AM #2 Robeen VIP Windows XP Access 2010 32bit Join Date Mar 2011 Location Tulsa, Oklahoma. Posts 1,500 There may be a simpler way, but try this:

Create a new query
1. Select Field1 from Table5.
2. Got to View -> SQL View.
You should see something like:
Code: Select Field1 FROM Table5; 3. Change the SQL in the same SQL pane so it looks like this:
Code: Select [Table5].[Field1] FROM Table5 UNION Select [Table6].[Field1] FROM Table6 ORDER BY [Table5].[Field1]; If you run this - you will get all the field1 values - no duplicates.
4. Save the Query and name it, for example, Query1.
5. Create a new Query.
6. Select - in this order: Query1, Table5 & Table6.
7. Join Field1 of Query1 to Field1 of Table5 & Field1 of Table6.
8. Right-click the Join lines and make the Join Properties of both option 2.
You should see an arrow pointing towards Table5 & Table6 on the join lines [in your new Query].
10. Select Field1 from Query1, Field2 from Table5 and Field3 from Table6.
11. Run the Query to verify that you get the results you need.
12. If you really need it to be a new table - convert Query1 into a MakeTable Query and run it to create your new table.

There may be a more sophisticated [a single query] way of creating the resultset [crosstab query, perhaps??] but I do not know off hand how to do that.

I hope this helps.


I have two columns on a worksheet, the contents of which relate to each other e.g.

01 Name1....etc.
02 Name2....etc
03 Name3....etc

I have a Form with two drop down lists.

The drop downs relate to the entries in the two columns side by side respectively.


I want to select an entry from either drop down list (e.g. "01" or "Name 1....etc") and have the full contents of the corresponding second column only display in the other drop-down field e.g. if I select "02" then the other drop down field displays "Name 2...etc".

If however I select "Name 2..etc" from it's drop down list first, then I also want the other drop down list to syncronise and display the corresponding column entry e.g. "02". (could even have them going both ways)

Can anyone provide me with the code to do this please?

I am open to suggestions on variations as long as the end result - which is to have the related contents selected for the two corresponding entries, displayed on the same FORM.

I have written a application that uses an Access database. I have a table that displays the following information.

Customer Name
Customer Phone
Customer Address
Shipping status

Now in my application I am displaying these 4 things in a data grid. But here is the problem. The shipping team updates their database daily with the new Shipping status. So to get the latest shipping status I created a linked table, lets call is link_shipping. Now All I have to do is somehow get the shipping status field from link_shipping and update the respective table in my database with this info.

To do this I created a query. but now I am having to manually go into my database and run this query to make sure the shipping dates sync up. How can I do this in a better way. The only thing I can think of is to find some way to automate the running of this query every day. But I dont know how to do this. Can anyone suggest anything here.

I have data stored in a table. For each record there are multiple fields that the data is Yes, NO, NA, FYI.
I want another field on each record to count how many of these items are not NA or FYI, but I don't know how to set that field up to automatically count without some kind of user input.

The users are selecting the NA, Yes, No etc but I don't want them to have to do something additional to count and store that number.

I'm guessing it is some kind of macro or query that is bound to the field type but I'm completely lost.

The record would look like this:
NA| YES| NA| NO| YES| [countOpportunities]
countOpportunities would = 3 for this record.

Any help is greatly appreciated!

I know that this should be very elementary but I can't get it.

I need to combine two fields, first name and last name, into one field. How do I do this?

I have tried fname + " " + lname and fname & " " & lname.

Neither one works.


Hello all,

I need help. I have add attachment Sample.mdb.

I need to join two tables, Shipment_details.shipment_number, and shipment.account.
But I want to replace JUST value "819161" in Shipment.account with accounts from Shipment_details.account?????

I have just start learned SQL. It is not difficult to understand, but sometimes I just can not found solution/statement to get result. I know practice, practice, and again practise is the best way.

Please help, and thank you everyone for help in advance...


I have form with fields of table for new entry and subform table that is showing all of records (Master - child fields links are empty) and that works fine.

Now I'd like to make search button that can search ALL the fields in table and show them in subform table.

Any ideas?

How to display memo fields on forms.

How can we display memo filed, with his all huge content
in textbox and in list box.

I am very new to access. I have designed a db for my employer, but I can't figure out how to subtract one field from another without having it subtract fom all others.
My employer is a counselor and wants to know how many units are left per client. There are several diffent services available per client. (88K, 86H, 81H, ect) For instance I tried to use a query, but when it was subtracting
(number of units)-(number of used) It subtracted from all the different service types.
Then I tried to do it on the form and all I get is #Name?
I can email you a copy of the database with a mock client if i'm not explaing myself enough. I know it's probably something very simple and I would appreciate any help. Also, if you know of any good books or websites where I can learn more I'd appreciate the info. I'm learning from Access 2002 Bible.

I have a database for service jobs for a mobile detailing company. Some of our customers have 2 cars but they are not always serviced at the same time. I have 2 fields (car 1, car2) in my order details table so that I know which car was serviced for that order. I have a report that automatically fills in information for that job when I type the customer name and order number. I would like it to also populate the car for that order w/o having car 1 and car 2 visible on the report. I would like a label, Car Serviced, and for that to pull from fields car 1 and car 2. How can I do that?
Thank you

I am writing the following line to print a report:

	DoCmd.OpenReport stDocName1, acViewNormal, , mysCriteria, acDialog, topLabelOnReport

but as I want to print two copies of each page that are printed out so I have changed the code into the following:

	DoCmd.OpenReport stDocName1, acViewNormal, , mysCriteria, acDialog, topLabelOnReport
DoCmd.OpenReport stDocName1, acViewNormal, , mysCriteria, acDialog, topLabelOnReport

Basically I had written one line twice to print two copies. It is now printing two copies but e.g there are 4 pages so it will be printed out in the following order:


so it is difficult to combine same pages together if there are about suppose 100 pages in total of that report . I want it to print in the following format:

page1,page1,page2,page2,page3,page3,page4,page4... ..

Can anyone please tell me hows it possible.



all of em, one-many relationship, 1 op-many containers, 1 container-many products

Sometimes, in a same operation you have dif containers but both containers may have the same products.

I have tblProducts with fields: CONTAINER_ID ,PRODUCT_NAME, QUANTIY, PRICE

So for my invoice I would be make a query that sorts out products by operation in ascendent order


Then I would do use "while EOF" and check PRODUCT_NAME of current entry with PRODUCT_NAME of comming entry (how can i make this?) and if they are the same, delete one entry and sum quantity of that product (how to make this?).

So my questions would be, how to compare one field of an entry with the same field of the coming entry and how to delete an entry.

This merging of info should only be done per OPERATION! How would I make this criteria?

I've created a combo box in Access where I've set the Column Count = 3 and the Column Widths to 0";0.5";1.5". The first field is hidden as it is the ID. The 2 other fields are visible to the user. When the user select the record, the combo box will display the value in the 2nd column as the selected record. What I would like to do is display the 3rd column and not the 2nd column. Is there any way that I can do this? I can't seem to find any properties to set it. Thanks.

Thank you for your help ahead of time! I want to compare two fields based on what is in parentheses. Example: Parathion-methyl (substance) vs Parathion-methyl (product).

Somewhere on the Internet I found the correct property to flip in order to be able to manually add a field to an existing Multiple Items form and have the label able to end up in the Form Header and the field to end up in the Form Detail area... just as Access automatically creates Multiple Items forms.

Please remind me... what is that trick?! I can not find the page again that had the answer.

How to show a field value in form?i want to show a value of a field in a form but from different table.
i try to read it from build event but when i preview it,it will show #Name? in my text box.
Can someone help me to solve this?

Example: Form 1 is create from table 1
then i want insert a field from table 2 in form1.
but i see #Name? in the text box.

Not finding an answer? Try a Google search.