Where am I going wrong?

[Liz James]

Queries won’t work
Forms to display data won’t work
Table structure is:

tblTrackRecs has 55 fields.
JobNo pk
Adate
start
end
waypoint
ItemID fk
IdescID fk
AlignID fk
TypeID fk
SlopeID fk
WidthID fk
SurfID fk
SoilID fk
VegID fk
Etc for 55 fields

Other related tables are
tblItem
ItemID
Item

tblType
IdescID
Idesc

tblAlign
AlignID pk
Align

TblSlope etc for most of the 55 fields

The related tables have 2 fields, a pk field and a text field.
Instead of storing the text describing the Item, the tblTrackRecs stores the
ItemID, the same for IDescID, AlignID, SlopeID, TypeID etc.
The rules are that ItemID is a required field, whereas AlignID, SlopeID,
TypeID etc are not required.

If a run a query from the main table tblTrackRecs, which includes the
foreign key for ItemID, IDescID, AlignID, SlopeID, TypeID etc, the query
works, there are Nulls where some fields are blank. However the information
showing IdescID, AlignID, SlopeID, TypeID etc as a number is not what I need.
I want to see the actual text that corresponds to IdescID, AlignID, SlopeID,
TypeID etc

If I drag the text field from each of the related tables on to the query
grid the query won’t work.

What do I need to do?
When this query is working it will be the data for a form.
This form needs to have the text for Align, Slope, Type etc (7 fields)
concatenated into one text box, then a different lot of 6 fields concatenated
into one text box, and so on.

If I store the actual text for Align, Slope, Type etc in the main table, the
whole thing works, but I want to do it this way, so I am not repetitively
storing the same text many times in the main table.
What do I need to do?

 

[Albert D. Kallal]

For each "id" field that is a lookup to the other table, simply drop in that
additional table into the query builder. Now, draw a line from the main
table to the table with the text description ( of cause, you draw the join
line FROM the main table to the child table..and you draw from the correct
id field to the key id field in the lookup table).

You can add 2, 3, or even 10 tables if you want. Now, just simply drag and
drop in the descriptions fields you need from all these additional tables. I
do this all the time....

The trick here is to use left joins (if you don't..then you will only be
able to add ONE table).

To make a left join, you have to double click on the join line you drew, and
select the option that says "anything from the main table, and possibility
something in the child table).

These querys are even still updateable if the child tables "key" id is the
primary key..

 

[John Vinson]

Queries won’t work
Forms to display data won’t work
Table structure is:

tblTrackRecs has 55 fields.
JobNo pk
Adate
start
end
waypoint
ItemID fk
IdescID fk
AlignID fk
TypeID fk
SlopeID fk
WidthID fk
SurfID fk
SoilID fk
VegID fk
Etc for 55 fields

Albert's suggestion should work. If you have these defined as Lookup
fields, you're being deceived: it *appears*, looking at the table
datasheet, that you have the "slope" description from the Slope table,
but what's actually *in* the table is the numeric ID.

However, your table design may benefit from a couple of changes. If
all of these lookup tables have just one ID and one field, it may be
more efficient to do away with the numeric ID's altogether, and have
Text fields in tblTrackRecs containing the actual data. This will make
your queries simpler, and avoid the 32 indexes limit that you'll
otherwise encounter if you try to set relationships for all these
tables.

Better yet, perhaps, would be to more completely normalize the data.
It appears that you have 55 types of parameter about a site (waypoint,
track record, whatever you call it). Rather than encoding these
parameter types in fieldnames you might want to model this as a many
(sites) to many (parameters) relationship:

tblTrackRecs
JobNo pk
ADate
Start
End
Waypoint

tblParameters
ParameterID pk Autonumber
ParameterName <e.g. "Type", "Slope", "Width", ...
<perhaps fields Datatype, Units; some parameters may be Text, some
numeric, width might be in meters, slope in degrees or grads, etc.>

tblValues
JobNo pk << and fk to tblTrackRecs
ParameterID pk << and fk to tblParameters
NumValue
TextValue


John W. Vinson[MVP]

 

[Liz James]

Thankyou to both Albert and John. This database design is a tricky thing to
learn. All the suggestions are helpful for me. This database has become very
tricky since client decided users would enter pre-determined responses,
instead of inventing them at time of recording.