Forms to generate Purchase Orders(PO)

**555Rage** · 09-11-2012, 02:27 AM

Hi, I'm a complete newbie at Microsoft Access. I have been attached to a company for my Industrial Training Programme and have completely no experience in using Microsoft Access 2010. Right now i have gone on youtube and watch several videos but is still vague on the programme itself.

I am tasked to create a database for their buying and selling of parts and items.

I have done a "Price Database" in 1 table which consist of the following field: Description, Model, Unit Price and supplier.
I have also done an empty template which will be my Purchase Order(PO) once its filled. It should have the field: Description, Model, Unit Price, Supplier, Quantity, Total.

I need to create a "search page" using forms where users will search using a combo box on the fields supplier and model on the "price database". Once user has found the record(part) that they want, he/she will enter the quantity that they want and click a button where the record with the quantity will be added to that empty template. PO may consist of more than 1 parts.

The PO will than have to be exported into excel which is their company's "main format".

Thanks for anyone that is willing to help me in advance.
CHEERS

**orange** · 09-11-2012, 05:49 AM

Have you done any research on Database design? Or Normalization?

When you identify one of your Tables as "Price Database", I think you are missing some basic concepts.
Excel and Access are very different "tools".

Please read this paper re data base principles.
http://forums.aspfree.com/attachment...achmentid=4712

To get a "feel " for normalization and relationships, I suggest you read the first 3 topics here
http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck.

**555Rage** · 09-11-2012, 07:07 AM

I'll read it once i have time, but in the mean time, can i have some tips on how add records to the empty template? Do i need to use VBA or Macro?

**orange** · 09-11-2012, 07:24 AM

You are jumping way ahead of where you need to be.

Here's a free data model involving Purchase Orders. Do you tables and relationships follow/look like this to some degree?

http://www.databaseanswers.org/data_...ders/index.htm

**555Rage** · 09-11-2012, 10:21 AM

Right now, my "price database" has 2 important fields that are really related to each other, which is the model(of the parts) and its supplier. Therefore i do not think that i would need to create relationship tables for other stuff yet. Im really a newbie and i want to take it one step at a time. I really haven't thought of what will happen after the Purchase order is send out. My main task at hand for this whole Industrial training program is to be able to generate the Purchase Order using the forms by searching the record(model of part) on my "price database" , it should show the unit price of the part and its supplier . Then, there should be a button to add it to an empty template( empty Purchase Order template) to be made.
The company i'm attached to has no experience in Microsoft Access , therefore their instructions are very vague.
Flow chart i am given for this databas: Part List --->Search for parts in part list on database using form --->selected parts will be generated on an empty template

**orange** · 09-11-2012, 10:48 AM

You indicate 1 table with Supplier and Part information. I can almost assure you that your table set up is incorrect. However, you may only be dealing with 1 very, very small piece of a larger picture.

You also said these were the fields in your table
Description
, Model
, Unit Price and
supplier.

You also mentioned an empty template (PurchaseOrder)?? with the following fields
Description
, Model
, Unit Price
, Supplier
, Quantity
, Total. ................................ In my view this template is probably a Report

From experience, i would suggest that Unit Price changes with time. So is this Unit Price the CurrentUnitPrice, or the UnitPrice for that Part for that Supplier at that specific Time?? Where are you going to store the Quantity? What if the unit price changes next week or month?

What about Supplier information
SupplierID,
SupplierName
Supplier StreetAddress
SupplierCity
SupplierPostalCode
SupplierPhone
SupplierContact.....

Just for clarity:

Data is stored in Tables.
Data may be presented in Forms and Reports.
Actual Purchase Orders can be printed based on data in Tables presented in Forms or Reports

**555Rage** · 09-11-2012, 06:41 PM

Unit price will not change with time, and if god forbid it does, user will manually edit the database through a form created for it which i think i can do.
Can i send you my Database privately and you take a look and tell me what's wrong.

**orange** · 09-11-2012, 07:21 PM

I don't have acc2010. I think it is best if you keep working and asking questions on this forum. Or get someone with Acc2010.
Did you look at the data model link I gave you earlier?

Take a look at this link
http://www.databaseanswers.org/approach2db_design.htm

I used Google to look for a sample MS access database for buying and selling items and
found this link http://www.schools.ash.org.au/olshc/...ch/ureldes.htm

You may get some ideas there.

**555Rage** · 09-11-2012, 07:43 PM

Yes , i haved looked at that link, it shows everything from tracking the purchasing to receiving.
But i think you have misunderstood me, i'm not needed to build an entire database for the company, but to only produce the database for the price of its parts.
I than need to create the search page and produce the Purchase Order from the selected parts from the search page.

**orange** · 09-11-2012, 08:04 PM

Fair enough, but I don't know exactly what is within the scope of your "project/business".
Show me a jpg of your tables and relationships.

Describe exactly what you are trying to do.
Make a list of business facts.

Here is the simplest model I could find.
http://www.databaseanswers.org/data_...cing/index.htm

Go to the link and click on specifications to get some idea of what the model is trying to represent.

As for your PurchaseOrder:
don't you have a Customer or Supplier to purchase from?
Do you have Delivery or Receipt of Goods???

What is your Business Context????

**orange** · 09-11-2012, 08:08 PM

Fair enough, but I don't know exactly what is within the scope of your "project/business".
Show me a jpg of your tables and relationships.

Describe exactly what you are trying to do.
Make a list of business facts.

Here is the simplest model I could find.
http://www.databaseanswers.org/data_...cing/index.htm

Go to the link and click on specifications to get some idea of what the model is trying to represent.

Purchase Order (wikipedia)
A purchase order (PO) is a commercial document issued by a buyer to a seller, indicating types, quantities, and agreed prices for products or services the seller will provide to the buyer. Sending a purchase order to a supplier constitutes a legal offer to buy products or services. Acceptance of a purchase order by a seller usually forms a contract between the buyer and seller, so no contract exists until the purchase order is accepted. It is used to control the purchasing of products and services from external suppliers.^[1]

**555Rage** · 09-11-2012, 08:25 PM

I managed to get snapshots http://imgur.com/a/uEnFS of my price database. i have around 3000 records of parts. 1 part may have different suppliers but each supplier will only provide 1 unit price. I do not know how to represent all the different currencies in 1 field, therefore i seperate them into different fields.

I've successfully created a search page that will find specific records on my database. Now what i need to do is to transfer that record into a empty report/table which i can export into a microsoft excel spreadsheet, this empty report/table will have extra fields like Quantity and Total that the price database do not have so when the user enters the quantity it will automatically generate the total price on the Total field. Please note that 1 Purchase order may have more than 1 order, which means i may have to add several records into the empty report/table.

And for your supplier issue, that may be a separate problem, because it is only added to the top of the PO template in excel.

I grabbed a empty PO template and try to illustrate it. http://i.imgur.com/Z6BlN.png

**orange** · 09-11-2012, 08:45 PM

And for your supplier issue, that may be a separate problem, because it is only added to the top of the PO template in excel.

I grabbed a empty PO template and try to illustrate it. http://i.imgur.com/Z6BlN.png

Yes a purchase order involves Supplier which earlier you felt wasn't necessary

But i think you have misunderstood me, i'm not needed to build an entire database for the company, but to only produce the database for the price of its parts.

If you are playing at Access and hoping to fill a PurchaseOrder form from /by using Excel, you're in for a long learning curve.

My approach, and the approach I am trying to share with you is along the following:
- get a clear statement of WHAT you are trying to do
-identify what are the entities involved (things)
-what are the attributes of these entities (fields)
-how are the entities related to one another (business facts/rules)
-gather some test data (with valid and non valid values)
-create a working data model
-test your model with your test data
-document every issue
-reconcile each and every issue by adjusting the model or refining the test data
-repeat until you have no issues
--- at this point you understand what you have to do; are very familiar with the data; and the database design
--mock up some forms/reports with prototypes; review with others get agreement; move on
--now go on and build the forms/reports/queries
-

**555Rage** · 09-11-2012, 08:58 PM

Okay thanks, i'm gonna try to create a test database.

**orange** · 09-11-2012, 09:04 PM

Forms to generate Purchase Orders(PO)

Thread Tools

Forms to generate Purchase Orders(PO)

Similar Threads

Have supplier forms, one frm for ordering need orders to go to supp frm automaticall

how to creat purchase order

Purchase orders 2 in 1

Help With Purchase Order Form

Orders & Products

Posting Permissions