primary key v value conflict

[aaearhart]

Hello-

I have three points of data:

T-Trips:
T-Trips.TripID - auto number / primary key
T-Trips.Traveler - number (SELECT ContactID, FName&" "&LName AS FullName
FROM T-Contacts) where ContactID is autonumber/primary key
T-Trips.TripJob - number (SELECT JobID, JobName&"-"&JobNumber AS FullJob
FROM T-Jobs) where JobID is autonumber/primary key
T-Trips.Destination - text
T-Trips.StartDate - medium date
T-Trips.EndDate - medium date

Q_Trips:
Q_Trips.TripID
Q_Trips.Traveler
Q_Trips.TripJob
Q_Trips.Destination - text
Q_Trips.StartDate - medium date
Q_Trips.EndDate - medium date

R-UpcomingTrips:
TripID
Traveler
TripJob
Destination
StartDate
EndDate

okay

When i look at the data for T-Trips.TripJob, the values are (essentially)
"Project A," "Landscaping Project," etc., not "200554" or "200437". This is,
of course, how i want it.

The same goes for Q_Trips. I get the jobs' names, not their primary key ID#

However, once i move over to the report, i'm seeing the primary key number,
not "Landscaping Project." What's more confusing, is this is only on a few
records. Not accross the board for any one project.

So, i've tried going back and making the data source for the report like this:

SELECT ...(T-Trips data), T-Jobs.JobName, T-Jobs.JobNumber, ....

to get the actual job names. When i employ that method, my records display
in quadruplicate and throws off my report grouping. however, it still does
not give me "Landscaping Project" on my problem records.

any thoughts?

===================================================
full data here:

R-UpcomingTrips

SELECT [T-Trips].*, [T-Contacts].FName&" "&[T-Contacts].LName AS FullName
FROM [T-Contacts] INNER JOIN [T-Trips] ON [T-Contacts].ContactID =
[T-Trips].Traveler WHERE ([T-Trips].[EndDate] >=Date());

 

[Jeff Boyce]

When I see the same field names/definitions in multiple tables, and table
names that seem to contain differing values of a common theme, I suspect I'm
seeing ... a spreadsheet!

You may be able to get much better use out of Access' strengths and
functionality if you normalize your data before proceeding. For example, if
your three tables actually contain the exact same kinds of data, consider
creating a new table with the same fields as your three, plus one more.
That additional field will hold information about what the "type" is for the
rest of the data (e.g., "T", "Q", or "R").

Regards

Jeff Boyce
<Access MVP>