Multiple query report

[Peps]

I’m presently going through a brain storm and would appciate some Ideas. Let’s hope that I can clearly explain myself..
I’m working on a small DB that handles the in/out of stock for which I have two Tables: [Entry] & [Dept]
The [Entry] table basically has the same fields as the [Dept] table. The prime fields for both tables are: [CustomerCode], [Doc_No], [Pckgs],[Weight] . Let's imagine that we receive a box of parts from our customer that contains 7 items with, we therefore have 3 entries for each item

CustomerCode Doc_No Pckgs Weight
112211 1 1 10
112211 2 5 13
112211 3 1 15
-----------------------------------------------
Total 7 38

Lets imagine that we have to take out of stock the second item in the list, I now need to produce a report that can give us the results of all the entries and items taken out of stock but we must keep the groups together and show a complete relation.

The report should show the data more or less as below:

CustomerCode 112211 Doc 1 - 1 Pckg 10Kgs
Items Out
Total 1 Pckg 10Kgs

CustomerCode 112211 Doc 2 - 5 Pckg 13Kgs
Items Out - 2 Pckg 3 Kgs
Total 3 Pckg 10Kgs

CustomerCode 112211 Doc 3 - 1 Pckg 15Kgs
Items Out
Total 1 Pckg 15Kgs


Any ideas/suggestions would be appreciated.
Thanks,

 

[GingerR]

Where are you recording the items "taken out of stock"? And what is the table structure for that?

Do you have a unique ID for each item (i.e. ItemID/Autonumber)? If so that is in the "taken out of stock table" too? Or is your "unique ID" a combination of the CustomerCode and the Doc_No? If so, when you want to take something out of stock, how do you easily know which "CustomerCode" to look up? Is there some other part number or item number of some kind that you aren't telling us about?

So you have your current table, I assume data is entered in off of a packing list or something right?

TableName: Stock
Field: ItemID (number)
Field: PackageCount(number)
Field: TotalWeight(number)

TableName: StockUsage
Field: ItemID (number)
Field: PackageCount(number)

So you have a form where you type in the packing lists when things arrive. It's based on the first table.

Then later you take something out: you have another form based on the second table where you enter the ItemID (or part number or however you are doing it) and the # of packages taken.

That's all of your data.

As for displaying results, you can write a query; after that, much of the info can be made in calculated controls on the report. So, using the tables I described above (to test this, just make two tables like I did above and follow the rest of this:

Make a query with this:

SELECT Stock.ItemID, Sum(Stock.PackageCount) AS PackagesDelivered, Sum(Stock.TotalWeight) AS TotalWeight, IIf([StockUsage]!ITemID Is Null,0,[StockUsage]!PackageCount) AS PackagesTaken
FROM Stock LEFT JOIN StockUsage ON Stock.ItemID = StockUsage.ItemID
GROUP BY Stock.ItemID, IIf([StockUsage]!ITemID Is Null,0,[StockUsage]!PackageCount);


Use this query in the recordsource of the report. Then you can add another calculated control on the report, and put in this formula to calc the weight per package:

=(Int((([TotalWeight]/[PackagesDelivered])+0.05)*10))/10

Name this control i.e. WeightPerPackage; then you can use it in other calculated controls to get

=[PackagesTaken]*[WeightPerPackage]

Ok, so, when I put in some data, I made multiple deliveries of the same "ITemID", so the report came out kinda goofy (mult rows for the same ItemID), but I think you can get the gist of what I'm talking about here. Try it out and let us know how you do.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Peps]

Thanks GingerR,

First of all, I thought I could make things simple by changing the names of some fields in my first post, while the idea is still the same, the field names change. I can post you a query SQL but it’s a bit long.. The biggest problem that I have is expressing myself without having to write a 20 foot long post.
I have nearly solved the problem by placing a sub report inside the main report but now I’m faced with another small (maybe a big) problem. To be honest I may have to start a new thread because "my small" problem has completely changed.

I'm going to have a play around for a while before I write a new post with my further challenges.

Thanks for your help,