Table Relationships

[edthened]

I am a complete beginner to Access 2007 so please bear with me because the answer is probably very simple to experienced programmers.

I have two tables - a Client Table (with their particulars) - and a Furniture Table (stating details of the article and the restoration carried out).

The Client Table has a Client ID column which is set as the key and AutoNumber - the Furniture Table has a Furniture ID set as key and AutoNumber and the next column is the Client ID set to Number.

The Client Table has a one-to-many relationship with the Furniture Table and that works fine.

However I cannot seem to make the reverse happen - what I want is the Furniture Table, with all furniture items shown for every client (shown in datasheet style), and beneath it the Client details. When I click the mouse on a row in the Furniture Table, I want the client, with his/her details to show below.

I should be very grateful if someone could show me how to do this.

Many thanks

[dandoescode]

If I'm understanding you correctly you want.

 SELECT FurnitureTable.Furniture ID, ClientTable.ClientID, ClientTable.ClientInfo FROM Furniture Table Left Join Client Table WHERE FurnitureTable.ClientId = ClientTable.ClientID;

[dandoescode]

But I could be completely misunderstanding you.

[dandoescode]

If you wanted to actually change the Furniture Table you would use an Append Query, but you may want to leave it as is.

[edthened]

Thank you very much for replying to my query. I have been trying to send you a screen shot, so that it makes my enquiry clearer as I think what you have suggested may work (thought I am not sure how to set it up). Do you know how I could send you one?
I really appreciate you spending valuable time helping me.

[dandoescode]

I don't know how to do that either. I guess the question is, do you want to alter the Furniture Table or create a separate table with the properties you want. Either one can be accomplished using a query the difference is whether the query will be a SELECT or UPDATE query. Let me know which it is and I can try to walk you through the process.

[edthened]

No - I do not need to alter any data, only a separate table showing one line of text with the relevant client details. I will try and find a way to show you a screen shot.
Thanks again.

[edthened]

Attachment 8196


I think I have suceeded!
Enclosed is a screen shot to make things clearer. In the top box I have a list of all furniture that I have worked on by many clients. I have clicked on Furniture ID 13 made by a carpenter named French of Wenvoe. The item belongs to Client ID 6 who's details I want to pop up in the lower box, as shown. If I click on another piece of furniture in the top box, I want the lower box to immediately change with the respective clients details.
Hope this helps.

[edthened]

Sorry Dandoescode, I did not mean I had suceeded in the programming, only in sending you a screenshot!!

[ssanfu]

There are many ways to accomplish what you want to do. Attached is *one* way - which might not be the best for you....

I do not have A2K10, so this is in A2K format.....

[edthened]

Thank you ssanfu. Yes that is nearly what I want to do. Is there any way I can limit the Furniture data to about 8 lines, with a scroll bar, so that the page fits nicely on the screen. I have about 400 data entries and the present list means that the client data is at the bottom of a long page!

Thanks again for your help - nearly there!

[ssanfu]

the present list means that the client data is at the bottom of a long page!

I'm not quite sure what you mean. In my example, the client data is displayed in the footer. The number of Furniture entries has no effect on where the client data is displayed. I guess you could increase the size of the footer and move the controls to the top of the footer if you want to display only 8 rows. But that means more scrolling.

You could move the controls from the footer to the header and make the footer size smaller (more records on the page).
Or there are other ways to create the display. Maybe using a list box to filter the form...???

[edthened]

It's OK Steve - I was being rather thick! I have understood now how you did it, and have corrected my program and am very pleased and grateful to you for sorting it out for me.

I should like to thank all those who helped me - this is a great site.

Thanks again Steve