How to use mutiple user input to display results

[Adish]

Hi All



Iam new to Access. I am using 2007 version. I have a form where user can choose option from radio buttons to search by address or property number or name.
If they choose search by address then I have 3 text box and a combo box to get input. e.g they would enter house number, the suffix and unit number and from combo box select the available street name. I have managed to populate my combo box with street names and bound the input boxes to the appropriate fields like house number text box to HOUSE_NO field from the table.

Now I cant make my search button/command to work when the user enters the address to search. I have provided appropriate text boxes in the form to display results for the particular property which is to be searched.

Please if someone can help. I am stressed out with this for 2 days now.

The tables I have is Address (Address_CTR as PK and PPROPERTY_CTR as FK), Property Table (PROPERTY_CTR as PK), Street Table (STREET_NO as PK and STREET_NAME as other field in the table), Name Table (NAME_CTR as PK), Association Table to eliminate the many to many relationship between name and property and address tables since one person can own many properties and one property can have many owners, and one person can have may address associations

[orange]

Can you describe to readers, in simple English -
what your data base is about - the things involved in the database (Owner, Property.......)
who will use it to solve/answer what
What business is this related to?

The common approach to database is to identify the things, then get your tables and relationships set up to meet/support your business rules. It isn't clear that you have done that.

[June7]

Review http://www.allenbrowne.com/ser-62.html

[Adish]

My database has got main tables as Property which has property details like property number, its value, Valuation Number, legal description, My Name table has owners name details, and phone numbers, emails. The Address table has all address details which has a street number that is linked to the Street table providing the street names. I had to make a associative table named as Association to get the association type as an owner may have a main postal address, another postal address for their pets, another for their business and their association sequence. E.g I want the main postal address for their property to show on top and pet's postal address to show second etc.

My main objective with this form is to populate the data from the tables and update details as necessary, eg. I can update phone numbers and end a current postal and update with a new one if there is a change of postal. I also want to list all the owners of the property which is searched.

[June7]

Usually a form can do data entry/edit for only one table.

What are the relationships of these tables - 1-to-1, 1-to-many, many-to-many?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Adish]

Thanks heaps, I have attached my database for analysis.

I am not sure if this would help but the key 1 and key 2 in Association table will depend on the association type. e.g. If I want to get the address I would do a join of the keys dbo_NUCAssociation. Key1 = dbo.NUCPROPERTY.PROPERTY_NO as one join then a second Join of dbo_NUCASSOCIATION.Key2 = dbo_NUCADDRESS.ADDRESS_CTR
nb: the table names start with dbo_NUC....

[orange]

Adish,

Research Normalization, and Primary Key, Foreign Key

Your table structure is not well designed, and it will cause you lots of frustration as you continue to develop your application.

Here is a tutorial you could work through in 45-60 minutes and you will learn about table design and relationships. You have to do the exercise, but what you learn can be used with any database.
Good luck.

[Adish]

The composite entity which is my dbo_NUCASSOCIATION is linked by key and and key 2 from property, name and address depending on the association type. Since the nature of the business is so complex, that's the actual tables in the system database.

[June7]

I agree with orange. The data structure is problematic. dboNumProperty is linked to two tables and this is possibly a circular relationship - review http://www.codeproject.com/Articles/...atabase-Design