Access Report 1

[sturner333]

I am trying to do an complicated report and am looking for some ideas on how to do it. This hard to explain ( I think), but what I want to do is run a report based on

project category
project number
and all notes that where entered for those projects

The tricky part is if there is a project under a project category that has no note I would like to leave a "placeholder" or dummy entry for that project number along with the engineers name that should have made a note. I can do this in a query or I can use VBA, whatever gets the job done. All this information is available from the same table.

Maybe I am making this harder than it looks or it may be not practical.
Thanks for the help!

 

[dhookom]

Where are you storing "the engineers name that should have made a note"?

Duane
Hook'D on Access
MS Access MVP

 

[sturner333]

The project table that the rest of the data comes from also has 2 engineers listed.

 

[dhookom]

Then you create a query of all the tables using a JOIN to the notes table that includes all records from the project table. Rather than displaying the engineer from the notes table, use an expression like:
EngineerOfNote: Nz([NotesEngineerField],[ProjectEngineerField])

If you can't figure this out, come back with additional information.

Duane
Hook'D on Access
MS Access MVP

 

[sturner333]

Thanks for the response. I have a couple questions:

1. In the Join, choose ALL projects and notes that match?

2. This expression should be in the Field: section?

3. I know you don't know all my data but I am trying to match up you code with my data.
NotesEngineerField is the Engineer from my notes table Note.Engineer?
ProjectEngineerField is the engineer from my project table Projects.Engineer?
I don't know what EngineerOfNote refers to.

So as I building this expression here is what I think I understand minus what I don't:
XXXXXXXXX: Nz([Note.Engineer],[Project.Engineer])

 

[dhookom]

You can either create the new field/column in your query with an expression in the field like:
DefaultEngineer:Nz([Note.Engineer],[Project.Engineer])

You might also be able to use a text box in your report with a control source of:
=Nz([Note.Engineer],[Project.Engineer])

Duane
Hook'D on Access
MS Access MVP