Solved

Update one table based on the criteria of another table

Posted on 2014-02-20
5
816 Views
Last Modified: 2014-02-20
I would like to create a field in one Access table that is based on the criteria of another Access table.

The field would require an if statement and an or statement.

Something like this:
If [table1].[APPLES] or [table1].[GRAPES] is greater than 0
then
[table2].[FRUITCOUNT] = [table1].[FRUITCOUNT]

Any help would be greatly appreciated!
KT
0
Comment
Question by:ktjamms2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Author Comment

by:ktjamms2
ID: 39873777
oops...one other detail. The two tables would be joined by a common Id number.
0
 

Author Comment

by:ktjamms2
ID: 39873930
Maybe I was making it harder than what it really is. This is what I think would work:

UPDATE table1
INNER JOIN table2 on table1.ID = table2.ID
SET table1.fruitcount = table2.fruitcount
WHERE (((table1.apples)>0)) OR (((table1.grapes)>0)))
0
 

Author Comment

by:ktjamms2
ID: 39873946
I am still wondering if there is a way to build it into the structure of the table instead of having to run a query every time the count in the other table changed?
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39874590
In a relational database we don't store the same piece of data multiple times.  There is no built in tool/feature that would help you since what you are doing violates normalization principles.

If you are using A2010 or A2013 (possibly A2007), you can create data macros.  Data macros are the ACE equivalent of triggers in "big" databases.  I haven't created any so I can't give you specifics but in theory, you can create a trigger that runs if a particular table is updated and in that trigger update a different table.  I am assuming that a Data Macro would offer the same options.

Your example is hypothetical so there really isn't any way to advise you further except to say, you should rethink your schema.
0
 

Author Closing Comment

by:ktjamms2
ID: 39875136
Thank-you!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question