Query Problem

[zeeroone]

Hi guys,

I have a problem regarding the query calculation.

I have 3 tables:
-supplied inventory
-delivered item
-issued item

In each table their is Item Description and Quantity

how can i calculate the quantity of a specific item when delivered and issued.

Thanks in advance

 

[Alansidman]

I would do this by running an aggregate query on each of the tables to give you a total for each item. I would then join the three tables and in an expression add and subtract the appropriate fields.

My question to you is why do you have three tables for this when one would do the trick. In my mind

InventoryTable
ProductID (PK)
ProductDescription
Additions
Withdrawals

In a query you can then have one aggregate query and then a summary query.

Alan

 

[MarkK]

Typically in a database a record in a table should completely describe a single instance of an object. Looking at your design here you have an 'issued item' and a 'delived item', but they are both items that seem to differ only by status.
This status should then be a field in the Item table. Any description of the item is a field, so if the item is red, or 12 inches long, or 50 pounds or issued, these are not distinctions to make at the table level. We don't make a new table for red items, we create a color field.
And counting items in the same table is really easy...

dim count as long
count = DCount("*", "tItem", "Issued = True AND CustomerID = 12")

Cheers,
Mark

 

[zeeroone]

Thanks for the reply, actually i already made a query please see attached file.

may problem now is how to compute the Total, what i need is the incoming supplies - outgoing supplies.

Please help

Thanks in advance

 

[Alansidman]

Create a new field. For example

InventoryOnHand: [IncomingInventoryField] - [ShipmentsField]

Change the names to your field names. Because you used the same name for both fields make sure to include the table name, ie. [tablename].[fieldname] as your syntax.

 

[zeeroone]

I already did change and added the field, my next problem is the outgoing quantity. If its blank the Total Qty is also blank.

Please help

Merry x'mas to all

 

[Alansidman]

Yup. Access doesn't know how to handle empty or null records. So you have to tell it what to do if it encounters one. You need to employ the NZ function. Here is a quick lesson on it.

http://www.techonthenet.com/access/functions/advanced/nz.php

Alan