Here is the SQL for the Purchase Orders Query. I use this query as a sub
query for several other queries for different pieces of information so I
apologize for its length.
SELECT [Purchase Orders].PurchaseOrderID, [Purchase
Orders].PurchaseOrderNumber, [Purchase Orders].OrderDate,
Products.ProductName, [Inventory Transactions].TransactionDescription,
[Purchase Orders].PurchaseOrderDescription, [Inventory
Transactions].UnitPrice, [Inventory Transactions].UnitsOrdered,
[UnitsOrdered]*[Inventory Transactions]!UnitPrice AS [TOTAL COST],
Accounts.AccountFund, Accounts.AccountOrg, Accounts.AccountAcct,
Accounts.AccountProg, Suppliers.SupplierName, Suppliers.Address,
Suppliers.Address2, Suppliers.City, Suppliers.StateOrProvince,
Suppliers.PostalCode, Suppliers.ContactName, Suppliers.PhoneNumber,
Suppliers.FaxNumber, [Purchase Orders].PayableCheck, [Purchase
Orders].PurchaseOrder, [Purchase Orders].MailCheck, [Purchase
Orders].[Pick-upCheck]
FROM (Suppliers INNER JOIN [Purchase Orders] ON Suppliers.SupplierID =
[Purchase Orders].SupplierID) INNER JOIN (Products INNER JOIN (Accounts
RIGHT
JOIN [Inventory Transactions] ON Accounts.AccountID = [Inventory
Transactions].AccountID) ON Products.ProductID = [Inventory
Transactions].ProductID) ON [Purchase Orders].PurchaseOrderID = [Inventory
Transactions].PurchaseOrderID
GROUP BY [Purchase Orders].PurchaseOrderID, [Purchase
Orders].PurchaseOrderNumber, [Purchase Orders].OrderDate,
Products.ProductName, [Inventory Transactions].TransactionDescription,
[Purchase Orders].PurchaseOrderDescription, [Inventory
Transactions].UnitPrice, [Inventory Transactions].UnitsOrdered,
Accounts.AccountFund, Accounts.AccountOrg, Accounts.AccountAcct,
Accounts.AccountProg, Suppliers.SupplierName, Suppliers.Address,
Suppliers.Address2, Suppliers.City, Suppliers.StateOrProvince,
Suppliers.PostalCode, Suppliers.ContactName, Suppliers.PhoneNumber,
Suppliers.FaxNumber, [Purchase Orders].PayableCheck, [Purchase
Orders].PurchaseOrder, [Purchase Orders].MailCheck, [Purchase
Orders].[Pick-upCheck]
HAVING ((([Purchase Orders].PurchaseOrderID)=1));
Following is the SQL for the accounts query:
SELECT Accounts.AccountID, Accounts.AccountNumber, [Inventory
Transactions].UnitPrice, [Inventory Transactions].UnitsOrdered,
[UnitsOrdered]*[UnitPrice] AS Total, [Inventory
Transactions].PurchaseOrderID
FROM Accounts RIGHT JOIN [Inventory Transactions] ON Accounts.AccountID =
[Inventory Transactions].AccountID
GROUP BY Accounts.AccountID, Accounts.AccountNumber, [Inventory
Transactions].UnitPrice, [Inventory Transactions].UnitsOrdered, [Inventory
Transactions].PurchaseOrderID;
Thank you for your help.
--
Pete P.
Dale Fye said:
Pete,
The implication here is that you have either have duplicate
PurchaseOrderID
values in the [Accounts Query] or in the [Purchase Orders Query]. Post
the
SQL for each of those queries to assist us.
Dale
--
Email address is not valid.
Please reply to newsgroup only.
Pete P. said:
I use a query to generate individual P.O.s for ordering items. Usually
each
order has only one acct.#, but ocasionally there are multiple acct. #'s
and I
need to know the totals for each acct.#. The query that I use for this
purpose uses the following SQL:
SELECT [Accounts Query].Total, [Accounts Query].AccountNumber,
[Accounts
Query].AccountID
FROM [Accounts Query] INNER JOIN [Purchase Orders Query] ON [Accounts
Query].PurchaseOrderID = [Purchase Orders Query].PurchaseOrderID
ORDER BY [Accounts Query].AccountNumber;
When I run the query it gives me 2 entries for each item recorded in
the
specified P.O. (based on the P.O. ID that I've saved in the Purchase
Orders
Query). If I use the sum function to create a total dollar figure it
returns
a figure that is 2, 3, or 4x's greater than it should based on the
number of
entries in the original P.O.
How can I re-write the SQL to retrieve only single entries and get the
proper TOTAL for my Account Query Total? Thanl you for any suggestions
given.