Procurement database

[Tom B]

Hi,

Complete beginner here, I'm afraid. What I'm trying to do is establish a simple (at least, I think it's pretty simple) procurement database. What I want to do is to be able to 'check in' equipment from the database into a series of discrete locations. In terms of reporting, I'd like the database to be able to give me a breakdown of equipment in each location, a total kit list, and a breakdown by supplier. This way I can make changes to price, location and specification, and it'll be instantly reflected in the PO I give to the supplier. I've been through this process before, using Excel, and it wasn't much fun.

So far I've got several tables. The main one is the equipment table which comprises the bulk of the database. This contains information about the equipment itself, the supplier, the unit cost, etc. After that, each location has its own table, and this contains quantity information and simple calculations to work out the value of the equipment in each location. Further tables create summaries of this information.

What I'm struggling with is adding the equipment into the location tables using relationships. I can make the location tables modify the equipment table, but not vice-versa.

Any ideas gratefully received. I suspect the main concept is flawed, and I'd appreciate any guidance.

Thanks in advance,

Tom

[Fenton]

As is usual when there are problems like this, it is likely because you are missing a dedicated table for the "transactions," a table with the EquipmentID and the LocationID, each as foreign keys, a date when the transaction occurred, a price for the transaction,* and any other data specific to this particular transaction (notes, etc.).

Oh, by the way, a separate "table" for each location, if that's what you're doing, is just plain bad relational design. All locations are in a Locations "reference" table.

*Actually there may need to be another table, for Equipment|Location. It sounds like you're saying that each unique Equipment-and-Location combo may have its own price, which could be entered here, then looked up; that could be a lot of records. If any give piece of equipment had one price, then it would just be in Equipment, and you wouldn't need this other "reference" table. We don't really know where the price is supposed to come from, so can't really tell you how to get it.