Linked fields don't show in queries

  • Thread starter Thread starter Evi
  • Start date Start date
E

Evi

My database show the vehicles assigned to staff members
The tables are

TblStaff
----------
StaffID
StaffLastName

TblVehicle
--------------
VehID
Registration
ModelID

TblModel
-------------
ModelID
Model

TblStaffVehicle
--------------------
SVID
StaffID
VehID

The first number in each table is the Autonumber Primary Key. The other ID
keys are linked to their respective tables.

I have already entered the data into TblStaff, TblVehicle and TblModel

I make a query with all the fields in TblStaffVehicle. I add
StaffLastName from TblStaff
Registration & ModelID from TblVehicle
Model from TblModel

If I type a VehID into the Query, the Vehicle Registration shows but the
Model does not.
If I close the query and then reopen it, the Model then appears.

What have I done wrong?
Evi
 
Hi Evi

Why do you have the vehicle model in a separate table? This item should be in
your tblVehicle table. You also need to relate tblstaff and tblvehicle.

Maurice
 
Model would contain eg Ford Sierra, Vauxhall Cavalier etc. There may be
several Ford Sierras and several Vauxhalls so that indicates a seperate
table for the different Models.

Your second point:You missed the end of my message in yours. (I've included
it all below). There is a relating table, TblStaffVehicles.

I just don't understand why it doesn't work as it should. I'm wondering if
something is broken in my Access (Access 97 by the way). I've run Repair of
course and even tried to re-install Access but to no avail. I've imported
the tables into a clean db but that didn't work.

Has anyone ever seen this before?
Evi




StCyrM said:
Hi Evi

Why do you have the vehicle model in a separate table? This item should be in
your tblVehicle table. You also need to relate tblstaff and tblvehicle.

Maurice
Click to expand...
My database show the vehicles assigned to staff members
The tables are

TblStaff
----------
StaffID
StaffLastName

TblVehicle
--------------
VehID
Registration
ModelID

TblModel
-------------
ModelID
Model

TblStaffVehicle
--------------------
SVID
StaffID
VehID

The first number in each table is the Autonumber Primary Key. The other ID
keys are linked to their respective tables.

I have already entered the data into TblStaff, TblVehicle and TblModel

I make a query with all the fields in TblStaffVehicle. I add
StaffLastName from TblStaff
Registration & ModelID from TblVehicle
Model from TblModel

If I type a VehID into the Query, the Vehicle Registration shows but the
Model does not.
If I close the query and then reopen it, the Model then appears.

What have I done wrong?
Evi
 
Well, I've found the cause of the problem; database corruption. I recreated
the database, table by table and added the data by pasting it into a
spreadsheet and importing and appending it. The database now works as it
should. Linked information appears immediately.

That was the wierdest case of corruption I've ever seen!
Evi
 
Back
Top