many parameters

[Donkeygirl]

I have a relationship problem, like wiseguy. I have two tables. One has street addresses and each address has a number, which it corresponds to. Many addresses have the same number. The second table acts as the pk 'one table' for the number, which has many fields of information to it. I need users to be able to type in the parameters for the address they are looking up, ie street number is one field, street is another, street type is another, city is another. These would be parameters. From this I need the number listed for that record's address and the information for that number from the second table.

This is long winded, but it is a complicated problem. From looking closely at the problem, you can see that the relationship, in essence is a many to one....there is no way around it, but I have to think there is someway to deal with it. Can't I dock parameters and have them act as a group or something?
If you think you can help, let me know.
Thanks

 

[RickSpr]

I don't think this is such a hard problem. Set up a main form based on the "one" table, and a subform on it based on the "many" table, with your number as the link field. Then just let them use Filter By Form on the subform. Not only will that let them find a record with a particular address, it will also let them find all the "one" table rows in a particular city, state, etc. Rick Sprague

 

[Donkeygirl]

The problem is that they do not know the one number. They must enter in the address, which is within the county, and that address must locate its number for them.
Also, I am not to good at creating filter by form. Could you give me the run down on setting it up?
Thanks

 

[RickSpr]

Actually, I think I was too sleepy when I suggested the main form/subform idea. Instead, what you need is just the main form, and set its record source to a query that joins your two tables on the number. Again, your users can use filter by form to find everything they need.

You don't have to set up filter by form. It's a built-in part of the user interface (unless you're still on Access version 2 anyway). Take a look at the Records menu when in form view, and look up Filters in the help file or (even better) an Idiot's Guide to Microsoft Access or similar book for users.

Back to the form and its record source query. The query, in effect, creates a temporary table (called a recordset) that contains all the records from your address table and your number table, matched up by the number. To create this query, just create a new query and add both tables to it. If Access doesn't automatically draw a line between the tables, use the mouse to drag the number field from one table and drop it on the number field in the other. This is what matches up the rows in the tables.

Because the recordset this creates looks like a table with all the address data and all the corresponding Number table data in each row, the looking up part is done for you. Then all you have to do is specify which particular address fields you want to find, and Filter By Form (or the other variations such as Filter By Selection) will eliminate anything that doesn't match your criteria.

In this case, Access will do all the work for you, if you'll just tell it the right way. Rick Sprague