Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modelling bank accounts (2002)

    I'm creating a database that models personal bank accounts. I've got a customers table (CustomerID) and accounts table (AccountID) and I'm relating them in a many-to-many relationship with a join table. The accounts table (tblAccounts) links to a transactions table (tblTransactions) with a lookup for the AccountID.
    Is it possible to, on one form, create a new customer, a new account and key the first transaction for this account?? I've tried but failed so far. I may have over-complicated it by storing an OpeningAmount and OpeningDate in tblAccounts. Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modelling bank accounts (2002)

    I understand that one customer can have several accounts, but are you sure that several customers can have the same account?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modelling bank accounts (2002)

    Yes. Depending on the type of account, it can have several signatories. For example, a Club or Charity account. When opening such accounts, one of the signatories may be an existing customer and another not. This should be reflected in the database. Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modelling bank accounts (2002)

    OK. In that situation, I would use an unbound form (i.e its Record Source is blank) on which the operator can enter the minimum details needed for
    - A new customer
    - A new account
    - A first transaction
    Put an OK button on the form; use the On Click event code for this button to create new records in the various tables; you can use ADO or DAO for this.

    For viewing and editing existing records, you can use a main form with one or two subforms, for example a main form bound to the accounts table with a subform for the signatories of the account, and another subform for the transactions of the account, or a main table bound to the customers table, with a subform for the accounts of the customer, and another subform or a sub-subform for transactions.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modelling bank accounts (2002)

    Are you trying to model the way an actual bank account works or are you trying to create a relational model for handling bank accounts? Believe me, it isn't the same thing. In banking terms, a "customer" is an account holder, which may in fact be several persons or even an organization. Those account holders are not tracked as individuals with respect to the account. Depending on what you're trying to accomplish, you might want to have a table of authorized signers, as distinct from customers. Essentially, when you group people together as an account holder, they can no longer be related to separately and banks generally don't even try. Tracking "customers" is a lot like normalizing addresses: at some point the usefulness of the fully normalized model becomes questionable because the maintenance cost becomes unacceptable. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modelling bank accounts (2002)

    Thank you. I suppose what I was trying to do was to force Access to create a record in the join table automatically.. based on the new customer and account details displayed on a form. Am I right to infer that this cannot be done? I.e., without DAO?
    I attempted to do this with a query using AutoLookups. It is right that the AutoLookup can look up fields from the one side of two related tables, but cannot then create a new record in a third table?? Thanks, Andy.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modelling bank accounts (2002)

    As far as I know, AutoLookup can't create a record in a third table.

    You'll have to create records for a new customer and for a new account before you can create a new record in the join table.

    BTW, I would study Charlotte's reply carefully - she has been a banker earlier in her career.

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modelling bank accounts (2002)

    I'm trying to create a (simplified) relational model of how bank accounts 'might work'. I realise that there are many other issues to be considered 'in real life'. For example, the idea of a customer as a single entity is tricky, as the same person could be registed using a slightly different name and/ or address?!
    For a simplified version, I've got three different bank account types, say Basic, Premium and Gold. I assume that any individual customer can be uniquely identified in the Customers Table and that the signatories can be taken from this table. If I keep it this simple, do you see any difficulty in creating a sensible (small) database? Thanks, Andy.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modelling bank accounts (2002)

    A relational model is quite possible, even if - as Charlotte indicates - it doesn't correspond to the way banks actually operate.

    If you want to use one form in which the user can enter a new customer AND a new account AND a first transaction, using an unbound form and DAO or ADO code to create records seems the best way to me.

    Alternatively, you could create a form based on the transactions table, with combo boxes for customer and account, and code in the On Not In List event to pop up forms to let the user create a new customer or account. So effectively you'd end up with (at least) three different forms anyway.

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modelling bank accounts (2002)

    If I use a Continous Form to add transactions for existing accounts I want to be able to also see a list of signatories for the currently selected account. So I presume I would add a (customers) subform which is bound to a TextBox Control on the main form - which in turn is set to the AccountNumber of the currently selected Account? Does this make sense, and is it how you would do it? Thanks, Andy.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modelling bank accounts (2002)

    Can one transaction have multiple signatories?

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modelling bank accounts (2002)

    Yes, the same person can have multiple names and addresses and the bank doesn't worry about uniquely identifying them through the name and address. Accounts CAN be related to one another, so my combined bank statement includes both my checking and savings account. If I created a business account with a different name, it would be more difficult for the bank to maintain the connection, and I would probably receive two statements, even though banks also collect information like social security numbers to facilitate tax reporting. In fact, different people can have the same name, so name alone isn't a valid unique identifier, nor is address since it can belong to multiple members of a family or by unrelated persons sharing a household. What exactly are you trying to use to uniquely identify a customer?
    Charlotte

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modelling bank accounts (2002)

    In real banking, yes, Hans. For example, an account can be set up to require two different signers on a check.
    Charlotte

Posting Permissions

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