Controls for a Many- to- Many relationship application 1

[kellyputty]

I am stumped. I have an Access db that I am interfacing with VB6.0.

Situation:Banking Application
Clients can have several different accounts
Accounts can have more than one clients

I can't think of a way to display the info so that the user can add, update, delete records.

ClientID1 --Account1--Balance1
--Account2--Balance2
--Account3--Balance3

Could I use a combo box for the Accounts and Balances? Do they "talk" to each other?

Any advice appreciated,
KellyPutty

 

[BobRodes]

In resolving many-to-many relationships, it's important to understand how the kernel entities interoperate. "Kernel" entities are "real stuff", in other words, entities that are a part of your business. So, Clients and Accounts.

These two are in many-to-many relationship. In order to resolve this relationship, you need to come up with an "associative entity" which, in practical terms, is a table in one-to-one relationship with each of the two.

So, what exactly happens between clients and accounts? When you say add, update and delete records, what constitutes a record? You really have to answer that question for yourself, but I'll speculate that you mean make entries into an account on behalf of a client. Now, if this is true, your associative table might be called "transactions" and represent an entry into an account on behalf of a client.

You would add, update, and delete (well, be careful with the last, it isn't proper accounting practice) records from this table. Of course, you would also add, update and delete data from the clients and accounts table. Once you have the idea down, where which information goes falls into place pretty quickly.

You would use the transactions table to find balances. Just total all the transactions for a given client in a given account.

This is the gist of the underlying theory. Of course, there are practical considerations, such as performing period-end consolidation and so on. But this should get you started.

HTH

Bob

 

[kellyputty]

Thanks Bob,

I have the database structure complete, in which, I have a table that relates clients and accounts in a one-to-one relationship. I am pretty sure the database is of sound design.

I fret the diplay of the VB interfaces which interacts with the db. I can't find a control that would, for example, display one client (say client 1) and thier multiple accounts (account1, account2,..., accountN) and all other info that is a one-to-one relationship with the respective account(Balance,Minimum Balance,AccountType, etc).

Not only do I need this info displayed elegantly, I need the user to be able to change the data via textbox, datalist, or whatever control I end up using.

I was thinking of using MSHFlexGrid because of the hierarchical properties, however, I don't know how or if I can use it to update entries in the database.

Then I thought I could use a textbox for the client (client1,for example) and a corresponding datalist to list the accounts (account1, account2,...,acountn). Then corresponding textboxes for the selected account with balance, minimum balance, accountType; in each respectively.

However, I don't know if they can talk to each other or I don't know how to make them talk to each other.

Any suggestions?


Thanks a million,
KellyPutty

 

[BobRodes]

Oh, sorry, didn't understand your question completely. You might want to look at the VB6 Data Form Wizard. It's an addin. At the very least, it will do a lot of your busy work for you. I generally use it to create a data form and then take out all the code and write my own. You might check out the master/detail option with ADO code (not the data control), study the underlying code if you need to, and then get rid of the "noise".

MSHFlexGrid isn't directly updatable. There are ways to do it (trap user behaviors that imply data changes, overlay the current cell with a text box transparently to the user, take the input, requery the underlying db, that sort of thing), but I prefer not to let users change data via a table type list, so I don't really know the technique.

Whenever possible, I have Add and Edit buttons, and show a single record screen to let the user enter rows of child data. I treat editing of the master record and the detail record as separate processes.

HTH

 

[johnwm]

To manipulate MSFlesgrids see faq222-3262

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[BobRodes]

That's nice! Maybe I'll change my mind.