Lookup Column to Report

  • Thread starter Thread starter loki
  • Start date Start date
L

loki

I've searched to see if anyone else has this problem but it doen't
seem so.

So the database keeps track of photos sent to be archived. There is a
lookup field in the main table that get photographers names from
another table. There are often more than one photographer which is
why I used that kind of list.

The problem is when I go to the report there is a different row for
each photographer and all of the other data is duplicated on each
row.

Does anyone know how to get the photographers to display together?
 
If I'm understanding your description, you have a table with a "lookup" data
type field. A scan through this newsgroup will reveal a strong consensus
AGAINST using lookup data types in table definitions. Among other issues,
this data type stores one value (the key of the record), while displaying
something else (the looked-up value). This leads to confusion.

We aren't there. We can't see your data. I don't know what you mean by "a
different row for each photographer and all of the other data is
duplicated..." Are you describing what happens in a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I've searched to see if anyone else has this problem but it doen't
seem so.

So the database keeps track of photos sent to be archived. There is a
lookup field in the main table that get photographers names from
another table. There are often more than one photographer which is
why I used that kind of list.

The problem is when I go to the report there is a different row for
each photographer and all of the other data is duplicated on each
row.

Does anyone know how to get the photographers to display together?
Click to expand...

You don't describe the structure of your tables so it's hard to be sure...
note that a Lookup Field is actually a meaningless number that only APPEARS to
contain a name. I'm guessing that you're also using A2007's abominable new
Multivalued Field to get multiple names... right?

Consider using the Report's Sorting and Grouping dialog to group by the photo
ID. Put the photo-specific information in the group Header (or Footer) and the
photographers' names in the detail section.
 
I have since read comments again the lookup column but it was a great
help in designing this system.

Okay so the better description would be; we have a main table named
photos. Each row is a batch of photos we got from a specific shop on
a specific day. So the row reads Unit, ID, Date, # submitted,
Downloaded by, photographers, Notes...and a few others not really
necessary to list. So there can only be one unit, id, date, #
submitted but there is usually more than one photographer. We need to
be able have everything the same in all entries so the important ones
are linked to lists: Unit, downloaded by, photographers.

The problem that happens is that in the report (arranged by date),
there is a different row for every photographer chosen. All of the
data corresponding is repeated along with the photographer. So some
entries are repeated even six times. I have hid the duplicates to
make it easier to read but the empty space remains the same as if the
data was there and won't shrink.
 
It's a common practice to base a report on a query. Is your report
'pointed' directly at the table, or at a query? If a query, what's the SQL?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have since read comments again the lookup column but it was a great
help in designing this system.

Okay so the better description would be; we have a main table named
photos. Each row is a batch of photos we got from a specific shop on
a specific day. So the row reads Unit, ID, Date, # submitted,
Downloaded by, photographers, Notes...and a few others not really
necessary to list. So there can only be one unit, id, date, #
submitted but there is usually more than one photographer. We need to
be able have everything the same in all entries so the important ones
are linked to lists: Unit, downloaded by, photographers.

The problem that happens is that in the report (arranged by date),
there is a different row for every photographer chosen. All of the
data corresponding is repeated along with the photographer. So some
entries are repeated even six times. I have hid the duplicates to
make it easier to read but the empty space remains the same as if the
data was there and won't shrink.
 
Back
Top