Solved

Update one table based on the criteria of another table

Posted on 2014-02-20
5
802 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
  • 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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now