Tracking Purchase Order Tr

[mhoque]

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.

 

[BSman]

Keep your data in one place only and use relationships and queries, etc., to calculate totals and who is working on what.

PO needs a PODetail table that contains each line for a PO.

 

[GingerR]

What does "A/C" stand for?

Yes I think you should have tables for Fund, A/C, and Departments. Makes it way easier to expand later on. I think you could get away with not having any autonumber fields for these tables, and simply just list the text numbers:

Table: ACNumbers
Field:ACID (Text) (Sample = "55444", etc)
Field: ADDesc (text) (Sample = "Design)

Table: Departments
Field: DeptID (Text, as some may have a leading zero?)

Table: Funds
Field: FundID (Text, as some may have a leading zero?)


Is what you are saying is that a PO could have like 5 parts to it, for Construction, Design, etc? Then you'd have a table which lists items that relate one time to a PO (POID, ConsultantID, Contigency, maybe others, I can't tell)

then another table that holds the various parts of the PO:

TableName: POItems
Field: POID (number, from table "PO")
Field: ACID (Text, from new table ACNumbers)
Field: FundID (Text, from new table Funds)
Field: DeptID (Text, from new table Departments)
Field: Amount

So then, for your example above, this table would hold four rows for POID = 1. The alphanumeric string denoting dept, fund, etc can be built on the fly. In fact, on a side note, I'd write it in a function if you think you will use it throughout the db. If you want to do that, let us know and we'll walk you through it. For now, you can just use joins in queries to get the Dept number, for instance, joined with the DeptID.

Anyhow, I think that's how your data tables should be structured. That's what you should think of first: proper structure. Spitting the data out in a report will come easier if it's set up properly.

Then you can see how easy it is to display the four rows for POID = 1 on your report. To get your Contigency line, will be some more work.

How are you launching the report? I made a form called POForm, where I put a text box (or you could put a combo box where the user chooses the PO Number), then they'd click a button to open the report for that entered number. My query below assumes that you are reading the PO NUmber off of this form.

We'll write a union query. You'll have to tweak what I write to fit your tables/fields. This will become your Report's Recordsource. It's a union query: we'll get your four rows of Construction, Design, etc, then UNION one more row (Row "2", with the Contigency amount) underneath them.

SELECT "1" AS SortOrder, PO.POID, PO.Contigency, POItems.ACID, POItems.DeptID, POItems.FundID,POItems.Amount
FROM PO INNER JOIN POItems ON PO.POID = POItems.POID
WHERE (((PO.POID)=[Forms]![POForm]![txtPOID])) UNION Select "2", Forms!POForm!txtPOID,0,"55120","035","130",dlookup("Contigency","PO","POID = " & Forms!POForm!txtPOID)*dsum("Amount","POItems","POID = " & Forms!POForm!txtPOID) from POItems;

This assumes the following:

Your PO Table is set up as I listed above;
You've set up a "POITems" table as I listed above.
You have a form called POForm with a text box called txtPOID, and there's something typed into it.

What this does is get the four regular rows; I put in a "1" if it's one of the rows from the POITems table, but a "2" if it's the contigency row. This way the sort of 2 will go last. I also hardcoded in your ACID, DEPTID, and FUNDID since it seems from what you said that these are always the same for the 'contigency' row.

the last part of the code actually multiplies the contigency by the sum of the AMounts listed in the POItems table for that selected POID:

dlookup("Contigency","PO","POID = " & Forms!POForm!txtPOID)*dsum("Amount","POItems","POID = " & Forms!POForm!txtPOID) from POItems;

From my query, you'd get this:

1 55110-028-129 1,000
1 55310-028-303-HP0501 3,500
1 55010-028-303-HP0503 2,500
1 52170-035-303-SW0404 3,000
2 55120-035-130 1,000


To make the record numbers (the -1, -2, -3, -4): Put a text box in the detail section of the report. In the control source, put =1. In the Running Sum propery, pick Over All. name it txtRecordNumber. Leave it off to the right some place where it's out of the way. You can make it invisible later. Run the report and see how it counts the rows. BUT--for the fifth row (in your example) you don't want it to say "-5", you want it to be a "-1", right? So over on the left where your labels are, put a text box that builds the "Amount Line X-X" words. Make a text box, and put:

="Amount Line " & [SortOrder] & "-" & iif(SortOrder=1,txtRecordNumber,"1")

So if the "SortOrder" is a 1, it will then show the record number after it. But if the SortOrder is a 2, it will just display a "-1" instead.

I know this is a lot, but hang in there, fiddle around a bit, and let us know how it goes.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html