Linking Tables 01-14-08

I need some advice on basic table set-up / linking

I have a student record table with a unique identifier (student ID)  
Contains student name and address
I have an enrollment record table with the same unique identifier (student 
ID)  Contains term and year attended
Now I would like to create a third table to record transcript orders.  The 
unique identifier would be student ID.  However, I would also like the 
student name to appear in this table (it is stored in the student record 
table).  How do I get the name to show up in this new table without 
duplicating the information?
0
Utf
1/14/2008 12:43:03 AM
access 16762 articles. 3 followers. Follow

3 Replies
736 Views

Similar Articles

[PageSpeed] 17

Create a query that contains all 3 tables.  Set your relationship to 
StudentID in all three tables.  Add those fields from each table that you 
require.

"pht1991" wrote:

> I need some advice on basic table set-up / linking
> 
> I have a student record table with a unique identifier (student ID)  
> Contains student name and address
> I have an enrollment record table with the same unique identifier (student 
> ID)  Contains term and year attended
> Now I would like to create a third table to record transcript orders.  The 
> unique identifier would be student ID.  However, I would also like the 
> student name to appear in this table (it is stored in the student record 
> table).  How do I get the name to show up in this new table without 
> duplicating the information?
0
Utf
1/14/2008 1:07:00 AM
On Sun, 13 Jan 2008 16:43:03 -0800, pht1991
<pht1991@discussions.microsoft.com> wrote:

>I need some advice on basic table set-up / linking
>
>I have a student record table with a unique identifier (student ID)  
>Contains student name and address
>I have an enrollment record table with the same unique identifier (student 
>ID)  Contains term and year attended
>Now I would like to create a third table to record transcript orders.  The 
>unique identifier would be student ID.  However, I would also like the 
>student name to appear in this table (it is stored in the student record 
>table).  How do I get the name to show up in this new table without 
>duplicating the information?

Don't.

Tables are for STORING data, not presenting it or editing it. The student name
should exist in the student record table *and noplace else*.

If you need to see the name in conjunction with data in the transcript order
table, use a Query joining the two tables on StudentID.

Also... it is probably not appropriate to use StudentID as the primary key.
Since the PK is, by definition, unique within the table, this would restrict
each student to only one transcript order. I would presume some students might
want to order two or more transcripts! StudentID should be a non-unique
foreign key (Long Integer if the student record StudentID is an Autonumber),
and the transcripts table should have its own, distinct primary key.

If you haven't already done so, the same principles would apply to the
enrollment table.

             John W. Vinson [MVP]
0
John
1/14/2008 1:12:25 AM
How do I "set a relationship"?  
Do I need to create a "name" field in my transcript table?  How do I get it 
to populate with the name from the student record table?


"Olduke" wrote:

> Create a query that contains all 3 tables.  Set your relationship to 
> StudentID in all three tables.  Add those fields from each table that you 
> require.
> 
> "pht1991" wrote:
> 
> > I need some advice on basic table set-up / linking
> > 
> > I have a student record table with a unique identifier (student ID)  
> > Contains student name and address
> > I have an enrollment record table with the same unique identifier (student 
> > ID)  Contains term and year attended
> > Now I would like to create a third table to record transcript orders.  The 
> > unique identifier would be student ID.  However, I would also like the 
> > student name to appear in this table (it is stored in the student record 
> > table).  How do I get the name to show up in this new table without 
> > duplicating the information?
0
Utf
1/14/2008 1:13:01 AM
Reply:

Similar Artilces:

Excel VBA...Linking to a vba picture holder
Help Please! I've got an excel prog that uses a hyperlink to display a .jpg from directory. It works fine but opens psp7 every time and then psp asks i i want to save it again. I've put a vba image box on the spreadsheet BUT have got totally los trying to link it to the hyperlink. a quick bit of info... Col A , rows 1 to 199 contain links to the folder with images in. Col B is data to be copied to an xml file which comes from Gmax (fltsim 2004 stuff). I've put the vba image box in col D and of course it shows a piccy.. If I alter the properties to the full path. Is there a...

Changing paragraph format in table cell
[This is a "sequel" to my previous question but is a separate problem.] When a table cell is selected, I need to do the equivalent of Paragraph > Format on the cell, but can't make it work. My best try follows. The loop needs to format the second cell in each row. With ActiveDocument.Tables(1) For i = 1 To .Rows.Count .Rows(i).Cells(2).Range.ParagraphFormat.FirstLineIndent = InchesToPoints(0) Next i End With Suggestions appreciated. -- Christopher Brewster Lockheed Martin, Eagan MN Message posted via OfficeKB.com http://w...

PIvot Table Sorting Differently in Excel 2003 than Earlier Versions
I am wondering if anyone else is having a similar problem or concern and can provide some documentation or insight into this problem. The problem occurs in Excel 2003 as the program seems to act differently under the same conditions than it does in earlier versions of Excel (97, 2000 and 2002). The problem is best illustrated by using two pivot tables, one based on the other, as in my example below. I have a pivot table with three items inserted into the row section of the layout and one item in the data section which is the subtotal of the line item. Let's say the three items are the...

System Restore not working 07-01-10
Using win XP Home SP3. All up to date. I down loaded a free evaluation copy of Google Sketchup pro 7 the other day. It is time limited (480 mins I think). I didn't think much of it so I tried to uninstall it via the usual Add/Remove Progs but that didn't work. Then I tried system restore and that failed as well. Could it be that the program is blocking uninstalation/restore because of the time limitation? i.e. it is trying to stop reinstallation and a further free 480 minutes ? Regards Bill "Bill P" <BillP@nospam.invalid> wrote in message news:...

Table Manners
In Outlook how can I implant a table in two or more colums, to obviate the necessity to scroll down if it is a lengthy table? ...

Broken Files in System Table
I am using Money 2004. When I try to balance my account, the beggining and ending balances are ok. Then when I reconcile against my bank statement on the left side is "0.00" which means the account has been reconciled. However, it does not finish. It has two buttons on the left:Next and Postpone. When I click on next it says that my beggining balance does not match the beggining balance in my register. I tried both salvage options (these had nothing in the log file. So I tried the salvage.exe file in the C directory. The log in the C:\salvage.exe had a warning that that...

Pivot Table and Zeros
How do I remove the Zeros from my Pivot Table -- mense2 ----------------------------------------------------------------------- mense23's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=717 View this thread: http://www.excelforum.com/showthread.php?threadid=26957 Hi you can set this in the pivot table option (show for blank cells) -- Regards Frank Kabel Frankfurt, Germany "mense23" <mense23.1e69sn@excelforum-nospam.com> schrieb im Newsbeitrag news:mense23.1e69sn@excelforum-nospam.com... > > How do I remove the Zeros from my Pivot Table? &...

Store one table elsewhere on Network
We have a Linux server with standalone MS Access copies on several PCs. I have created an application we use to calculate lease payments for cars, and each month certain parameters change, forcing me to update each PC seperatley. All the parameters that change are contained in one table. I would like to be able to somehow store that table on an extenal networkable HD and have the PC's refer to that drive for the parameters. Can this be done without splitting the database? Thanks in advance -- Gary in Michigan, USA Why? What reason do you have for wishing not to hav...

Link cell to axis maximum and mininum
Hi I have a big number of charts that I would like to change the X-axis maximun and minimum values in an easy way. It would be perfect just to asign e.g. the maximum value to a worksheet cell. Is it possible in any other way to change the chart axis max and min value for more than one chart at a time Best Regard Peter Hi have a look at the following site for an add-in that will achieve this http://www.tushar-mehta.com/excel/software/autochart/index.html -- Regards Frank Kabel Frankfurt, Germany Peter wrote: > Hi, > > I have a big number of charts that I would like to change the...

Transform matrix data ("Pivot Table like data") to simple dataset
With help of Pivot Table Wizard it is very easy to transform a table of data in the form of dataset into the matrix form of pivot table! Coying using "special-values" it is also easy to get the matrix form data independent of underlaying source data. What about inverse (backward) of such transformation? I know that some times (years) ago I saw a certain excel expert web page with the explanation of a pretty simple action (not using VBA and also without a lot of copying and inserting) you must perform to get a dataset from the matrix data. For the clearness, from the data in t...

MISSING TEXT 01-21-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Since i upgraded to snow leopard all of the text is missing from my emails. If i check my sent mail folder the email has the text. I have looked at all my settings and i can't find what i'm doing wrong. I have checked the colour just to make sure i'm not typing in white. any clues to what may be happening. many thanks <br> Guesty44 ...

linking docuements
q: how do I send a link to a document that's in a public folder using ? (not a link to the folder but to the document in the folder) NOTE: Exchange 5.5 ...

Linking /Copying Autofilter Arrows
I have a created a spreasheet with autofilters in a worksheet name "List", and created macros on a main menu worksheet named "Search". am wanting to either link or copy the dropdown arrows in the filte mode from the "List" worksheet, on to the "Search" worksheet, lisitn the selection criteria. Is this possible -- Message posted from http://www.ExcelForum.com You can't link to the AutoFilter arrows. You could use an Advanced Filter to create a list of the unique items in a column. There are instructions here: http://www.contextures.com/xlad...

Table Link
I am trying to write a report in Crystal that will show line item comments for returns. I am having trouble with linking SOP30300 with the comment table SOP10202. I can link the SOPNUMBE, but how do I get the correct line item comment? Thanks in advance for any help. KJ Kelly, You need to do a join on not only the SOPNUMBE, but also the LNITMSEQ (Line Item Sequence number.) These values are normally multiple of powers of two around 2^14 and higher, such as 16384, 49152, 32768 and the like. They are independent of the ITEMNMBR in the order, so that a given order can have multiple li...

Link Excel to Access
I am needing to populate an excel spreadsheet with data that is in an Access file. The problem is that the Access records are 779,834 and the Excel spreadsheet is 44,000. I need help in linking a VLOOKUP function to the Access file. Hi just as a suggestion: Why not do this linking in Access (link the Excel table in Access) and use SQL for this. Afterwards you may create a query in Access to return the values back to Excel >-----Original Message----- >I am needing to populate an excel spreadsheet with data >that is in an Access file. The problem is that the Access >recor...

Excel graph linked to PPT
I have a spreadsheet w/ multiple worksheets/tabs (3 for right now). I also have a PowerPoint file w/ the 3 slides (one for each worksheet/tab). On my 1st tab I have a small matrix that is basis for a graph. Both, matrix and graph have an active link to my 1st PPT slide. When I make a change in Excel and open PPT, the changes are updated in PPT... so far so good. Now, back in Excel, I copied the 1st worksheet twice and entered only different numbers into the matrix... which update my two Excel graphs properly... still, so far so good. In PPT, I added now 2 additional slides that need t...

Link a file to a Pivot Table in another file
I have a sumamry file for monthly reporting that is linked to four different files (each with a pivot table). With all the files open, I refresh the data of each pivot table, save them and voila! the summary file is updated and saved. **The issue I have is when I close all the files with pivot tables, the summary file shows me "#REF" in the cells with the links to teh tables. Not sure why, any ideas are greatly appreciated! ...

mailto link in HTML error
When I click on a mailto link in a webpage, the outlook message window that opens will not send. Any ideas? Getho ...

VC++2005 and extending COleDataSource and static link to MFC
Hello I have added a new class through VS2005's wizard, of base type COleDataSource. The program compiles and links fine if the settings were to dynamically link to MFC. Now if I set to statically link to MFC it fails on: IMPLEMENT_DYNAMIC(CMyOleDataSource, COleDataSource) 1>.\myoledatasource.cpp(11) : error C2039: 'classCOleDataSource' : is not a member of 'COleDataSource' 1> c:\program files\microsoft visual studio 8\vc\atlmfc\include\afxole.h(195) : see declaration of 'COleDataSource' 1>.\myoledatasource.cpp(11) : error C2065: 'classC...

show sereral columns of data field in pivot table
In pivot table, data column. I would like to show several columns of data. Please instruct me how to do it? This may help you...if not reply and we'll try something else http://office.microsoft.com/en-gb/assistance/ha010346321033.aspx "Linda" wrote: > In pivot table, data column. I would like to show several columns of data. > Please instruct me how to do it? To arrange the data fields horizontally, drag the Data field button onto the cell that contains the word Total, as shown here: http://www.contextures.com/xlPivot02.html Linda wrote: > In pivot table, dat...

Quarterly Data in Pivot Table
Hi Everyone, I am using Excel 2003. I just made my first pivot table last week. I have a chart and a sheet that I created from the wizard. I have grouped by years, quarters and months. On my sheet, I can see the years and months but not the data by quarter. I have noticed that I can double click on my year and it collapses down. I only have data from April thru Dec 2008, then Jan thru Dec 2009, and this year Jan thru March. I would like to know how to view the quarter data Thanks, Linda Hey Guys, Nevermind. I just dropped the month out of my grouping and ...

Multiple Child Links
Hi I have a main form which has 2 tables set as record set. There are 2 combo box, cboStoreNo, cboReferenceID and I would like the subform to show all the data based on which ever combo box has the data in it. Code has been created for the combobox before update to set the other combobox as null so only one combobox has data at one time. One solution I tried was to have a hide text box in the main form which works great but I cannot get any code to set the child link as it can be either StoreNo or ReferenceID depending on the combobox selected. Any thoughts would be appreciated. Thanks...

copy or change content in table style page setup
How do you modify, save and copy for Excel the content and margins in the table at "File, page setup, table style" ? You modify the values by typing in the fields. You can't export the configuration. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Poll: What version of O...

Linking Project Plans
I've read the help information on how to "Link selected fields dynamically between Project Plans" and was able to get it to work when updating tasks. However, is their a way to get the linked plans to also add new tasks that area entered? When I add a new task row, it is not updating on the "Master Plan" In article <8A7DECAE-6815-476E-BDDB-96129BBC12C7@microsoft.com>, Grhodes <Grhodes@discussions.microsoft.com> wrote: > I've read the help information on how to "Link selected fields dynamically > between Project Plans" and...

Windows Media Player no picture 08-02-10
Vista windows Media Player 11 has sound and visualization but no picture on some AVI files. One file that does not respond is Fill Handle.exe. AVI which is a tutorial for Windows Office 2007. ...