Database Design - Many to Many Issues

[rts]

Hello,

I have been struggling this for a while now so hopefully someone with a little more expertise can help me.

I've created an inventory database but I am having trouble getting the reports that I want out of my data. I have a Projects table with all pertinent info relating to each construction project my company is currently working on. I have an Inventory table with all pertinent inventory information. I have a MaterialTransfer table that has fields: MaterialTransferID, ProjectFrom, ProjectTo, and other pertinent date, time, and personnel information. I then have a Detail table that allows the user to enter the InventoryItem and Quantity. This has MaterialTransferID as a foreign key connecting the Detail table to the MaterialTransfer table.

Unlike examples I've seen where inventory transactions work as items coming in or going out, I need to show items moving between the different projects. I want to be able to create a report that is grouped by Project. I want it to show all the Inventory Items that have come into that Project and all of the Inventory Items that have come out of that project as sums. So the ProjectNumber will be the header and there will be a line for each inventory item (not all of the inventory items, only the inventory that has come in or out of that particular project). Then there will be a sum in, a sum out, and a delta for each line. I can't get the "sum in" and "sum out" to show up correctly on the same query. If I have ProjectIn and ProjectOut fields in the MaterialTransfer table, the query of course wants to look at the record related to that MaterialTransfer only. I want it to single out the InventoryItem and ProjectNumber together then show how much came in and how much went out of the project so we can see when we lose inventory on a certain Project.



Any suggestions on the best way to solve this problem would be greatly appreciated. I have a lot of many to many relationships going on and I've tried multiple ways to resolve. There are Projects and Inventory Items. I need to get a report out that will let me know at the end of the day, how much came in and out of each project. There are other reports necessary that need to show all of the information so the other tables and fields are necessary. I appreciate any insight.

Thanks!!

[orange]

Please post a jpg of your tables and relationships.

[rts]

Database tables and relationships attached. I'm afraid my the way I've linked my "MainCategory", "SubCategory", and "Inventory" tables may seem like an odd way to get to an InventoryID for an item. I needed to be able to have my form filter the results down as the user chooses Main Category, then Subcategory, then Description so that there is no way for him to mess up the data. I couldn't figure a better way to go about it. But the matter I'm working through now is what I've described above. I appreciate any help. Thanks!