Relationships

[beeorlando]

I've been using Access for a few years and I'm pretty good but I'm lousy at relationships! Here's what I'm trying to do (I'll try to explain it...):

I set up a database to track our report library.
Each report has a unique ID#.
I have to pack up some reports for archive leaving some copies on the shelf and some in boxes.

For example, I leave 2 copies of Report #1 on the shelf and pack the rest in Box #1. So, the database record for Report #1 reflects that there are copies on Shelf #1 and in Box #1.

There may be other reports packed into Box #1.

I want to print a label for each Box that lists the contents.

My question is: If three different reports are in Box#1, how can I add the content info. together for the label?

It would help if Box #1 was in it's own table with a one-to-many relationship. But I've tried to set up and I must be doing it wrong.

This forum has always come through for me....I await it's help again.

Thanks!

 

[GHolden]

I think from what you say you have a number of copies of different reports in different locations.
The following assumes you have a unique id for each report (not for each copy of each report).

Therefore you would want something like

tblReport
ReportID Description etc
1 Sales
2 Marketing
3 Orders
4 Inventory

tblLocation
LocationID Description
1 Shelf1
2 Shelf2
3 Shelf3
4 Box1
5 Box2

Then join the two together with a ReportLocation table

ReportID LocationID Qty
1 1 2 '2 copies report 1 in location 1
1 4 2 '2 copies report 1 in location 4

This way you can always find what reports are where or what the contents are of a location.



There are two ways to write error-free programs; only the third one works.