1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Automatic insertion ???

Discussion in 'Business Applications' started by miller08, May 5, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. miller08

    miller08 Thread Starter

    Joined:
    Mar 28, 2010
    Messages:
    26
    hi, i am workin on a database with some missing fields. in some cases, the price for a certain category (fish species) was not recorded for a particular instance (day). i have calculated the average price for each fish species in a separate query. i would like to automatically insert this average price into instances where there is no real price available (empty fields which correspond to a species name). does anyone know if this is possible, and how? i have quite a lot of empty fields and this would save a lt of time and frustration!

    thanks!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You can run an Update Query, but your query will need the Fish ID in it to link your "prices" query to the Fish table.
    You would also need to put an "Is Null" or 0 (depending on what is in the table for blank prices) in the Criteria Row of the Price Field to prevent the query overwriting the prices that are there.
     
  3. miller08

    miller08 Thread Starter

    Joined:
    Mar 28, 2010
    Messages:
    26
    hi OBP, thanks for your response.
    i've tried what you said, using the fish species to link the 2 tables. i also have "Is null" in the Criteria Row of the Price Field, but i think that is what makes the table return only lines which have no price entered. so, i get a table with no prices at all, instead of the software replacing "null" fields with the average prices.
    i've tried using the expression builder. can i create an "if" condition in access? basically what i want is something to this effect:
    if [average price].[price] = null, [average price].[price]= [query].[Total of price]
    does this make sense? im not sure how to do this type of expression in access and keep getting error messages

    thanks again!
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The way that I would tackle this is to derive the average price for each fish type in the query as you have, but using the NOT IS NULL, so that only fish with prices are used.
    Then use that query as the basis of the Update Query that use the Is Null to identify the missing prices.
    You could even put the average price in a temporary table along with the Fish ID to use in the Update query.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/921198

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice