If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
One-to-Many relationship design suggestion
I have a table called Servers with a Primary key of Server_ID; a table called
Modems with a primary key of Modem_ID, and a table called Server_HDD with a primary key of Server_HDD_ID. Both the Modems and Server_HDD tables have a Number field called Server_ID. I then have a One-to-Many relationship from the Server.server_ID to modem.server_ID, and another from server.server_ID to Server_HDD.Server_ID. The problem is when I create a query. I can add one of the tables to the query, but if I add them both, the query is blank. Can you not have two tables share the same ID in one table? |
#2
|
|||
|
|||
On Mon, 7 Feb 2005 17:27:03 -0800, Shawn Fletcher SCC Shawn Fletcher
wrote: The problem is when I create a query. I can add one of the tables to the query, but if I add them both, the query is blank. Can you not have two tables share the same ID in one table? Certainly; but unless there are records for that ServerID in both linked tables you'll see no data. Please post the SQL view of your query. John W. Vinson[MVP] |
#3
|
|||
|
|||
Thanks for the quick response John!
That would explain why when I open the query it is empty. Do you have any suggestions for me to get around this? I mean, a Server can have multiple printers, hard drives, and modems...but it may not have one of each of these items, so I can't have it require that all of the tables have records in order to work. Here is the SQL View of the Query: SELECT Servers.ServerName, Servers.OS, Servers.[OS_Service Pack], Servers.Netbios_DomainName, Servers.DNS_DomainName, Servers.AD_DomainController, Servers.DNS, Servers.WINS, Servers.DHCP, Servers.IIS_WWW, Servers.IIS_FTP, Servers.IIS_SMTP, Servers.Hosts_Website, Servers.Hosts_OWA, Servers.Internal_IP, Servers.Internal_Subnet, Servers.Gateway, Servers.WINS_IP, Servers.Primary_DNS, Servers.Secondary_DNS, Servers.Brand_Server, Servers.Server_Series_Name, Servers.Server_MT, Servers.Server_SN, Servers.RAM_Server, Servers.RAID_Level, Servers.RAID_Controller_Brand, Servers.RAID_Controller_Model, Servers.Tape_Drive, Servers.Tape_Drive_Type, Servers.Tape_Drive_Model, Servers.Tape_Drive_Capacity, Servers.Tape_Drive_Location, Servers.[Remote Access], Servers.Exchange_Server, Servers.Org_Name, Servers.Site_Name, Servers.Exchange_Version, Servers.Exchange_SP, Servers.SQL_Server, Servers.SQL_Server_Version, Servers.SQL_SP, Servers.SQL_sa_password, Servers.Backup_Exec, Servers.Backup_Exec_Version, Servers.Pcanywhere, Servers.Pcanywhere_Version, Servers.SC_Web, Servers.SC_Web_Version, Servers.SC_Email, Servers.SC_Email_Version, Servers.Server_Warranty_Exp_Date, Servers.Server_Warranty_Resp_Type, Servers.Server_Notes, Servers.Member_Server, Servers.Member_Server_Local_Pass, Servers.Printers_ID, Servers.UPS_Brand, Servers.UPS_Model, Servers.Powerchute_Console, Servers.Powerchute_User, Servers.Powerchute_Pass, Servers.Server_InService, Servers.Server_Processor_Speed, Servers.Server_Processor_Type, Servers.Number_Proccessors, Servers.Rack_Mounted, Servers.Remote_Supervisor_Adapter, Servers.SAV_Server, Servers.SAV_Version, Servers.SMS_Server, Servers.SMS_Version, Servers.SUSorWUSServer, Servers.Modem_ID, Servers.Digiboard, Printers.ShareName, Printers.Printer_Manf, Printers.Printer_Model, Printers.Printer_SN, Printers.PrintServer_Manf, Printers.PrintServer_Model, Printers.PrintServer_IP, Printers.[500_Sheet_Tray], Printers.Duplexer, Printers.Envelope_Feeder, Printers.Printer_Notes, Printers.Output_Expander, Printers.High_Cap_Output, Printers.Finisher, Printers.[250_sheet_Tray], Printers.[2000_sheet_Tray], Printers.Printer_HDD, Printers.Printer_Warranty_Exp, Printers.Printer_InService, Server_HDD.* FROM (Servers INNER JOIN Printers ON Servers.Server_ID=Printers.Server_ID) INNER JOIN Server_HDD ON Servers.Server_ID=Server_HDD.Server_ID; "John Vinson" wrote: On Mon, 7 Feb 2005 17:27:03 -0800, Shawn Fletcher SCC Shawn Fletcher wrote: The problem is when I create a query. I can add one of the tables to the query, but if I add them both, the query is blank. Can you not have two tables share the same ID in one table? Certainly; but unless there are records for that ServerID in both linked tables you'll see no data. Please post the SQL view of your query. John W. Vinson[MVP] |
#4
|
|||
|
|||
On Mon, 7 Feb 2005 18:05:02 -0800, Shawn Fletcher SCC
wrote: FROM (Servers INNER JOIN Printers ON Servers.Server_ID=Printers.Server_ID) INNER JOIN Server_HDD ON Servers.Server_ID=Server_HDD.Server_ID; Change the INNER JOINS to LEFT JOIN and you'll see all the servers, whether thay have printers or HDD's or not. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table design help! | Niklas Östergren | General Discussion | 4 | November 5th, 2004 08:08 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Design Templates don't apply font sizes consistantly | Greg H | Powerpoint | 1 | September 15th, 2004 02:07 PM |
design master problem | J. Vermeer | General Discussion | 0 | September 8th, 2004 03:23 PM |
Table Design Suggestion | JH | Database Design | 8 | June 23rd, 2004 01:36 AM |