R
Ramzi
Hi,
Its easyer to join table 2 and 3 together and use fields QtyIn and QtyOut.
Its easyer to join table 2 and 3 together and use fields QtyIn and QtyOut.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
Nicole
Hi Iam really struggling with creating a stock control system on access. I
have set up the table with all the info required these cover three tables, in
stock, delievered stock and out stock I would like all these to add up and
show me what I currently have in stock, but I am getting in a right muddle
any help would be good, Thank you. If I can send you my tables by email I
will so you can see what i have done
Table 1 Currently In
ProductID ProductName ProductDescription Serial
Number Supplier UnitsInStock ReorderLevel
Table 2 Delievered
ProductID Date ProductName ProductDescription Delievery Note
Ref; SerialNumber Quantity
table 3 Out
ProductID Date ProductName ProductDescription Supplier Job Number Quantity
Used
I will need the current stock as we would be starting the stock control
systme based on what we already in stock then work out on top of the that
what is going in and out. Any help or ideas would be good.
have set up the table with all the info required these cover three tables, in
stock, delievered stock and out stock I would like all these to add up and
show me what I currently have in stock, but I am getting in a right muddle
any help would be good, Thank you. If I can send you my tables by email I
will so you can see what i have done
Table 1 Currently In
ProductID ProductName ProductDescription Serial
Number Supplier UnitsInStock ReorderLevel
Table 2 Delievered
ProductID Date ProductName ProductDescription Delievery Note
Ref; SerialNumber Quantity
table 3 Out
ProductID Date ProductName ProductDescription Supplier Job Number Quantity
Used
I will need the current stock as we would be starting the stock control
systme based on what we already in stock then work out on top of the that
what is going in and out. Any help or ideas would be good.
N
Nicole
I have mircosoft access 2000
N
Nicole
If I changed it to the following how can i find out or create a table to show
current stock from these tables:
ProductID ProductName ProductDescription Serial Number Supplier Delievery
Ref; Quantity Delievered Current Stock ReorderLevel
ProductID Date ProductName ProductDescription Supplier Job Number Quantity
Used
current stock from these tables:
ProductID ProductName ProductDescription Serial Number Supplier Delievery
Ref; Quantity Delievered Current Stock ReorderLevel
ProductID Date ProductName ProductDescription Supplier Job Number Quantity
Used
K
Ken Sheridan
You have a lot of redundancy in your tables at present in that product names,
descriptions and serial numbers are repeated in more than one table. Also
your 'Currently In' table is holding values which can be computed from the
other two, i.e. the number in stock of a product is the sum of all deliveries
of that product less the sum of all removals from stock of the product. This
leaves the database open to inconsistent data creeping in.
In a relational database tables model entity types, with their columns
representing attribute types which are specific to the products entity type
(in the jargon they are said to be functionally dependent solely on the whole
of the table's primary key). A more appropriate model would be made up of
the following tables:
1. Products: this would have one row per product and columns such as
ProductID (the primary key), Productname, ProductDescription, SerialNumber,
ReorderLevel.
2. Deliveries: this would have columns ProductID (a foreign key
referencing the primary key of products), DeliveryDate, DeliveryNoteRef,
Quantity. Your current stock in hand would be inserted as rows into this
table.
3. Removals: this would have columns ProductID (a foreign key referencing
the primary key of products), RemovalDate, JobNumber, Quantity
You also show a Supplier column in your 'Out' table, but I'd have thought
that should pertain to Deliveries, so you'd have a SupplierID foreign key
column in that table.
In addition to these tables you'd also have a Suppliers table with
SupplierID as its primary key, and a Jobs table with JobNumber as its primary
key.
You can compute the quantity of products in stock in a query which subtracts
the sum of the quantity removed from stock from the sum of the quantity
delivered. The query can also include a computed column which indicates if a
product has reached or fallen below its reorder level.
You might also need to cater for stock being written off. This could be
done in a number of ways. The simplest would be to treat a write off as just
another removal from stock, e.g. to job number zero. A better solution would
be to have a separate WritteOffs table as you can then have a column's
specific to write offs, such as the reason, which would not be appropriate in
the Removals table.
If you'd care to mail me your file at:
kenwsheridan<at>yahoo<dot>co<dot>uk
I'd gladly take a look at it. Recasting your tables into the above model is
a fairly simple task using a few 'action' queries, and wouldn't take long.
Ken Sheridan
Stafford, England
descriptions and serial numbers are repeated in more than one table. Also
your 'Currently In' table is holding values which can be computed from the
other two, i.e. the number in stock of a product is the sum of all deliveries
of that product less the sum of all removals from stock of the product. This
leaves the database open to inconsistent data creeping in.
In a relational database tables model entity types, with their columns
representing attribute types which are specific to the products entity type
(in the jargon they are said to be functionally dependent solely on the whole
of the table's primary key). A more appropriate model would be made up of
the following tables:
1. Products: this would have one row per product and columns such as
ProductID (the primary key), Productname, ProductDescription, SerialNumber,
ReorderLevel.
2. Deliveries: this would have columns ProductID (a foreign key
referencing the primary key of products), DeliveryDate, DeliveryNoteRef,
Quantity. Your current stock in hand would be inserted as rows into this
table.
3. Removals: this would have columns ProductID (a foreign key referencing
the primary key of products), RemovalDate, JobNumber, Quantity
You also show a Supplier column in your 'Out' table, but I'd have thought
that should pertain to Deliveries, so you'd have a SupplierID foreign key
column in that table.
In addition to these tables you'd also have a Suppliers table with
SupplierID as its primary key, and a Jobs table with JobNumber as its primary
key.
You can compute the quantity of products in stock in a query which subtracts
the sum of the quantity removed from stock from the sum of the quantity
delivered. The query can also include a computed column which indicates if a
product has reached or fallen below its reorder level.
You might also need to cater for stock being written off. This could be
done in a number of ways. The simplest would be to treat a write off as just
another removal from stock, e.g. to job number zero. A better solution would
be to have a separate WritteOffs table as you can then have a column's
specific to write offs, such as the reason, which would not be appropriate in
the Removals table.
If you'd care to mail me your file at:
kenwsheridan<at>yahoo<dot>co<dot>uk
I'd gladly take a look at it. Recasting your tables into the above model is
a fairly simple task using a few 'action' queries, and wouldn't take long.
Ken Sheridan
Stafford, England
N
Nicole
Thank you i have sent it to the given address.
Ken Sheridan said:You have a lot of redundancy in your tables at present in that product names,
descriptions and serial numbers are repeated in more than one table. Also
your 'Currently In' table is holding values which can be computed from the
other two, i.e. the number in stock of a product is the sum of all deliveries
of that product less the sum of all removals from stock of the product. This
leaves the database open to inconsistent data creeping in.
In a relational database tables model entity types, with their columns
representing attribute types which are specific to the products entity type
(in the jargon they are said to be functionally dependent solely on the whole
of the table's primary key). A more appropriate model would be made up of
the following tables:
1. Products: this would have one row per product and columns such as
ProductID (the primary key), Productname, ProductDescription, SerialNumber,
ReorderLevel.
2. Deliveries: this would have columns ProductID (a foreign key
referencing the primary key of products), DeliveryDate, DeliveryNoteRef,
Quantity. Your current stock in hand would be inserted as rows into this
table.
3. Removals: this would have columns ProductID (a foreign key referencing
the primary key of products), RemovalDate, JobNumber, Quantity
You also show a Supplier column in your 'Out' table, but I'd have thought
that should pertain to Deliveries, so you'd have a SupplierID foreign key
column in that table.
In addition to these tables you'd also have a Suppliers table with
SupplierID as its primary key, and a Jobs table with JobNumber as its primary
key.
You can compute the quantity of products in stock in a query which subtracts
the sum of the quantity removed from stock from the sum of the quantity
delivered. The query can also include a computed column which indicates if a
product has reached or fallen below its reorder level.
You might also need to cater for stock being written off. This could be
done in a number of ways. The simplest would be to treat a write off as just
another removal from stock, e.g. to job number zero. A better solution would
be to have a separate WritteOffs table as you can then have a column's
specific to write offs, such as the reason, which would not be appropriate in
the Removals table.
If you'd care to mail me your file at:
kenwsheridan<at>yahoo<dot>co<dot>uk
I'd gladly take a look at it. Recasting your tables into the above model is
a fairly simple task using a few 'action' queries, and wouldn't take long.
Ken Sheridan
Stafford, England