Query with relation for inventory

[rana1978]

Dear All….

I’m new in Access and I’ve got a lot of supports from the forum members.

I have a inventory data base, where I need a Daily Reports with the below column heading

Item Code,
Item Details,
Day Opening Balance,
Received of the Day,
Issuance of the Day,
Closing Balance

I have below 4 main tables in the database

itemlist
T_I_1 (for issuance)
T_R_1s (for receiving)
tblopening (a particular opening balance at the time of the data base creation)

From the above table I have created some Queries and from the Queries a Final queries (creating relation) has been developed for the daily transaction records. But I am not getting the accepted result. As I have not received any item in a particular Date while issuance was done. In that date I need to show the Received of the Day column Nill or Zero along with the issuance. But as receiving has not been done no data is been shown in the query.

How Can I show all Items and transaction of each column whether there is no transaction of a particular Item or column??

The DB is attached herewith. Could anyone work on the DB and update it as per my need.

Thanks in advance
Rana

 

[gemma-the-husky]

to get nils in, you need to something like this

a) have a query finding all the items you want to see

b) have a query finding the movements

now some items in a) will not be in b), which is your problem

so have a third query which includes both these queries, joined on the item code. Now right-click the join line, and you should see a box that lets you select

1 - only matching items
2. - everything from query a
3. - everything from query b

you want to select option 2 (a left join) - now you will see all the items, even if there is no movement.

------------
note that this type of join is what access does when you prepare an "unmatched query"

 

[rana1978]

Thank you very much... Now it is working as per my need

Cheers Rana

 

[rana1978]

More Help_ Query with relation for inventory

Dear ..

Now, though the all fields of the Query (see the Query Q4_01B_dsr in the attached DB) are shown, the closingstock: [bal]+[recvqty]-[dayissqty] is not giving any result. It is happening when there is no value in Received or Issued Field.

How can I show the resuld in the Closing stock field?

Please help me.

Rana

 

[gemma-the-husky]

if you have a blank, then you get a null, and nulls propagate through - eg null + anything is still null

so you need to use the nz function in case of a null - this should work

closingstock: nz([bal],0)+nz([recvqty],0)-nz([dayissqty] ,0)

 

[rana1978]

Thank you very much D... Now it is working properly