HOW TO GET AN ITEM's BALANCE REPORT

[Shafique]

HELLO EVERYONE.
I am working to develop an INVENTORY database. In my database i created an ITEM IN Table where i stored the IN Items using cascading combo boxes. I define my one Item with Three criteria eg. ITEM NAME, ITEM TYPE, ITEM SIZE (Untitled1).

i performed same sequence to store data in my ITEM OUT Table. (Untitled2)
I have lot of Items with same name but differ in types and also in its sizes eg. PIPE(item name), Plastic(item type), 3"(Item size)
Here I want to create a report that first SUM Defined Item's IN quantity and then subtract same Defined Item's OUT quantity to check an ITEM's Balance within a date range.
How its possible.

 

[xenou]

Hi,
in general this is a query:

select sum(T.Total) 
from
(
select sum(IN) as Total from table where item = x  where TransDate >= y and TransDate <= z
union all
select (sum(OUT) * -1) from table where item = x  where TransDate >= y and TransDate <= z
) T

 

[Shafique]

Impressive.

So please tell the way how can i run this Query in my Database (Microsoft Access)

 

[xenou]

Hi,
In my humble opinion creating queries in MS Access that is not really something to be described in a board post as it has been covered in many online tutorials and videos that you can search for with Google (or your favorite search engine) and at YouTube and so forth.

This looks like a good one to me (and it happened to be the first hit when I searched for "creating queries in Access DB" with DuckDuckGo:

How to Create a Query in Access

database.guide

Note that my query as written above assumes modifications will need to be made appropriately for actual column names, table names, and some proper basic syntax for sql queries.
In other words, It won't work "as-is".

 

[welshgasman]

You should NOT have a table for in and one for out.
You should have one table and a field to determine in or out, or outs as a negative value?

Then you can just sum for the balance?

A pipe 3" should be a different item to a pipe 6"?

 

[Shafique]

You should NOT have a table for in and one for out.
You should have one table and a field to determine in or out, or outs as a negative value?

Then you can just sum for the balance?

A pipe 3" should be a different item to a pipe 6"?

hello.
you said right.
But i do a lot of work.

 

[welshgasman]

hello.
you said right.
But i do a lot of work.

Possibly use a Union query then? to get all the data in one place as it should be, then another query to sum as it should have been done in the first place.
This issue is going to haunt you all the time now.?

 

[welshgasman]

FWIW, it should not be too hard to fix.?
Make the out table values negative and continue to do so when new records created.
Append Out to the In table.

The combos will have criteria of > 0 and < 0, so they are easy to fix
Amend any other code to suit the new system.

You are effectively building a house on faulty foundations? and will always be creating workarounds.?

Up to you. I know it is not nice when you hear the structure is not correct and it will take time to repair, but time spent now will save you a lot of time further down the road.?

I had a DB that does the same sort of thing, money comes in and money goes out. A simple Sum() in one simple query gets me their balance.

 

[Shafique]

FWIW, it should not be too hard to fix.?
Make the out table values negative and continue to do so when new records created.
Append Out to the In table.

The combos will have criteria of > 0 and < 0, so they are easy to fix
Amend any other code to suit the new system.

You are effectively building a house on faulty foundations? and will always be creating workarounds.?

Up to you. I know it is not nice when you hear the structure is not correct and it will take time to repair, but time spent now will save you a lot of time further down the road.?

I had a DB that does the same sort of thing, money comes in and money goes out. A simple Sum() in one simple query gets me their balance.

 

[Shafique]