Solved

Update one table based on the criteria of another table

Posted on 2014-02-20
5
809 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

838 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