Set report to show project name instead of its ID

I have a tblProject that contains Projects and its activities.

Since each project has more than one activity, I put the activities in
tblActivity, using ProjectID as a foreign key in tblActivity (ProjectID is
the primary key in tblProject).

I set tblActivity ProjectID field to show project name because it’s easier
for me to check if the activities where assigned to the correct project
reading the project name instead of its ID.

I did this on tblActivity datasheet view, selecting “Lookup� sheet and
creating a query on “Rowsource� field (query shows project name instead of
its ID).

Now I tried to make a report, grouped by project, showing each project name
and all its activities.

I set the report’s RecordSource to tblActivity.

I used the wizard to do the report, adding ProjectID field. But instead of
showing project name, it’s showing ProjectID (number).

If I was able to set ProjectID field to show project name in tblActivity, why
can’t I do the same when I use tblActivity’s ProjectID field in the report?

How do I correct this?

Thank you very much,
Antônio Machado.

--
Message posted via http://www.accessmonster.com

Base your report on a query that includes TblActivity and TblProject and
include the project name field.

BTW, it's a very bad idea to use the Lookup feature!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications





"AntonioMachado via AccessMonster.com" u35762@uwe wrote in message
news:755dda9754eeb@uwe...
I have a tblProject that contains Projects and its activities.

Since each project has more than one activity, I put the activities in
tblActivity, using ProjectID as a foreign key in tblActivity (ProjectID is
the primary key in tblProject).

I set tblActivity ProjectID field to show project name because it's easier
for me to check if the activities where assigned to the correct project
reading the project name instead of its ID.

I did this on tblActivity datasheet view, selecting "Lookup" sheet and
creating a query on "Rowsource" field (query shows project name instead of
its ID).

Now I tried to make a report, grouped by project, showing each project
name
and all its activities.

I set the report's RecordSource to tblActivity.

I used the wizard to do the report, adding ProjectID field. But instead of
showing project name, it's showing ProjectID (number).

If I was able to set ProjectID field to show project name in tblActivity,
why
can't I do the same when I use tblActivity's ProjectID field in the
report?

How do I correct this?

Thank you very much,
Antônio Machado.

--
Message posted via http://www.accessmonster.com

Using Lookup fields in tables is generally consider a bad idea. You should
simply add tblProject to your report's record source. Join the ProjectID
fields and make sure you add the project name field in the query output.

--
Duane Hookom
Microsoft Access MVP


"AntonioMachado via AccessMonster.com" wrote:

I have a tblProject that contains Projects and its activities.

Since each project has more than one activity, I put the activities in
tblActivity, using ProjectID as a foreign key in tblActivity (ProjectID is
the primary key in tblProject).

I set tblActivity ProjectID field to show project name because it’s easier
for me to check if the activities where assigned to the correct project
reading the project name instead of its ID.

I did this on tblActivity datasheet view, selecting “Lookup� sheet and
creating a query on “Rowsource� field (query shows project name instead of
its ID).

Now I tried to make a report, grouped by project, showing each project name
and all its activities.

I set the report’s RecordSource to tblActivity.

I used the wizard to do the report, adding ProjectID field. But instead of
showing project name, it’s showing ProjectID (number).

If I was able to set ProjectID field to show project name in tblActivity, why
can’t I do the same when I use tblActivity’s ProjectID field in the report?

How do I correct this?

Thank you very much,
Antônio Machado.

--
Message posted via http://www.accessmonster.com

AntonioMachado via AccessMonster.com wrote:

I have a tblProject that contains Projects and its activities.

Since each project has more than one activity, I put the activities in
tblActivity, using ProjectID as a foreign key in tblActivity (ProjectID is
the primary key in tblProject).

I set tblActivity ProjectID field to show project name because it’s easier
for me to check if the activities where assigned to the correct project
reading the project name instead of its ID.

I did this on tblActivity datasheet view, selecting “Lookup” sheet and
creating a query on “Rowsource” field (query shows project name instead of
its ID).

Now I tried to make a report, grouped by project, showing each project name
and all its activities.

I set the report’s RecordSource to tblActivity.

I used the wizard to do the report, adding ProjectID field. But instead of
showing project name, it’s showing ProjectID (number).

If I was able to set ProjectID field to show project name in tblActivity, why
can’t I do the same when I use tblActivity’s ProjectID field in the report?


You are tying yourself in knots using those bleep lookup
fields where what you see is NOT what you get. Change it
back to a text box and either learn with the ID number or
create a query that Joins the two tables to get the name.

You will need that query anyway, because using it as the
report's Record Source is the solution to your report
problem.

--
Marsh
MVP [MS Access]