Subform help - Summary of purchase orders for specific job number

[Technoyd]

Help, I am new here and still trying to wrap my head around Access 2010.



I am trying to display information on a subform that I can print. I would like to convert this into a report later.

I have the main form working fine with one record source table [Sales Report], and the subform shows all records for each purchase order related to the specific job number in a long list of each item per purchase order.

The subform is using a query to get records from 3 tables [Inventory Transactions], [Suppliers] and [Purchase Orders].

What I need to do is show in the subform the following based on the main form table of [Sales Report]:

Purchase order number - Transaction Date - Supplier Name - Total amount $ for the specific Purchase order number

I can't figure out how to only display one line for the purchase order instead of every line of items that was purchased on that order on the subform.

The basic record source query I have at this point is:

 SELECT [Purchase Orders].PurchaseOrderNumber, Suppliers.SupplierName, [Inventory Transactions].TransactionDate, [Inventory Transactions].UnitPrice, [Inventory Transactions].UnitsOrdered, [Purchase Orders].JobNumberID FROM [Inventory Transactions], Suppliers INNER JOIN [Purchase Orders] ON Suppliers.SupplierID = [Purchase Orders].SupplierID;

Once this is working, then I will add a total at the bottom of the main form showing the totals of all purchase orders for that specific job number.

Any help would be very appreciated.

[JeffChr]

It looks like you are trying to do too much in one query. Not enough information. Table structures would help.

[ranman256]

The MASTER form has a POkey (is this the same as purchaseordernumber?) but either way, its the uniq 1 of a kind key.
The subform needs to have the POkey in it too.

and the sub form properties:
LINK CHILD FIELD
LINK MASTER FIELD
must both be set to the POkey

(put whatever fields you want in the subform)
If you want 1 line, then set the subform to SINGLE FORM.

[Technoyd]

Here is more information on the table structures:

Purchase Orders Table:

Name Type Size
PurchaseOrderID Long Integer 4
PurchaseOrderNumber Text 30
PurchaseOrderDescription Text 255
JobNumberID Text 255
JobLocationID Text 255
SupplierID Long Integer 4
EmployeeID Long Integer 4
OrderDate Date/Time 8
DateRequired Date/Time 8
DatePromised Date/Time 8
ShipDate Date/Time 8
ShipFob Text 255
ShippingMethodID Long Integer 4
FreightCharge Currency 8
PurchaseOrderStatus Text 255
SalesReportID Long Integer 4

Inventory Transactions Table:

Name Type Size
TransactionID Long Integer 4
TransactionDate Date/Time 8
ProductID Long Integer 4
PurchaseOrderID Long Integer 4
PurchaseOrderNumber Text 255
TransactionDescription Text 255
UnitPrice Currency 8
UnitsOrdered Long Integer 4
UnitsReceived Long Integer 4
UnitsSold Long Integer 4
UnitsShrinkage Long Integer 4
SalesReportID Long Integer 4
SupplierID Long Integer 4
SupplierName Text 255

OCI Sales Report Table:

Name Type Size
SalesReportID Long Integer 4
JobNumberID Text 50
PurchaseOrderNumber Text 255
PurchaseOrderID Long Integer 4
ReportDate Date/Time 8
ClientID Long Integer 4
ClientName Text 255
ClientStreet Text 255
JobNameID Text 255
JobLocationID Text 255
Onsitedate Date/Time 8
SalePrice Currency 8
EstCom Currency 8
EstComMarkup Double 8
TotalEstCom Currency (Calculated) 8
EstSubCon Currency 8
EstSubConMarkup Double 8
EstSubConTotal Currency (Calculated) 8
EstShopMWLabourRate Currency 8
EstShopMWLabour Double 8
EstShopMWTotal Currency (Calculated) 8
EstShopSSLabour Double 8
EstShopSSLabourRate Currency 8
EstShopSSLabourTotal Currency (Calculated) 8
EstENGHours Double 8
EstENGRate Currency 8
EstENGTotal Currency (Calculated) 8
ESTShipping Currency 8
ESTShippingMarkup Double 8
ESTShippingTotal Currency (Calculated) 8
OtherCosts Currency 8
OtherCostsMarkup Double 8
TotalOtherCosts Currency (Calculated) 8
TotalEstCosts Currency (Calculated) 8
Notes Text 255
TotalCostswithMarkup Currency (Calculated) 8
SupplierID Long Integer 4
SupplierName Text 255
EmployeeID Long Integer 4

[Technoyd]

The MASTER form has a POkey (is this the same as purchaseordernumber?) but either way, its the uniq 1 of a kind key.
The subform needs to have the POkey in it too.

and the sub form properties:
LINK CHILD FIELD
LINK MASTER FIELD
must both be set to the POkey

(put whatever fields you want in the subform)
If you want 1 line, then set the subform to SINGLE FORM.

The POKey (PurchaseOrderID) is different than the (PurchaseOrderNumber) field which is in each table.

I linked the child field to (JobNumberID) and also the master field to (JobNumberID).

Because I want only the purchase order's for the specific job number.

And I don't actually have a table for the subform, I am using an the above code in my first post as the record source.

Should I be creating a table and adding a relationship?

[Technoyd]

Ok, I have this form working well enough.

Thanks for the comments and help with this, much appreciated!

End of Thread.