inventory mgmt

[thunderjas]

Hi all , i think i am at the right place need help for designing a inventory management for defence depot.
i am new to access and ill just list down the reqmt
1. mine is a supply depot required to just issue items( dry rations ( categoried in basic and neccessary group)
2. we receive these items from either mother depot or sometimes local purchase it .
3. now for the consumers or we call them as army units they are 2 types one is on peace accounting and second is field accounting . difference being the peace units tell us how much qty of items hey want ( for eg they tell us 1000kgs of rice 1000kgs of flour etc ) were as field units tell us thier strength of people based on which we calculate how much hey are auth ( for eg they give us str of 100 men and auth scale of rice is 200gms so this is how we do it )
4. i have already made tables of items , item category , army units , unit cat , unit demand , issue transaction etc i am able to do it for peace units .


5. for field units i want when i select any item in a look up in a form the qty should automatically be calculated and shown .
6. if some one can help me to even redesign my tables ill be very helpfull thanks

[June7]

Have you looked at Microsoft Desktop Product Inventory database template? Open Access, look in the Small Business folder for available templates.

[thunderjas]

Have you looked at Microsoft Desktop Product Inventory database template? Open Access, look in the Small Business folder for available templates.

yes i had a look now the only prob is i have a table of
1.)items ( items id , item name , item category , auth scale ( officers ) , auth scale ( jcos)
2.) issue transaction ( transaction id, consumer name , item name , qty issued )
3. ) consumer demand ( consumer name , indent receipt no, feeding str, rice eater , flour eater)
now wen after filling details of consumer demand in which he is giving me only the strength of people , when i go to issue transaction i want every time i select the item name from lookup, the qty issued should to automatically calculated for that item as the scale of each item is different . now were do i put in the formula or he expression .

[June7]

Sorry, don't understand your request. No idea what you mean by 'scale'.

If you want to save calculated values, this is usually a bad idea, especially if the calculation is an aggregate.

[thunderjas]

Sorry, don't understand your request. No idea what you mean by 'scale'.

If you want to save calculated values, this is usually a bad idea, especially if the calculation is an aggregate.

firstly thanks for replying to the post
well let me put it this way ..we have consumer ( army units ) on peace and field accounting
so for peace consumers they tell us the reqmt in quantity that is their demand would be like
100kgs of rice , 200 kgs of flour etc . for field consumers they tell us their strength of persons and no of days for which ration is required and we calculate at our end
for eg :
for a single person the scale of rice is 230gms , so when they tell us they require ration for 15 days for 100 men we calculate by 100*15*0.230
in the issue transaction table were we have unit details and item and qty to be issued its very easy to just lookup for the item name and mention the qty and you are done .
But for field consumers you have to calculate every time for each item separately as every item has different scale hope it makes it a bit clear .

[June7]

Do you mean 'piece' consumers?

The convention would be to save the terms of the request in each record, then do the extended calc in query for report output. However, I can see the conundrum you are faced with - piece and field requests do not have the same terms. So your options:

1. two tables for the two kinds of requests, then use queries to combine the records for report output

2. have one table for all requests and save the calculated field consumer requirement - will require code to save the calculated result

[thunderjas]

Do you mean 'piece' consumers?

The convention would be to save the terms of the request in each record, then do the extended calc in query for report output. However, I can see the conundrum you are faced with - piece and field requests do not have the same terms. So your options:

1. two tables for the two kinds of requests, then use queries to combine the records for report output

2. have one table for all requests and save the calculated field consumer requirement - will require code to save the calculated result

hey thanks for the instant reply

well its peace only , actually i am a serving defence officer so we have army units deployed in peace and also few units deployed in field areas so accounting for both are different, So at my present location we have few units in peace accounting and few farther units are on field accounting or they are field consumers .
2.could u pl help me as to what additional do i need to do i have following tables
(a) ITEMS ( item name , auth scale of ( officers ) , auth scale of ( jcos))
(b) CONSUMER ( name , type ( peace or field)
(c) DEMAND ( indent receipt no , consumer name , indent no , strength of men ( only for field units))
(d) TRANSACTION TABLE ( indent receipt no , item name( lookup from item table), qty issued , A/u ( kgs , ltrs ) etc , date of issue )
if u could help me with the coding i ll be very helpful thanks

[June7]

This won't be easy if you have no programming experience and don't know how to code in Access.

You need a basic understanding of database principles, programming concepts, Access functionality, and programming language - either macros or VBA.

Start with a review of http://office.microsoft.com/en-us/ac...010341717.aspx

[thunderjas]

hey hi thanks for the response was able to do with some hitches . just one more thing i am running a email server xeamps and thunderbird as email client and its a Local Intranet based.i have a inventory table which lists out all items recd and issued and their reorder level . i want an auto email to be generated and send to the users in my LAN . can we do an auto email using access .

[June7]

Can easily through Outlook. I have seen code for Lotus Notes which might be transferrable to your apps but I really don't have experience with that.