Results 1 to 2 of 2

Thread: Inserting data if it doesn't exist

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    1

    Inserting data if it doesn't exist

    Hi everyone,
    Am just getting started so i have a ton of questions but i guess this is the best place i have found yet.
    I am a high school student and there is a database that we are creating.
    So let me jump straight into my questions:
    1. Can one have a condition in an insert into sql query?

    If yes how is the syntax

    And if no
    How do you work around it.

    2. The database we are creating is being made in Access 2007 or Access 2010 which are pretty much a like.
    So long story short let me just give out the details of the project then i shall state where we have a problem.

    Money Inc. Is a self help group intended to improve the economic status of its members through pooling of financial resources. The organization sustains itself through registration fees of its members, monthly contributions, interest acquired through loans given to members, and penalties for delayed payments. The members benefit by getting cheap loans from the organization.
    Membership to the organization is by registration. There are two types of membership; individual and group. Group membership constitutes two or more individuals who have common interests. An individual member pays a registration fee of $20 while a group pays $50. During the registration of individuals, personal details are captured. When registering a group, details of the group and those of its individual members are captured.
    An individual member contributes a monthly share of not less than $10 to the organization. For those registered under a group, each member contributes a monthly share of not less than $10 where $2 is the group share contribution by the member. All monthly contributions are treated as shares for the contributor and attract annual dividends. Members can borrow loans from the organization based on their total shares. Members registered as individuals can be loaned up to three times their share contribution. The repayment period does not exceed three years and the loan attracts interest at a rate of 1.2% per month on the principal amount borrowed. Members registered under a group can be loaned up to four times their individual share contribution. The repayment period does not exceed four years and the loan attracts an interest at a rate of 1% per month on the principal amount borrowed. A group can also get a group loan amounting to three times the group's total share contribution which attracts interest at a rate of 0.8% per month payable within 5 years. All payments to the organization are monthly and must be made by the last day of the month. Any defaulted payments are penalized at a rate of 10% per month. Every end of the year, the organization determines the total interest charged on all loans and total penalties on defaulted amounts. 60% of this amount is paid as dividends to the shareholders each getting an amount proportional to their total share contributions. The balance is retained to run the organization.

    #############
    The things I have to ensure are:
    1. Maintain a record of members and groups
    2. Maintain a record of registration fees
    3. Maintain a record of monthly contributions
    4. Maintain a record of loans borrowed
    5. Compute loan repayments and interest
    6. Compute income for the organization
    7. Compute dividends to each shareholder.
    8. Generate reports on demand.
    ##########

    For the most part registration is all done however the problem comes in in the monthly contributions.
    The system that i envision is one which when a user clicks on their details all their information is auto-generated and they get a list of months that they didn't pay and how much they should currently pay.
    The part that i have already done is created a table to hold monthly contributions for those registered as individuals; so i wanted to come up with a vb code that would add new months automatically to this table (which are all the months that come after the registration date of the person to current date [or from the last time this persons record was updated in the monthly payments table to date])

    May be my approach is way wrong from a professional's angle but that is why i am here, i have come to the professionals to help me work around my first complete database.
    I really need help.
    I don't know what to do next.

    Thank you all in advance.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Inserting data if it doesn't exist

    Welcome to VBForums

    It is possible to have a condition in an Insert Into, but it is rather awkward... rather than using a Values clause you can use a Select statement (with the values placed directly in the field list), but you need to be careful to ensure that it can only return a maximum of one row, otherwise you get lots of duplicate records.

    What is generally done instead for this kind of situation (with an Access database at least) is to use your non-SQL code to do the work.

    A common way is to load the existing data (ordered appropriately) and create a loop that goes through the months you might want to add, and check if they are in the existing data. When one doesn't exist, add it to the database.


    By the way, you mention VB but didn't specify which of the variations of VB it is, and the kind of code varies quite a bit between them so it is best to tell us. Options include VB.Net (VB 2002 and later), VBA (the VB Editor inside a program like Access), VB 6, and VBScript.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width