Query help

[redeye]

Ok yet another newbie here.

I have the beginings of a database. 1 form named client_data (picture attached).
1 table called tblclient_data which holds infomation on a client such as address, company type, telephone numbers etc.
1 table called tblclient_contacts which holds contact details on employees of a company.

On both tables I have a particular field, "No" which is set as the Primary key (a text field as I want an abreviated company/ personal name).

On the client_data form I want a sub-form which contains contact information on employees from the company currently displayed in the main part of the form.

Fields in the table for the tblclient_contacts are:

No (Primary Key)
Salesperson Code
Name
Job Title
Phone No
Mobile No
Fax No
E-mail address

I have had a good read of most of the posts about relationships and understand the theory of one-to-one, one-to-many etc but I cannot get it to work in practice. I've downloaded the various examples but I must be having a bad day with brainpower. Any help/suggestions to get this to work (as It seems like a simply Query!) would be much appreciated. Thanks.

 

[Kraj]

First off, a golden rule of database design is your primary key should never have any meaning beyond a unique identifier. Create a new field in each table, call it 'TableName'ID. Set the data type to Autonumber. This will save you so many problems it's downright silly.

Once that is done, if I understand your question correctly, you should be able to create a query that simply selects the fields you want. Be sure to include the primary key from the main form in the query. Then use the toolbox to add a subform/subdatasheet to your tab. The wizard will kick in and ask you what you want to build the subform from. Select queries and pick your new query. The wizard should show a screen that lets you select what fields to link the forms on. It generally defaults to the correct field, so try leaving it to the default and see if it works. That should give you a subform with fields from the query based on the data in your main form.

 

[redeye]

Thanks Kraj, have made the changes to the primary key. Stll trying to get my head around how to actually create the query though. I've not done queries before

First off, a golden rule of database design is your primary key should never have any meaning beyond a unique identifier. Create a new field in each table, call it 'TableName'ID. Set the data type to Autonumber. This will save you so many problems it's downright silly.

Once that is done, if I understand your question correctly, you should be able to create a query that simply selects the fields you want. Be sure to include the primary key from the main form in the query. Then use the toolbox to add a subform/subdatasheet to your tab. The wizard will kick in and ask you what you want to build the subform from. Select queries and pick your new query. The wizard should show a screen that lets you select what fields to link the forms on. It generally defaults to the correct field, so try leaving it to the default and see if it works. That should give you a subform with fields from the query based on the data in your main form.

 

[Kraj]

You would probably be well served by getting a begginer's Access book from the library or bookstore. I'm sure there are tutorials online as well. In the meantime I'll do my best...

...I started to write about queries but come to think of it, from what you're describing I think you want to display all the fields in the tblclient_contacts. In that case, if you create the subform directly on the table you should be fine...as long as the ID from tblclient_data appears in tblclient_contacts. If it doesn't and you don't know why it should, let me know and I'll explain the details.

If using the unqueried table doesn't work, let me know what it doesn't do that you need it to do and we'll go from there.

 

[redeye]

Ok mate got it working did exactly what you said ref subform directly on the form etc. Thanks for your patience.. I'm off to get a book as well to take this project further.

You would probably be well served by getting a begginer's Access book from the library or bookstore. I'm sure there are tutorials online as well. In the meantime I'll do my best...

...I started to write about queries but come to think of it, from what you're describing I think you want to display all the fields in the tblclient_contacts. In that case, if you create the subform directly on the table you should be fine...as long as the ID from tblclient_data appears in tblclient_contacts. If it doesn't and you don't know why it should, let me know and I'll explain the details.

If using the unqueried table doesn't work, let me know what it doesn't do that you need it to do and we'll go from there.

 

[Kraj]

Awesome! Good to hear.