Simple Access Inventory Management

[RoystonTeo]

Hi,

This is my idea,

I have designed 2 tables now.
One with the fields PartNumber and Qty.
The stock qty that is in my store now.

I will add in another table for the
Date, PartNumber, InQty, OutQty, ReferenceNo.

Example:
Date PartNumber InQty OutQty ReferenceNo:
25-09-07 11-1212 50,000 0 PO-00538
25-09-07 11-1212 0 20,000 Inv-00538

I will update the table accordingly to this.

Then with a query, when I enter the part number, I will be able to know how much stock is in the store for that part that i key in. Please take note, there are in and out qty.

Plus to have a report summary to show the partnumber and qty that is in the store at every end of the month.

Can somebody help me on this query and Report?

This database objective is to track the movment of the stocks, and records in and out qty with their date and reference.
Also to find out the qty left in the store.

[nico5038]

Hmm, the general approach is to have a StockItem table with the items and the stock available at the last stockcount. (Generally the end of the bookyear)

Next you have the PurchaseOrderDetails indicating the added stockitems Qty and the Invoices indicating the shipped stockitems Qty.

To get the stock Qty you take the StockItem's stock quantity, add the PO's and subtract the Invoiced (delivered) StockItems.
For this you can create a UNION query like:

select ItemID, StockQty from tblStockItems
UNION
select ItemID, PurchaseQty from tblPurchaseOrders
UNION
select ItemID, DeliveredQty * -1 from tblInvoices;
 

Finally a groupby query summing the Qty field will give the accurate stock on hand.

Getting the idea ?

Nic;o)

[RoystonTeo]

Hi Nic,

Thank you very much for your help.
Currently i am entering all the parts and data into it for evaluation.

May i ask you another question,
I create a form name: Qty IN.
The fields are part_no, qty, date.

Whenever i click the form, it will put me to the first line of the data.
What should i do so that the data will always be the last item, which will be blank for me to enter a new record?

[nico5038]

Hi Nic,

Thank you very much for your help.
Currently i am entering all the parts and data into it for evaluation.

May i ask you another question,
I create a form name: Qty IN.
The fields are part_no, qty, date.

Whenever i click the form, it will put me to the first line of the data.
What should i do so that the data will always be the last item, which will be blank for me to enter a new record?

Won't be possible, as Access shows the last row as newrecord by design.
A solution could be to have two subforms, one for showing the existing ones and one in "Dataentry" mode.

Nic;o)

[RoystonTeo]

Won't be possible, as Access shows the last row as newrecord by design.
A solution could be to have two subforms, one for showing the existing ones and one in "Dataentry" mode.

Nic;o)

Hi Programmers,
My database is up, but i will want to know to know when did i receive the parts between this date and another date. This 2 dates are not fix, and will be key in by the users. What will be the query like?
Currently my SQL doesn't work:
WHERE tbl_in.in_date Between [DD-MM-YY] And [DD-MM-YY];

Also anybody knows how much records can a table consists of?
Up to 10,000 ? 20,000 ? per table.

[natalie99]

Hi

In regards to the number of maximum records.

Access can hold millions of records per table, however, this all depends upon the size of the data. Access limits it's capabilities through size only. Depending on your version, it holds 1 or 2 gb.

If your table is only 20,000 records you shouldn't have any problems unless you are using some very large field data types, indexes, queries and/or macros which would take up some space.

For eg. 5 million records in 7 collumns limited to 55 digits or letters per collumn comes in around 200MB.

Nat

[RoystonTeo]

Thank you so much Nat.
Are you also able to answer this question:

i will want to know to know when did i receive the parts between this date and another date. This 2 dates are not fix, and will be key in by the users. What will be the query like?
Currently my SQL doesn't work:
WHERE tbl_in.in_date Between [DD-MM-YY] And [DD-MM-YY];

[natalie99]

i will want to know to know when did i receive the parts between this date and another date. This 2 dates are not fix, and will be key in by the users. What will be the query like?
Currently my SQL doesn't work:
WHERE tbl_in.in_date Between [DD-MM-YY] And [DD-MM-YY];

you could simply write a query in design view for this, select the two date fields and in the criteria range:

Min_date

<=[date_received]

Max_date

>=[date_received]


where [date_received] is your part delivery date, taken from your table storing parts deliveries, and Min_date and Max_Date are taken from your key entered dates.

you then need to select part number and delivery date, your end result will show you how many of each part were delivered on each day in single lines.

this is a very simple way of showing the parts delivered between two dates. is that what you are trying to do?

[RoystonTeo]

Last question before i can post this program for users.

I will like to lock all the queries with passwords, which means nobody can see the codes to prevent copyright.

What is the best way to do this?