Apend Query to Primary Key

[mjhopler]

Have an odd question and it probably creates bad data managment (I am only a half-way DB user).

I work in the banking indusrty. I created a database to manage all the reports the upper guys give me. From this, I have one main table that lists all the loans. I have a form runnign off of it that then pull in formation from other tables via subforms. My main issue is this main table they gave only shows borrowers and not other entiteis such as guarantors. In all the reports, there is no identifer that lists the relationship of these two. So the next best think I can think of is to just add the guarantors as an option in my form (I have a drop down that shows the borrower, I select it and then it pulls up all the information I need in the subqueries).



However, in the main table, the primary key is account number. I do not have account numbers associated with the guarantor. When I pull the data in, I just want to take all the guarntors and add it to the main table so it will appear in my drop down. Since there are no account numbers associated, a straight apend query will not work as the primary key has to have data entered. Is there a way to do this so that I can put in an auto number that will remain unique using the apend query? Hope that makes sense.

[June7]

Not sure I understand. Do you have a table of guarantors? Does it have a unique ID field (perhaps autonumber type)? Select guarantor from combobox list and save the ID in the loan record.

[NTC]

I believe that you have a Guarantor List table (just a stand alot list of all Guarantors that exist out there). You need a GuarantorLoan table - which is the record source of the sub form in your Main form.

The GuarantorLoan table would have the Guarantor name field which can be a look up field type of the Guarantor List table; its own autonumber key field, plus a field for the Main table key (which should be a Number type field).

With a subform for the GuarantorLoan table inset into your Main form properly using the wizard - the Main form's key will automatically go into the sub form's field so they are cross referenced every time you select a guarantor's name in that subform.

[mjhopler]

Unfortunatly I do not have a guarantor table. That would make it too easy. I also am not provided the relationship of guarantors to loan. I understand what you are saying, but unfortunatly I have to work with the data provided and it does not give these relationships. Rather, where I am pulling my guarantor information from is based on a report they give us that requires us to gather financials on certain people. If that person is on the loan list, I know they are a borrower and if not, then they are a guarantor. Don't worry about it. Decided that to make this work around, it really creates poor data management and worried it will hurt other areas of the database. Going to move forward doing this portion outside the database. Thanks for the help.

[Dal Jeanis]

Sigh of relief. You really did NOT want to do what you were asking. Just shoot yourself, it's less painful.

If you don't know what loan a person is guarantor for, then why would the guarantor be in the database? If a loan gets paid off and you don't know what loan the guarantor guaranteed, how will you remove the guarantor from your database? Unless you have something to link the guarantor to a specific loan, there is no sensible way to include guarantor information in any possible report.

For proper normalization, your borrowers and guarantors, as "financially responsible" entities, should be on one table, your loans should be on a second table, and the relationship between the entities and loans should be documented by a junction table, each record of which says person or entity X has relationship Y to loan Z. If you wanted to implement this, then the guarantors would be any person in the database who did not have an associated loan.