?
Solved

Update one table based on the criteria of another table

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

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

765 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