Frustration with Tables, Relationships, and Combo Boxes

[mbentle]

I hope this is in the right forum.

I have a problem. I'm a fairly new Access user and I'm trying to create a fairly complex database used for mortgage quote retrieval. The idea, is to be able to search through a database of lenders to find which lenders offer different, specific types of loans or products (as they are termed in the database).

Where my problems are coming into is that most of the data we are storing are simple details (i.e. a yes/no field indicating whether a specific feature is available, or a combo box of options for a specific field). There isn't a great deal of complexity for each field. However, there is about 50 different fields (I'm guessing).

Here's where I'm at. I have tried to normalize the data as much as possible. For the drop down menus I've broken the fields into separate tables so that I can I can pull combo boxes to ensure data integrity. I have created a lender table that allows the user to retrieve immediate contact information after pulling up the appropriate product.

Here's my problem:
1. When I try to create a combo box that allows for the selection of a third generation relationship table, I get input problems. (i.e. a parent of a parent of a child relationship as demonstrated in the attachment via the ProductTbl > PropertyTypesTbl > MaxAcresTbl). It either won't allow me to establish a combo box or when the combo box is selected, it won't allow for any additional data entry (on the data entry forms).

2. The PropertyTypesTbl, ProductOptionsTbl, and UnderwritingCriteriaTbl are all details of the ProductTbl. I broke them out to simplify the data entry but I'm not exactly sure this is the appropriate step. In reality, the details of say the PropertyTypeTbl are as significant to the ProductTbl as are the fields in the ProductTbl such as ProductName. I guess I just need someone who is more experienced in this type of development to take a look at what I've done and tell me if I'm laying this out properly or if I've made critical errors. I think I've made errors but after a week of staring at this I really am not sure where or how to correct the errors.

3. Because the database is search driven, and because it needs to be absolutely dummy proof (most of the users aren't access savvy), it would be great to incorporate all fields as 'selectible' in one search function which would return the names of the appropriate lenders offering the product features selected. What is the best way to set this type of search form up? Is each field individually SQL coded? Does the Wizard offer anything that will deal with this type of need. I tried to use the Wizard to create a data entry form that would allow the user to enter one product with all of the fields outlined and as soon as I tried to incorporate the 'detail tables' that would link to the combo boxes, I got an 'we cannot create that form' error. I tried it with subforms and got the same problem.

I realize this is a pretty big project to undertake, especially since it's the first database I've created, but it needs to be done and the only way it can get done is for me to do it.

Your suggestions and time are greatly appreciated. Thanks

 

[mbentle]

geeze guys, I was hoping to at least get ONE reply. Oh well, thanks to those who looked at it.

 

[Mike375]

With your point 3 you can enter query criteria on the query grid by using references to a form.

For example, say that you had a field LoanType and the various options wer ABC, XYX and EFG and the three of these options were in a combo.

On the criteria line of the query for the field LoanType you enter

[Forms]![YourFormName]![YourComboName]

You can do this for several fields and if they are all on the same criteria line then it becomes an And situation for each of the criteria. In other words if you also included a field Lender then both combo boxes would need an entry and the records returned would be those that met both the LoanType and Lender criteria that was selected. If you use different criteria lines for each entry then it becomes Or. If it is an And situation then all combo boxes that are forming query criteria need to have an entry. For Or you only need one of them to have an entry.

I think you have way too many tables which seems to be what you are saying in Point 2. I have had plenty of disagreements with several people on this forum about normalisation. I think life is much easier if extra tables are there because of a One to Many situation. Perhaps my comment will invite others to chime in.

Without knowing the details and with that limitation in mind it does appear that many of the fields could be on one row in one table for the different lenders. Contrary to what most people on this forum will say I think the best way to kick off is with one table and then the nature of the data will take down the part of other necessary tables. A simple example would be a doctors patients and patient visits. If you started with one table then the very nature of the data, that is, many visits per one patient, will force the second table to be produced.

As a a by the way, with your loan search are you trying to make a selection that could result in a few loan types "to be examined" or for a final single choice.

Mike

 

[Pat Hartman]

There is a pretty good built in solution for question 3 and it requires NO code or even queries. It is called query by form. You can find it on the Record menu. Just open a form that displays all the fields you want to see and then select query by form. Access will display a form with all your fields on it and each one will be a combo. If you select criteria for 2 or more fields, ALL criteria must be true in order for a record to be selected. If you want to use OR criteria, use the Or tab at the bottom of the form.