 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / December 2005Displaying fields from one table in another table
I haven't used Access for a few years and I need some help. I have designed two tables: (1) Guatemala destinations and (2) Guatemala database. I need to take text from fields in Table (1) and put the text into corresponding fields in Table (2). Table (2) has a form. In design view for this form: For the list box (Destination), in the Properties, I entered: Row Source Type: Table/Query Row Source: SELECT [Guatemala destinations].[Record key], [Guatemala destinations].Destination FROM [Guatemala destinations]; This enables me to select the record in Table (1) that I require and get the text from the Field (Destination) and put it into the Field (Destination) in Table (2). So far so good, but I need to get text from other fields in the same record. E.G. text from the Field (Destination type) in Table (1) and insert it into the Field (Destination type) in Table (2). I can't work out what clause to add to accomplish this or am I barking up the wrong tree. Thanks in advance >I haven't used Access for a few years and I need some help. > [quoted text clipped - 3 lines] >I need to take text from fields in Table (1) and put the text into >corresponding fields in Table (2). No. You almost certainly do NOT. You're using a relational database. Use it relationally! This means using the "Grandmother's Pantry Principle": "A place - ONE place! - for everything, everything in its place". Text from the Destinations table should be stored ONLY in the Destinations table. If you need to see it in conjnction with fields in Table2 (I don't know what that table is), then you can use a Form with a Subform, or a Query joining the two tables, or a Combo Box, or a variety of other techniques to *see* the information together on a Form or on a Report; but it is neither necessary nor appropriate to store that data redundantly. John W. Vinson[MVP] Thank you. I get the point about not storing data twice. I need to attach a record to every record in Table (2) by selecting the required(Destination) field from Table (1) and then display that Table (1) record in the Table (2) form. I just don't need to *see* the information, I also need to be able to search for records in Table (2) based on the information in the attached Table (1) record. Is all this best done by using a subform? > >I haven't used Access for a few years and I need some help. > > [quoted text clipped - 19 lines] > > John W. Vinson[MVP] I personally feel that you would make your problem clearer, if you described it in ordinary natural language - without using technical terms like table, record, field & so on. Your use of those technical terms is somewhat confusing, & does not serve to clarify what you are trying to do. Tell us what you want to achieve from an "end user" viewpoint, then we can suggest a way to proceed. HTH, TC Thanks for the patience. It’s rather complicated. Hopefully this is a little clearer. I will use New York as my example. I have a table that contains travel information. Every record in that table has to be attached to a Destination (e.g. information about the Surrey Hotel has to be attached to the Upper East Side). The Destination table contains records that pinpoint each destination’s location (e.g., the record for Upper East Side will contain info such as (a) New York (b) New York State (c) New England (d) The North East. We will search for Information records using the fields of the attached Destination records. I need to: (1) attach a Destination record to each Information record by selecting the destination from a list (e.g., attach the Upper East Side record to the Surrey Hotel record). (2) view the other fields of the Destination record in the Information record form (New York, New York State, New England, The North East, etc.). In my question, I described how I achieved #1. What’s next? If I put a sub-form into the Information form, will that automatically display the attached Destination record? > I personally feel that you would make your problem clearer, if you > described it in ordinary natural language - without using technical [quoted text clipped - 5 lines] > HTH, > TC The instant you say: "I have some tables that ...", I have to respond: "What is the primary key of each table?" You can't discuss tables, unless you state their primary keys. But I suspect you mightn't be ready for that! That's why I suggested that you avoid discussing tables at all, initially. If you're determined to talk about tables, that's fine - but you have to state their primary keys, otherwise the discussion will get nowhere quickly. If you don't know what a primay key is, you have to do one of two things: (a) research that topic until you understand it, or (b) /forget/ about tables, records & fields, and describe your needs in normal lanaguage - as I suggested before! Then we can help you take it from there. HTH, TC Both tables are autonumbered. The database is for a Website. I've designed the tables and the forms and individually they do everything I want. It's just that I've never joined tables before. I can't fully describe what I'm doing because it's very large and multifaceted. Access help is OK if you know what you're doing, but it's fragmented and the tutorials only demonstrate the basics. I'm quite happy to read up on the matter and solve the problem myself, but I'm far from civilization. So any suggestions for sources on the Net would be welcome (if it's not free, I can use credit card and download). Thanks > The instant you say: "I have some tables that ...", I have to respond: > "What is the primary key of each table?" [quoted text clipped - 16 lines] > HTH, > TC - If you want to learn more about database table design, the magic phrase is "database normalization". Google for that term, you'll find lots of hits. - If you want me to comment on your table structure, you'll have to describe each table explicitly. Your description so far, is not nearly enough. Do it like this (for each table): Identify the primary key field(s) clearly. tblCustomer CustID < primary key CustName (other fields describing the customer) HTH, TC > Both tables are autonumbered. > [quoted text clipped - 6 lines] > civilization. So any suggestions for sources on the Net would be welcome (if > it's not free, I can use credit card and download). My free advice is to spend a few hours using and then looking at the forms, modules, and reports in the Northwind.mdb file that comes with every install of Access. While it is much maligned as an actual useable application it has examples of how to do nearly every thing you would need to do in the UI to a relational database. When you're done doing that do a search for Solutions.mdb and study what it has to offer. It may be on your system already. It ships with some versions of Access. If it's not on your system, search msdn for it. >Thank you. >I get the point about not storing data twice. [quoted text clipped - 8 lines] > >Is all this best done by using a subform? As suggested elsethread, some study of the principles of relational database design would be useful. One good list of resources is at Jeff Conrad's website: http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html The Database Design 101 list is particularly useful. The way to do this STARTS with the Tables. Forms are just tools to manage data in tables; don't start worrying about Forms until you have your table structure right. It sounds like you have a one-to-many relationship from Destinations to your Table2. The way you implement this is to have a Primary Key (which need not be an Autonumber, though that is often convenient) in Table1, and a "Foreign Key" - a Long Integer if the primary key is an autonumber, a field of the same datatype and size otherwise - in the second table. This should be the ONLY field from Table1 that you store in Table2. Typically, one can create a Form based on the "one" side table, with a Subform based on the "many" table, using the Primary Key as the master link field and the matching foreign key as the Child Link Field. This will let you display the Destination record on the main form, and all of the matching related records on the subform. Searching for a Destination on the mainform will automatically bring up the related information. John W. Vinson[MVP] Your last paragraph gets to the heart of my problem. I think this will solve my problem. I'm looking at the links provided by you and TC. I appreciate the help. Merry Christmas. Is this an automatic udpate you want as records are being entered? Have you tried creating the relationship betweent he two tables? You would need to enforce relational integrity and cascade updates. Let me know if this works. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.