Access Setup to obtain desired results

[mslennb]

I am trying to create a Access database to generate reports of Library collection fees.
tbl_Library (libraryID - Text key field) has all the Library ID #, names, location;
tbl_Receipts (ReceiptID-Auto#-keyfield), LibraryID-text, collection date
tbl_ReceiptDetails (Number-key field) and ReceiptID, TransactionID, Amount (currency)
tbl_Subobj (subobjID-text key field), subobject name
The problem I am having is that each Library has its own unique Activity number for each Transaction;
Example
Activity 0001 = Library 1 = Fines (Transaction) Subobj F200
Activity 0040 = Library 1 = Collection (Trans) Subobj F203
Activity 0051 = Library 1 = Lost book (Trans) Subobj M453
Activity 0002 = Library 2 = Fines (Trans) Subobj F200
Activity 0041 = Library 2 = Collection (Trans) subobj F203

The subobject code are the same regardless of the Library, but the Activity Codes are different for each Library and Transaction.
I suspect that my tables/relationship/query are incorrect and therefore not generating the desired daily report of amount collected by activity and library.
Any help would be appreciated.

 

[willir]

You need to create a reference table that links the specific activity code for each library to your own codes...

tblLibrary
LibraryCode - primary key
LibraryName
Location

tblActivityCode
ActivityCode - primary key
ActivityName

tblActivityXRef
LibraryCode - foreigh key to tblLibrary
ActivityCode - foreign key to tblActivityCode
LibraryActivityCode

Primary key for this table will depend. You may be able to use...
LibraryCode + ActivityCode
or
LibraryCode + LibraryActivityCode
or
...use an autonumber

Richard