Help design a Purchase Order Management DB

[xyzlook]

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

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.

 

[SkipVought]

Hi,

You have a header table that contains information about a PO: date placed, vendor id, etc

You have a detail table related to that header table that contains line items. The sum of each line item's amounts is the Amount of the PO (BTW, aggregation is usually done as a query and not STORED, since any line item can change)

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

FYI

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

ESSENTIAL principles for database design!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}