Table ID is showing not value of field

[lzook88]

I have created a report based on a query that combines three tables.

Explorer Posts: (1)
PostID (PK)
PostNumber
PostAgency
PostAddress
PostCity
ETC

Explorer Teams: (2)
TeamID(PK)


CTeamNumber
PostNum (FK)
PostTeamNumber

Events: (3)
EventID (PK)
EventName
TeamNumber_FK (FK)
Score

SQL Script:

SELECT [Explorer Posts].PostID, [Explorer Posts].PostNumber, [Explorer  Posts].PostAgency, [Explorer Teams].PostTeamNum, [Explorer  Teams].CTeamNumber, Events.EventName, Events.Score
FROM ([Explorer Posts] INNER JOIN [Explorer Teams] ON [Explorer  Posts].[PostID] = [Explorer Teams].[PostNumber]) INNER JOIN Events ON  [Explorer Teams].[TeamID] = Events.[TeamNum_FK]
ORDER BY Events.EventName;

When running the query it displays the information I want. When I go to put it in a report. It changes the EventName to its EventID and not the actual name of the event.. Any help would be nice..

Thanks all

[orange]

Do you use Lookups at the table field level?

[lzook88]

Yes I do. See attached database.

Attachment 22024

[orange]

Using Lookups at the table field level is one of the biggest sources of errors in forums. Using lookups on forms by means of defined lookup tables and joins is a proven method.
This article is often referenced.

[lzook88]

I am confused. My knowledge of access is quite limited. I know how to do a lookup on the form directly using the unbound forms and such. However I am not sure how this is an issue with the report I am trying to run?

Thanks again.

[orange]

It may not be an issue with your report. But although you may be seeing a text value when you display a field, it (Access) may be getting that value from a different table based on a numeric lookup in your table. All I was suggesting is that lookups at table field level are major source of issues; and you may be getting a (numeric) field without its "auto lookup".

The recordsource for your report is "Event Score Query"

and the sql for that query is

SELECT [Explorer Posts].PostID, [Explorer Posts].PostNumber, [Explorer Posts].PostAgency, [Explorer Teams].PostTeamNum, [Explorer Teams].CTeamNumber, Events.EventName, Events.Score
FROM ([Explorer Posts] INNER JOIN [Explorer Teams] ON [Explorer Posts].[PostID] = [Explorer Teams].[PostNumber]) INNER JOIN Events ON [Explorer Teams].[TeamID] = Events.[TeamNum_FK]
ORDER BY Events.EventName;

which includes Events.EventName -and you are expecting a Name --
but Events.EventName is a Number datatype in Table Events.

You really need EventNames.EventName which is the text field you are expecting.

See attached jpgs - I modified your report design (on my version) and show the output.

[lzook88]

Disregard my last post. on here. I read your post wrong. and now have fixed it and got it to work.

[June7]

I agree with orange. I NEVER set lookups in tables because I want to see the actual value stored in field, not the lookup alias.

Your options:

1. include EventNames table in the report RecordSource so the event names will be available, bind textbox to the EventName field from EventNames table

2. change EventName textbox into a combobox - just as you have for PostTeamNum (which could really be a textbox)

[orange]

I changed the Event Score Query to this

SELECT [Explorer Posts].PostID
    ,[Explorer Posts].PostNumber
    ,[Explorer Posts].PostAgency
    ,[Explorer Teams].PostTeamNum
    ,[Explorer Teams].CTeamNumber
    ,EventNames.EventName
    ,Events.Score
FROM EventNames
INNER JOIN (
        (
        [Explorer Posts] INNER JOIN [Explorer Teams] ON 
        [Explorer Posts].[PostID] = [Explorer Teams].[PostNumber]
        ) INNER JOIN Events ON [Explorer Teams].[TeamID] = Events.[TeamNum_FK]
           ) ON EventNames.EventNameID = Events.EventName
ORDER BY EventNames.EventName;

You could test this by renaming your current Event Score Query to say XXEvent Score Query, then save the code above as Event Score Query. You may need to tweak more but that's basically what I did.