Forms to generate Purchase Orders(PO)

[555Rage]

I have worked something out similar to this http://www.databaseanswers.org/data_...cing/index.htm.


I created 2 Tables : Suppliers and PriceDatabase.

Supplier
SupplierID
Supplier
Attn
Company
Tel
Fax
PriceDatabase
ModelID
Model(Brand)
UnitPrice
Description
SupplierID
Then, i created a relationship between the both tables through the supplier's ID. Is this right? And what do i have to do next? Please i need your guidance if you have the time.

[555Rage]

I have created 2 tables : Supplier and PriceDatabase

Supplier
SupplierID
Supplier
Attn
Company
Tel
Fax

PriceDatabase
ModelID
Model(Brand)
UnitPrice
Description
SupplierID

I created a relation with the supplierID but i do not know what to do next or how to proceed. Please help, i need your guidance on this if possible.

[orange]

Let's call the table with ModelId, Brand etc tblModels (or tblProducts)

So you have

tblSupplier(SupplierID,Attn,Company,Telephone,Fax) PK is underlined

tblModel(ModelID,Brand,UnitPrice,Description)

Now if Many Suppliers can provide many Models and you can get Model from Many suppliers
you can resolve the Many to Many relationship by using a junction table

Let's call it tblSuppliesModel(id, SupplierId,ModelId,SupplyDate,AgreedUnitPrice, Quantity)

id is unique identifier autonumber
supplierId is from tblSupplier
modelId is from tblModel
SupplyDate is the date of this specific transaction
Quantity is the number of this Model Supplied in this transaction
AgreedUnitPrice is the agreed upon/negotiated price for this transaction.
................................Not necessarily the price recorded last month, last week etc.

SupplierId,ModelId,SupplyDate are components of a composite unique key to prevent duplicates.
So a this transaction (PurchaseOrder or whatever) involves this Supplier, this Model on this Date. The Quantity of Modles and the UnitCost effective for this Order is all contained in the junction table.

At least that's how I see it.

Research junction table

If you want to see the whole process in free video tutorial, then

Here is a list of free video tutorials that may help. Related to Customers, Order, Items.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

[555Rage]

Let's call it tblSuppliesModel(id, SupplierId,ModelId,SupplyDate,AgreedUnitPrice, Quantity)

id is unique identifier autonumber
supplierId is from tblSupplier
modelId is from tblModel
SupplyDate is the date of this specific transaction
Quantity is the number of this Model Supplied in this transaction
AgreedUnitPrice is the agreed upon/negotiated price for this transaction.
................................Not necessarily the price recorded last month, last week etc.

SupplierId,ModelId,SupplyDate are components of a composite unique key to prevent duplicates.
So a this transaction (PurchaseOrder or whatever) involves this Supplier, this Model on this Date. The Quantity of Modles and the UnitCost effective for this Order is all contained in the junction table.

I think you have misunderstood me , let me try to walk you through the purchasing process. A designer will first design out the End product and will produce a list of parts needed to build it. The list will be handed to a "purchaser" in our company and he/she will start by searching for the parts in the database I'm suppose to create. They are suppose to search through a custom-made user interface which i assume is a form. Then they will select the parts that they want and generate it into a PO. The PO is unique to its own supplier, therefore each PO will only have parts from that supplier. This is basically what i have to do. Until here i stop.

[orange]

Did you watch the videos?

If I have misunderstood you (and that is absolutely true) it is because I am not getting a clear picture of what you are trying to build. You are giving more details now than in first 12 or so posts, but it still is not clear to me. Part of the issue is you know your business and processes and I don't. My questions are meant to elicit response to fill in some detail so that we both are "seeing the same picture". It appears that you may work in a larger company that has some databases and you are building something for 1 small piece of the puzzle. You make reference to excel, we're talking about Access, but have described nothing about the big picture nor how the Access and Excel parts will together.

Perhaps you can pretend to be a Purchaser and make a list of the questions/searches he/she is expected to find answers to based on your database. I am willing to help, but will continue to work to get a data model, before we get into nitty gritty forms/queries etc.

Perhaps your term Purchase Order is not exact???
I have worked with Pick List type activity where you have a list of what you need and some interchangeable parts/components, then you go to the warehouse to "pick" parts from inventory to satisfy your PickList.

Pick List/ Order Picking http://en.wikipedia.org/wiki/Order_picking

I have access 2003 so can not open or use an accdb formatted database.

Perhaps, someone else on the forum will join in -- maybe someone can see the requirement more clearly.

[555Rage]

Sorry if i wasn't being clear, my project, according to my supervisor, is to build a table(Description,Model,UnitPrice,Supplier), JUST 1. Then, i need to create a "Search Form" for me to navigate through the database and find specific records or parts. I have built a search form with a combo box , when i select a part, all of its information( description,model,unitprice,supplier) will appear below the combo box.
The problem i am facing now is to have a button to ADD that specific record to a report which will be customised to look like this http://i.imgur.com/Z6BlN.png. I do not have any experience of programming such functions and would like help in it.
For the excel part you were talking about, my company is a small one and all of the computers there do not have Microsoft Access, therefore, the report will then have to be EXPORTED into excel format and sent to the supplier.
You said that i was building a small piece of the puzzle, yes, this is exactly what i'm doing because this is exactly what i have to do. My project only needs me to be able to generate the Purchase Order.

[orange]

Seems you have a much better understanding of what you are doing now, than in earlier posts. I still recommend you build a model of the bigger picture (at least conceptual level) and make sure your stuff fits in that picture.
Good luck with your project.