Lookup ID instead of name

[ankit.goenka]

Hi I am trying to develop a access solution for my office - I am writing the details below



Tables:
Tbl_vendor - (venid/venname/venphone)
Tbl_item - (itemid/itemname)
tbl_requirement (itemname/quantity/isordered)
Tbl_order (itemname/quantity/venname/isreceived)


This is a purchase order processing system in which the requirements are put the approved and then orders.

I intend to use user login to control who can do what... Requirements to be put my all employees and order to be approved by purchase manager, price is not important here as we can always compare and purchase manager does that


Plz suggest changed in table design along with what should be related to what and also if anyone had enough time I can share my work ; which is incomplete.


I have successfully linked the requirement table with item table.. I have successfully linked vendor to order table but I am unable to link the requirement table item to order table...instead of showing up items it shows item id...

Wonder where I am going wrong


Plz help and obliged

[orange]

Do not use lookup fields at table level.

You appear to be missing an OrderDetail table.
Also, it will be helpful for you, for readers and for communications if you were to write a description of your business in plain English identifying what your database is going to support.

You can search this forum for "agreedToPrice" to get some insight into prices, changing prices and historical records.

You may get some ideas from this thread and the links within.

Good luck with your project.

[ankit.goenka]

Thanks will go thru it..will also.share the access file

[ItsMe]

Are you trying to manage accounts payable via purchase orders? I would start by trying to manage a list of Vendors. With a working example of a vendors list, I would then start to diagram the various entities needed. How the entities relate to each other would be illustrated in the diagram.

The most practical would be to attempt the implementation of an off the shelf solution like Quick Books. If that is not acceptable and you need a custom solution, then you might want to implement a custom solution. To implement a custom solution, I would recommend hiring someone with experience building business solutions.

[ankit.goenka]

hi!
Thanks for your reply.

My requirement is very basic and I am going to write it process wise - as to what I wish to achieve

I have the list of items i deal in and also the list of suppliers
now

my staff makes requirements - ie what all must be purchased and write a quantity which they they is appropriate
I look into the list and allot vendors from who what is to be bought and in what quantities - the quantity i decide is the final quantity which is ordered

the item when ordered is marked as ordered and marked as received when received

on many occasions vendor fails to send complete order due to unavailability - one vendor may have multiple items in one order.

as of now we are maintaining this manually and we have major issues considering we may skip to place order of some particular SKU just because we have a similar SKU in stock and may decide to buy it later.


If any more clarifications required plz ask and I honestly respect and am obliged to this forum and its members to help me out!

[ankit.goenka]

I have made the database as per design attached. Pl correct me if I am wrong

[orange]

see this model for info. It deals with Customers and Products, but you can adjust it to Vendors and Products. It should give you a good starting point. It is generic, so some parts may not apply to you---remove them as applicable.

In the model you showed in previous post, you should use the itemId and VenId in the tbl_order - not the Names.

When an Order can contain 1 or more Items/Products, there is another table --tblOrderDetail

Customer-->Order--->OrderDetail<---Item
There are some videos here that may help.

Good luck with your project.