Purchase Order Total

[PandoraGirl]

I am new to Access and trying to build out a vendor management database. I have a table with Purchase Requisitions and a table with Change Orders. Both have the Purchase Order number as the Foreign Key. I am trying to build a query or report that will give me the total cost of the Purchase Order (requisition amount + all change orders listed individually). However, what I end up getting are 2 lines that each have the requisition amount + one change order. I've spent quite a bit of time trying to figure out how to get what I want, using a Sum IIF expression (using the PurchaseOrder column), but cannot figure out how to make it work. Any help would be greatly appreciated.

[Bob Fitz]

Can youpost a copy of the db

[PandoraGirl]

Bob, Here you go! Thank you in advance for your help!

[Bob Fitz]

Please explain what the following tables represent in the business:

PurchaseOrders
ChangeOrders
POAll
Requisitions

[PandoraGirl]

Bob,

POAll will likely be deleted when I get everything built out. Requisitions is where a Purchase Order starts. It contains information about what is being purchased and the amount of the purchase. The Purchase Order number will be added after proper approvals have been obtained. PurchaseOrders is a list of the Purchase Order Numbers and the associated Supplier. ChangeOrders are any items that increase/decrease the Purchase Order amount.

[Bob Fitz]

....Requisitions is where a Purchase Order starts. It contains information about what is being purchased and the amount of the purchase....

So what products are being ordered/purchased in the 2 records shown in table Requisitions?

[orange]

Further to Bob's comments, could you lead us through "a day at the office" or if it makes more sense, the "process of creating a new purchase order, a change order etc".
It will help us understand:
-what your database is intended to support and
-what some of the terms mean in context.

You may find this link "Stump the model" helpful.

[PandoraGirl]

I am reattaching my file after having added some dummy product descriptions. Eventually, there will be forms that the team uses to enter the requisition or change order information. They will have the supplier, product descriptions, project it relates to, cost center to be billed, account numbers to be billed, cost of the product, sales tax, etc. We will need queries or reports to view this information. We may not always want the product description, but we will want to know the current total amount of the Purchase Order (original requisition amount + any change orders) or the total amount going to a certain cost center or account by PO. I have built a basic database and am now trying to figure out how to pull the data that will be needed before I continue to expand in case I need to rework my tables in order to get the information needed.

[mike60smart]

Hi

Only took a quick look at your database and you need to stop and carry out some changes to the relationships.

Each table should have an Autonumber PK - LongInteger - If the name of the
table is tblPurchaseOrders, then the PK should be named PurchaseOrderID

All tables should not have Lookup Fields

The related FK in any related table must be a LongInteger - Number DataType

You would then link PK to FK

[PandoraGirl]

mike60smart,

Why can't I have Lookup Fields? We have thousands of suppliers. There is no way to know the supplier number without looking up the supplier name but the supplier number is what we need when we upload into our financial system.

[Bob Fitz]

mike60smart,

Why can't I have Lookup Fields? We have thousands of suppliers. There is no way to know the supplier number without looking up the supplier name but the supplier number is what we need when we upload into our financial system.

Because tables are for data. You use forms to interact with the data and combo boxes on those forms to lookup data. See: http://access.mvps.org/access/lookupfields.htm

[mike60smart]

Hi

Your tblSuppliers should have an Autonumber PK and a SupplierNumber field which is used in your Financial System

The Combobox on your Data Input Form will allow you to search either by Supplier Name or Supplier Number

[Welshgasman]

mike60smart,

Why can't I have Lookup Fields? We have thousands of suppliers. There is no way to know the supplier number without looking up the supplier name but the supplier number is what we need when we upload into our financial system.

Mike only means at the table level, as it hides the fact that the IDs are being used and not the descriptions. When you then create a report for that table, you will be back here asking why the report has numbers and not the text descriptions.
You still have lookups, but you do the joining, or stick with table lookups, but understand how they work.

I still have table lookups in my very first DB, but I did no know any better back then.
Now I would not use them. There are a few drawbacks for using them as well.

https://www.google.com/search?q=acce...hrome&ie=UTF-8

First link from the link above.
https://improvingsoftware.com/2009/1...cess-are-evil/