A challenge for a report guru

[Newtothegame]

Please help. I have a report that pulls information from various tables. I
have a 6 text boxes on the the report because the table has data that is
repetative in the field.

Ex. in a table called Offsetjoblist, I have the field [Job Number]. in
this field, in multiple rows the job number is repeated. In that same table
I also have a field called [PN]. This field is the part number. The
situation is that for each entry of the Job Number there is a different part
number associated with the record. on the report I pull the job number from
another table and then use the dlookup to reference the Job Number and Part
Number where the criteria is to match job numbers to pull up the part number.
In other words, the formula looks like this:

=DLookUp("[PN]","Offsetjoblist","[Job Number]=Reports![PROD REPORT]![JN]")

the problem is that the formula only gives me the first instance, so the six
text boxes all read the first instance. I need to have the first text box
read the first instance to give me the first part number associated with the
job number, the second text box to give me the second part number associated
with the job number and so on until I get to the 6th text box. The issue is
that I need to do this directly on the report without having to go into vb.
Is there any way the dlookup can be manipulated to give me the other
instances? and should the formula be different in the corresponding text
boxes to increment rows or something like that?

Thanks in advance for the help.

 

[donotahame]

Sorry friend but either this "A challenge for a report guru"
Or this "I have a 6 text boxes on the the report because the table
has data that is repetative in the field."
Not both of them in the same post.

People here are always so kind to help but I don't think they like the
word of "Guru" let alone "Challenge".

 

[Gina Whipp]

Not a guru but I'll give it a shot...

Do I understand that you have used 'Job Number' as the the FK? If yes, then
the only solution I can think of is to use a subreport that will show all
the PN's, unfortunately, this would limit the way the data can be displayed
but it would give you what you want.

 

[Larry Linson]

People here are always so kind to help but I
don't think they like the word of "Guru" let
alone "Challenge".

Very perceptive response.

Volunteers (not Microsoft employees) participate here to help others by
answering questions. They don't have to be flattered with appelations such
as "guru", nor do they appreciate someone thinking "If I _challenge_ them,
they'll be eager to expend time and effort answering my questions." Just to
add to this, almost never will the most capable responders bother to open a
post with a subject like "URGENT RESPONSE REQUIRED" without any summary of
the question.

All too often the only real "challenge" is in trying to figure out from
vague high-level description what the original poster has, and even the
question to which they need an answer.

Larry Linson
Microsoft Office Access MVP