Database Design Question

[Boots6]

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!!

 

[dhookom]

How about providing some sample records and how you want these records displayed in your report?



Duane
Hook'D on Access
MS Access MVP

 

[Boots6]

MaterialTransferTable DetailTable
MaterialTransferID ProjectTo ProjectFrom InventoryItem Quantity MaterialTransferID
1 222 755 Hammer 100 1
2 333 755 Hammer 100 2
3 755 600 Hammer 200 3
4 222 333 Hammer 25 4
5 755 222 Saw 100 5
6 600 755 Saw 75 6
7 222 600 Saw 175 7


This is really basic of course, but you get the idea. There are many projects and one item can come in to a project from any other project. Also, when that item leaves that job, it will not necessarily go back to the project from which it came. Fields such as InventoryID, etc. are included in the DetailTable as well - this is just very basic. I would like my report to show:

Project #222
InventoryItem TotalTo TotalFrom Delta
Hammer 125 0 -125
Saw 175 100 -75

Project #333
InventoryItem TotalTo TotalFrom Delta
Hammer 100 25 -75

Project #600
InventoryItem TotalTo TotalFrom Delta
Hammer 0 200 200
Saw 75 175 100

Project #755
InventoryItem TotalTo TotalFrom Delta
Hammer 200 200 0
Saw 100 75 -25


The negative deltas show us how much of that material should be at that project site still. If we close a project out and that material is still a negative, we have lost it and it will be considered shrinkage. This helps us cost new materials to projects that have We should never have positives in the deltas so it would appear we stole some material Project #600 That won't be the case, but this is just for demonstration purposes.

I put spaces between the above information, but it looks like this forum automatically removes them. It should look like columns and rows. Column titles: InventoryItem, TotalTo, TotalFrom, and Delta. I don't know how to go about getting a url to attach a file. Let me know if there is a better way to show this. Thanks again.

 

[dhookom]

Consider using a union query like:

SELECT MaterialTransferID, ProjectTo as ProjectID, InventoryItem, "To" as FromTo, Quantity
FROM MaterialTransferTable
UNION ALL
SELECT MaterialTransferID, ProjectFrom, InventoryItem, "From", -Quantity
FROM MaterialTransferTable;

You can then use this union query as a source for a query that groups by ProjectID and InventoryItem.

Duane
Hook'D on Access
MS Access MVP

 

[Boots6]

Thanks for the suggestion!