[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Update one table based on the criteria of another table

Posted on 2014-02-20
5
Medium Priority
?
854 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 39

Accepted Solution

by:
PatHartman earned 1500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

656 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