Query based on joined tables

[beeorlando]

I have two tables:

Librarytbl
DeptBoxTbl

The library tables lists all the reports we have in our project library and the unique field is LibraryID.

The DeptBox table stores the box# for Archive Storage.

Many reports in the library table can have the same dept. Box #.

PROBLEM:

I'm trying to set up a query that I can use for my box label. The label lists the Box # and the contents of the box.

If I have Box # IM-0001 with 3 different reports (hence, 3 unique Library ID's), how can I set up the query to display the contents all together?

I'd be very grateful for help on this...I've been struggling with all day and I just can't figure it out!

Thanks,

Carla

 

[randysmid]

Carla,
I am not sure if I understand the problem. Can you give us a visual depiction of what you want? For instance, would the labels look like this:

-----------------------------------------------------
| Box IM-0001 | Box IM-0002 |
| Contents1 | Book ??? |
| Contents2 | Book ??? |
| Contents3 | Gone With The Wind |
| | |
If this is not correct, can you show us? I am thinking that sorting and grouping is the solution to the problem.

HTH,
Randy Smith
California Teachers Association

 

[beeorlando]

Randy,

Thanks for such a prompt reply! The label would look like this:

Box#: IM-0001

I4-0001 Wetland Resource Report 3 copies
I4-0003 Ownership Inventory Report 2 copies
I4-0021 Engineering Study 8 copies

------------------

When I do a query, it lists the Box # three times and the contents respectively. I can't use that for the label because a label is based on one record.

So, I need to figure out how to combine the contents for all the library reports that are in Box IM-0001.

Hope I explained it better!

Thanks.

 

[randysmid]

Hi,
Did you use the Label wizard to create the labels? Can you try an experiment? Just create a regular report, with the page size being the same as your label size. You can then set it to have two logical pages per physical page (this will get you the second column). Then you can use Sorting and Grouping to get all items for each box listed. You will have to play with the formatting a little bit, to make sure you get the proper margins (such as in the case of too many entries for a single box).

HTH,
Randy Smith
California Teachers Association