Please Help...... KRISH

[Guest]

Hi! to All
Happy New Year 2006.
I joined two tables StockIN and StockOUT - [storeid] - One to many
relationship. I want to know current stock. When I join in my query I am
getting current stock only if at least i issued an item once. In general it
is possible that i received some items in StockIN and not issued at all. So I
want to show them also as available in current stock. Kindly help how to
solve this problem.
Any help is greatly appreciable. Thanks in advance.
bye
KRISH

 

[Guest]

You need a Left Join. Right now you have an inner join where there must be a
matching record in each table for it to show up.

Open the query in design mode. Double click on the line between the two
tables. Yes this can be difficult. When you get it a dialog box will open.
Select the options that says something like "All the records in StockIN and
any matching records in StockOUT."

 

[Allen Browne]

Presumably you have a Product table, as well as the StockIN and StockOUT.

Double-click the line joining the Product table to the other one(s) in the
upper pane of the query design window. Access pops up a dialog offering 3
choices. Choose the one that says:
All records from Products, and any matches from ...

Technically this is called an outer join. Where there is no match in the
other table, the query will return a row with nulls in the other fields. If
you have criteria on the related table, you need to explicitly include the
Null possibility or you have still excluded those fields.

More info:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Tom Ellison]

Dear Krish:

I've read two other responses you received. I have a different opinion I'd
like to offer.

It would be my opinion that the wisest way to handle this is with a UNION.
Like Allen's recommendation, this will include all the rows in both sets.
That's a very important feature. It is possible to have stock OUT without
stock IN. This will happen when someone forgets to log a receipt. However,
it may not be necessary to include the Product table as Allen has
recommended. This will give you results even for items that have neither IN
nor OUT. Otherwise, his suggestion is very good. However, it will force
you to deal with NULLs which may not be a necessary complication.

Indeed, the UNION query gives you the same appearance you would have if you
had just had a single "transaction" table instead of separate IN and OUT
queries. IN is all the rows with a quantity > 0 and OUT is those < 0. This
approach gives you the simplest queries and the best performance, in my
humble opinion.

Tom Ellison