A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

One-to-Many relationship design suggestion



 
 
Thread Tools Display Modes
  #1  
Old February 8th, 2005, 02:27 AM
Shawn Fletcher SCC
external usenet poster
 
Posts: n/a
Default 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?
  #3  
Old February 8th, 2005, 03:05 AM
Shawn Fletcher SCC
external usenet poster
 
Posts: n/a
Default

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  
Old February 8th, 2005, 06:09 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 11:34 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.