Relationships that work and don't work

[Jacson]

I have very little Access xp so bear with this vague description

I am creating a little database in Access (Office 2000 professional) to keep track of various locations and the equipment at each location

So I’ve created a central Location table (auto-numbered) with Location name, address, city, state, zip

Then I created several support tables including system, server, position, port, function, etc.. The support tables are auto-numbered, have just one name field and just lookup data. Port , for instance, has records auto-numbered 1 from 12 and port_names such as, Com1, Com2, Com3

Next I created linking tables between the Location table and the support tables. Such as, Loc_printers which contains a printer id, a location id, a printer function id, and a port id

I have defined the same relationships between the Location table, the linking tables and the support tables

Problem
When I open loc_printer in Datasheet View, a drop-down is presented for each data field populated with the appropriate data (location, function, port)

But for a similar table (for example loc_bc_scanner), the location field drop down is populated, but position and port are not They have only a default value 0 displayed

What is puzzling is why the relationships work in some cases but not in others
If I remove the relationships from those tables that are working, the ones that don’t work still don’t work

This newb thanks you in advance.

 

[Evi]

You are relying on the wizard. You don't need to and in fact, are better off
not doing so. One of the many problems with lookup fields in tables is that
you don't get to learn how to use the correct thing for data input - a form.
Bite the bullet and do it the hard way. Create a form based on your table.
In design view insert combo boxes to 'look up' the data from linking tables
and 'Store the data' in the appropriate ID field. If you are not sure how to
do this, just say and we'll walk you through it.

I don't have much idea what all that Port and server stuff is about so I
can't comment on the structure of the database but if you have an autonumber
field in the tables that have the lists of items joined to the number field
in the table(s) that put different lists of items together (like your
LocPrinter table) then it all looks correct.

BTW, whenever you create a number field in a table, Access 'helpfully'
decides that you need the Default value of 0 in that field so that if you
don't specify a number for that field it will automatically put in a 0. This
is less than helpful if the number is a link from another table so go
through your tables and delete these 0s from Default Value of the fields
that are really links.

If you have already put data in one field and then have 0s in another field,
you won't be able to join that field from another table 'Enforcing
Integrity' because you won't have a 0 field in that linking table so all
those records with a 0 in the field will disappear if you do that kind of
join. There are plenty of ways of adding data later, by creating a
different type of join that allows this.

In cases where I may or may not have data for a field I sometimes have a
'default value' in the lookup table eg 'Unknown' and I put the ID number for
that record as the Default value in the linked table.

Please write back if you need further clarification on anything.

Evi

I have very little Access xp so bear with this vague description.

I am creating a little database in Access (Office 2000 professional) to

keep track of various locations and the equipment at each location.

So I’ve created a central Location table (auto-numbered) with Location

name, address, city, state, zip.

Then I created several support tables including system, server, position,

port, function, etc.. The support tables are auto-numbered, have just one
name field and just lookup data. Port , for instance, has records
auto-numbered 1 from 12 and port_names such as, Com1, Com2, Com3.

Next I created linking tables between the Location table and the support

tables. Such as, Loc_printers which contains a printer id, a location id, a
printer function id, and a port id.

I have defined the same relationships between the Location table, the

linking tables and the support tables.

Problem:
When I open loc_printer in Datasheet View, a drop-down is presented for

each data field populated with the appropriate data (location, function,
port).

But for a similar table (for example loc_bc_scanner), the location field

drop down is populated, but position and port are not They have only a
default value 0 displayed.

What is puzzling is why the relationships work in some cases but not in others.
If I remove the relationships from those tables that are working, the ones

that don’t work still don’t work.

 

[Jacson]

Thanks for the ideas.
I'll continue to struggle through.

Merry Christmas