How to add a table in access

Question

0.00/5 (No votes)

See more:

Hi guys

quick question i currently got 2 tables in access,
table1 is where the client add in his personal details
table2 is where the client add in his creditor details(including their installment amount)

and in table1 there is a field called total installment amount

my question is how do i run a query which will add all the clients installment amount from table 2 and insert the total amount in table 1, note that the 2 tables is linked via their ID Number,

hope this make sense

Posted 2-Jun-15 3:55am

ebie147

Updated 2-Jun-15 3:56am

v2

Add a Solution

Comments

CHill60 2-Jun-15 9:56am

Have you tried anything?

ebie147 2-Jun-15 9:57am

i have played around but can't seem to come around to anything

CHill60 2-Jun-15 10:08am

It would be a good idea to share the code you've played around with and explain what "not coming around to anything" means to you.
It's also a good idea to share some example data with an expected result.
The version of Access you are using would also be useful

ebie147 2-Jun-15 10:12am

im using access 2010,
what i am trying to do is this
total Instalment(table1) = instalment amount(table2) where id Number =

note that instalment amount(table 2) consist of a lot of records
also the table has a relationship of one to many
so it should only add the instalment amount(table 2) where the id number matches the id number in table 1

ebie147 2-Jun-15 10:16am

so for instance in table 2 i got the following
ID Number=12345
creditor1=test
Instalment1=123
creditor2= test1
instalment2=123

then i want those 2 instalments to add where the id number is 12345 in table 1 and insert it in table 1 where
total instalment=246

CHill60 2-Jun-15 10:19am

See Solution 1 - but also heed the advice in Solution 2 - there is little point in storing the total anywhere

ebie147 2-Jun-15 10:01am

what makes it hard is that it only needs to add the creditors installment for that id number of the person

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2015-06-02T04:12:00

If the total installment amount is derivable from other fields, then there is no need to store it. It is like getting someone's age from his date of birth. You only need to store the date of birth whereas the age is incremented over time and thus no point in storing it.
Learn more:
1. http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html[^]
2. http://www.studytonight.com/dbms/database-normalization.php[^]
To get the sum of a field, check this out: http://www.w3schools.com/sql/sql_func_sum.asp[^]

_Asif_ · Answer 2 · 2015-06-02T04:10:00

Solution 1

Though i have not tried this, but it should work. If it didn't then you can do a google search to tweak it.

SQL

update table1 as a
    set a.totalInstallmentAmount = (
        select SUM(b.credit) as credit
            from table2 b
            where b.ID= a.ID)

Posted 2-Jun-15 4:10am

_Asif_

Comments

ebie147 2-Jun-15 10:20am

so how would i do this in Microsoft Access?