Urgent help on relationships

[ann]

Hi

I am urgently trying to find an answer to my problem!
I have a table with pc names and they have a code number
unique.
I have a second table with printer names and again have a
unique number. I have created a third table with all the
code numbers both for pcs and printers and I want to be
able to go into this new table and relate so that if I
click on the little + sign I can see the pcs related This
works ok but everything is coming up relating to the pcs
and I cannot get the relationship to change half way down
to give me the printers information? What am I doing
wrong???

 

[nextu]

I don't have many time to understand very well your problem , but yuo can
try read this:

http://www.w3schools.com/sql/sql_join.asp

Bye

 

[John Vinson]

Hi

I am urgently trying to find an answer to my problem!
I have a table with pc names and they have a code number
unique.
I have a second table with printer names and again have a
unique number. I have created a third table with all the
code numbers both for pcs and printers and I want to be
able to go into this new table and relate so that if I
click on the little + sign I can see the pcs related This
works ok but everything is coming up relating to the pcs
and I cannot get the relationship to change half way down
to give me the printers information? What am I doing
wrong???

Two things:

- You're using table datasheets for data entry and routine use.
Despite Microsoft's efforts to encourage this pernicious practice,
that's not a good idea. You would be much better off using a Form
based on the PC table and a Subform based on the junction table.

- You're also apparently misunderstanding how many to many
relationships work. I presume (and I may be wrong here, so don't
hesitate to clarify!) that each PC is linked to a limited number of
printers, and vice versa. The "third table" should NOT then contain a
record for every possible PC/Printer pair, only for each pair where
the PC in fact uses that printer. The table structures should be (*
indicates the Primary Key):

PCS
*PCName <unique primary key>
<other information about this PC>

Printers
*PrinterID
Make
Model
Location
<other info about the printer itself>

PCPrinters
*PCName
*PrinterID
<info about how this PC uses this printer, e.g. a yes/no field for
default printer>

 

[Ann]

Hi John

Many thanks for your quick response.
Just to clarify:

Table 1 (PC List)
This has a the following

IAWS no
PC Name
User
Department
etc...

Table 2 (Printers List)
This has the following

IAWS no
Printer name
Model
Type
etc..

The people who have the pcs do not necessarily have a
printer.

What I did was create a make table query to add in all the
IAWS numbers. I now have a 3rd table called IAWS List
I was trying to get this table to link to the PC List and
also the PRinters list. They are 2 different tables with
different information and differnt IAWs nos.

I don't know if I am explaining myself very well but when
I set up the relationships I want to be able to click on
the IAWS no and see the details of whether it is from the
PC Table or the Printer Table???

Thanks a million again

Ann

-----Original Message-----

 

[John Vinson]

Just to clarify:

Table 1 (PC List)
This has a the following

IAWS no
PC Name
User
Department
etc...
ok...

Table 2 (Printers List)
This has the following

IAWS no
Printer name
Model
Type
etc..
ok...

The people who have the pcs do not necessarily have a
printer.

but some PC's have printers, and some (or all) printers have
associated PC's?

What I did was create a make table query to add in all the
IAWS numbers. I now have a 3rd table called IAWS List

WHY?

It doesn't do you ANYTHING that the PC List table doesn't already do.
What is the function of this redundant table?

I was trying to get this table to link to the PC List and
also the PRinters list. They are 2 different tables with
different information and differnt IAWs nos.

You need a "junction table" with two fields: PC_IAWS, linked to
Table1.IAWS; and Printer_IAWS, linked to Table2.IAWS. (suggestion -
DON'T use blanks in fieldnames).

This table should start out EMPTY. Do not fill it with anything.

Instead, create a Form based on Table1 and a subform based on this
table (I'll call it PCPrinters). Use a Combo Box based on Table2 to
store Table2's IAWS field into PCPrinters.Printer_IAWS; use Table1
IAWS as the Master Link Field and PCPrinters.PC_IAWS as the Child Link
Field. On this Form you can then assign printers (selected from the
Printer List) to each PC - zero, one, or several printers, as
appropriate.

I don't know if I am explaining myself very well but when
I set up the relationships I want to be able to click on
the IAWS no and see the details of whether it is from the
PC Table or the Printer Table???

I don't see ANY reason why you would need to do this, but if you
really do, then you'll have to create a query joining this new IAWS
list table to *BOTH* Table1 and Table2, using Left Outer Joins for
both. It's going to be snarky and as I say, probably not particularly
useful!

 

[Ann]

Dear John

Thanks a million.

You explained yourself very well. I honestly don't know
why the person wants to do this either but I will try what
you suggested and then they will probably see that it is
not very necessary or useful to do this.

Thanks again.

Ann

 

[John Vinson]

Dear John

Thanks a million.

You explained yourself very well. I honestly don't know
why the person wants to do this either but I will try what
you suggested and then they will probably see that it is
not very necessary or useful to do this.

One thing you *could* do if you want a unified IAWS list is a UNION
query:

SELECT PC.IAWS, "PC" AS Type, PCName AS DeviceName FROM PC
UNION ALL
SELECT Printers.IAWS, "Printer" AS Type, PrinterName FROM Printers
ORDER BY IAWS;