Designing a Purchase Order DB 1

[mhoque]

I am a beginner user of MS Access. So far created a time tracking DB for project hours with some help from some of you. It records hours spent by staff, generates managerial reports for project hours and several other employee specifice reports.

This next request is rather too long to explain. So please be patient. Basically, I need to create a Purchase Order (PO) Managment DB. My Objective is to track all PO from issuance to closure. I have been thinking of creating the following tables to start:
1. PO: a)POID (number), b)ConsultantID c)Amount (currency)(more on this later), d)Contingency(currency) e)IssueDate (Date/Time), f)ChangeOrderNo(Number), f)CloseDate

2.Consultants: 1)ConsultantID (Auto), 2)ConsultantName(Text), 3)ConsultantAddress, 4)ConsultantCity, 5)ConsultantZip, 6)ContactLastName, 7)ContactFirstName, 8)ContactPhone, 9)ProjectID(Number),10)LastUpdated(Date/Time)

3. Projects: 1)ProjectID (Auto), 2)JobNumber (Text), 3)ProjectName, 4)ProjectManager, 5)POID

4. Employees: 1)EmployeeID (Auto), 2)LastName (text), 3)FirstName (text), 4)Title(Text) etc.

5. POPayments: 1)RecordID (Auto), 2)POID(Number), 3)InvoiceNo (Text), 4)DatePaid(Date/Time), 4)AmountPaid


Where I am having problem is with the concept of "Amount". A PO can have several lines, A consultant can have several POs. Each Line of amount can have 3 segments and may or may not have a ProjectNo. associated. Let me explain. Let's say PONo.1 is issued to ABC Co. for a 10,000 with a 10% contingency. It's written this way

PONO. 00001
Change Order No. 0
Consultant: ABC Company
Amount Line 1-1 55110-028-129 1,000
Amount Line 1-2 55310-028-303-HP0501 3,500
Amount Line 1-3 55010-028-303-HP0503 2,500
Amount Line 1-4 52170-035-303-SW0404 3,000
Amount Line 2-1 55120-035-130 1,000

The Last line represents 10% Contingency. Each five digits (55110 etc.) is the A/C no for "construction", "Design" etc., the next 3 digits (028, 035 etc.) is the Fund code, the next 3 digits (129, 130 etc) is for the Dept ID, last five digits and letters represent Project Numbers.

Each PO can go through changes. Sometimes an Amount line is either increased or decreased or deleted, Sometimes, the amount is increased just by reducing the contingency amount.

My questions:
1)Should I also create tables for Funds, Dept and A/C numbers.

2)What other variables I must consider for other tables?

3)What must be done to capture any changes made to it throught its life?

Any other suggestions? Any help anyone can give will be much appreciated. Many Thanks.

 

[mrdenny]

1. Yes
2. Anything that will need to be selected needs a table.
3. In SQL, Oracle, MySQL, etc this could all be handled through triggers. In Access you'll need to handle this within the forms. When ever a change is made the old data should be saved in a useful format in an audit table.

Don'y forgot a table for the line items of the PO. I'd remove the amount from the PO table. Just have the front end total up all the lines and display that amount.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[johnherman]

Perhaps you can find something here to help you get started:

http://www.databaseanswers.org/data_models/index.htm

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[mhoque]

Dear Mrdenny:

Thanks for the tips. I am sorry but I don't know what did you mean by the last statement. How to have the front end total and display?

Thanks.

 

[mhoque]

Dear John Herman. Thanks for the link. I will check it out.

 

[ceh4702]

Purchase orders depend on what you are considering Scope for your project and what you want to keep track of.

Vendor Number and Vendor Information
Requestor and Requestor Information (Employee File)
Approval Level Information
We use electronic Signatures on our Lotus Notes System.

Our new system has a Person File where we store both employees and vendors, Staff, Students, Faculty, Business Entities, Organizations, etc. Then everyone can have multiple addresses and multiple phone numbers, and multiple E-mail Addresses. That is a little on the depending on your scope and complexity of your system side and is all discretionary. Our system is designed for a wide variety of schools and even has special options for State Requirements and requirements for Canada. So you could just simplify things a bit for your purposes.

Items for the individual Items in a separate table
Quantity
Type of issue i.e. box, each, carton, case, Pkg, barrell, etc.
Extended price (Actually is a computed field) Computed fields are discretionary.

If you actually keep track of the item numbers from the vendor, this is one item you might want to keep track of.
Vendor -- Item Number

Then you may have an inventory system for your use with your item or stock number.

We use a date of Aquisition wich is the date we actually purchase the item or when we order it if it is on account. You may want to store the date it is paid for and the Check Number you sent to the vendor. You may also store the date you received it at receiving.

If you have special arrangements with suppliers for Credit Purchases, you may want to store the specifics for that information. For instance it is not uncommon to have arrangements of purchases on credit for 90 or 60 days credit or longer, so you may want to store the madatory payment date. If the supplier offers a discount for early payment then that date might want to be stored. So you order something they say 90 days and a 15% Discount if you pay by a certain date. So you would want to store the discount value. This has more to do with Accounts payable but everything is related.

Not everything oredered is received. How you handle that is up to your discretion. As an example suppose you make an order with 2 items on it. One item is out of stock and the supplier takes that off your order. Then the second Item you ordered a quantity of 150 and they shipped 50 to you and back-ordered 100. So you may want another item table for items received. You usually get a statement with each order you receive saying what is in the order the company shipped to you. You should be physically counting items that you receive to see what is going on and keeping the results on file. Otherwise you can get billed for items you never receive. So you have to have a plan for that. When you receive it you need to add it to your inventory system.

If you do not like my post feel free to point out your opinion or my errors.

 

[ceh4702]

In access look at the delivered examples to see if you can find anything usable as an example. I dont know what comes with the Northwind database, but I suggest you look at it if you can.

If you do not like my post feel free to point out your opinion or my errors.

 

[mrdenny]

How to have the front end total and display?

You'll have to look into Access. I'm sure it's got a function for this. Check in the Access forums for info on how to have it total up the displayed data and show the total.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ceh4702]

Access can use forms for Data Entry and for displaying Querry Data. I think it can also do Reports and letters. ACCESS is more than just a database.

I suggest you look for the ACCESS Forum for some of these questions.

A Total is realy just a Computed Colum which you can make in a Querry. Might also look at the SUM Function. This falls under SQL Specific Functions. Typically using the sum of a column with "group by" and "order by" can give you totals of groups or categories or maybe by Department and section. You might find querry help in an SQL Forum if we have one.

How SQL is phrased is very specific to SQL Server or engine specific for your particular application/SQL server. The SQL is fairly standard in ACCESS. However, there may be some functions that are particular to Access that are not the same in other languages. For SQL you may want to find a small SQL Beginner's book with some examples. Sometimes these have a title like a "Guide to Standard SQL".

In Access there is a Querry Wizard, but it can be hard to get use to if you never used it. This is why you might want a MS Access book for you specific version of Access, depending on the version of MS Office Suite you are using. sometimes you can find used books for older versions practically given away or sold as used at very large discounts. I have seen schools literally give away outdated books.

If you do not like my post feel free to point out your opinion or my errors.

 

[Mag0007]

johnherman:

Good website...

KNow of any more like that? You can learn a lot from these type of sites!

Star Deserved!