How to calculate current balance on hand

[fazly lee]

Hi,

my details input as per below;
1) table 'Receiving' (Part#,Qty received,date received)
2) table 'Issuance' (Part#,Qty issued, date issued)



How to calculate current stock on-hand?

*previously this report are using ms excel table. i just tried to convert into access since i will have a better view, will a separate form to fill up, will have a report to pull in(hopefully... current on hand )

[June7]

You need a dataset that has every possible Part# (Parts table).

Do an aggregate query for each of Receiving and Issuance tables. Join those two queries to the Parts table.

[fazly lee]

Hi June7,

Refer picture attached. my database...

[June7]

Looks like you have the necessary datasets. Did you attempt queries as described?

[fazly lee]

take a look my 'relationship' and query. Correct?

[fazly lee]

above are my form 'receiving' and 'issuance'.

[June7]

The form images don't have relevance to the queries I suggested.

Is the diagram from the Relationship builder? I don't see an 'Issuance' table.

Just try to build the queries I suggested. If you don't know how, reference Access Help for guidelines on using the query designer.

[fazly lee]

i quit don't understand, but the original excel file is like above picture. both receiving (in) and issuance (out) are included into the same excel.

[orange]

What does the original excel file have to do with the Access database? I think you may have left out a few details in your post.

[fazly lee]

i just want to convert from updating in excel into access. since in access it will have a better and a nice view (form, report and also switchboard).
my excel file is still can work, already put some formula to auto calculate all 'in' and 'out'. at last, i also can get current balance on-hand.


confuse on;
- how to get current stock on-hand (don't know how to auto calculate using access. did i need to create 2 table receiving(in) and issuance(out)?

[June7]

One table? Your first post shows two tables - Issuance and Receiving.

Check out Inventory database template http://office.microsoft.com/en-us/te...001018458.aspx

[fazly lee]

ok.. i'll try create new database.