Database design question

Your transaction table should have a field for TransactionTypeID and for LocationID. If you want to maintain a history of locations, you could have a history table with Item, TrnsactionType and Location and EffectiveDate.

That's what I was thinking with the "Is the 'right' way to do it one field for the type of transaction/category of where it is, and then one field for the foreign key of that category?" part. So say assigned to a person is Transaction type 1. TransactionTypeID = 1 for that transaction, then the LocationID = (whatever the employees EmployeeID is). Then if stored in a location is transaction type 2, another transaction would be TransactionTypeID = 2 and LocationID = (whatever the StorageLocationID is). And any forms showing the transactions would just use VBA to determine which table to pull information from to display people and location names (as well as the other types)?

It seems to me that the different relationship types are distinct, and should be modelled by separate tables which resolve the many-to-many relationship types into two one-to-many relationship types in each case. So you'd have EquipmentPersonnel, EquipmentStrorageLocations, etc tables. Two things are crucial to the way in which the data is both input and presented for output, in a report say:

1. Firstly, each relationship should have columns to record the date range during which the relationship is active. This would be particularly important for the output mechanism, as I'll describe below.
2. Also important for the output mechanism is that the other attributes of each relationship type should be such that they can each be each be returned in a similar format when output as a single column.

For data entry there would be multiple subforms per item of equipment, each based on the table modelling that particular relationship type.

For output in a report or similar, to combine each of the relationship types per item of equipment the rows from the tables modelling the relationship types would be brought together into a single result set by means of UNION operations. This is where it is important that the columns in each table modelling a relationship type are such that each can be returned as a single column by the UNION operation. The date columns in each of the tables would be used to determine the order in which rows are returned. So one row might return the movement of an item to a storage location, and the next row its subsequent assignment to an employee.

I have done something similar in my Inventory demo database with the following query:

SELECT "Acquisition" AS MovementType, ProductID, Quantity
FROM StockAcquisitions
WHERE AcquisitionDate >=
NZ((SELECT MAX(StockTakeDate)
FROM StockTakes
WHERE StockTakes.ProductID = StockAcquisitions.ProductID))
UNION ALL
SELECT "Disposal", ProductID, Quantity*-1
FROM StockDisposals
WHERE DisposalDate >=
NZ((SELECT MAX(StockTakeDate)
FROM StockTakes
WHERE StockTakes.ProductID = StockDisposals.ProductID))
UNION ALL
SELECT "Stocktake", ProductID, Quantity
FROM StockTakes AS ST1
WHERE StockTakeDate=
(SELECT MAX(StockTakeDate)
FROM StockTakes AS ST2
WHERE ST2.ProductID = ST1.ProductID);
UNION ALL
SELECT "Sale", ProductID, Quantity*-1
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderNumber = OrderDetails.OrderNumber
WHERE OrderDate >=
NZ((SELECT MAX(StockTakeDate)
FROM StockTakes
WHERE StockTakes.ProductID = OrderDetails.ProductID));

Note that the ordering of the rows is done by means of the report's internal sorting and grouping mechanism, not by an ORDER BY clause in the query.