double entries for each record when running a query

[Pete P.]

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.

 

[KARL DEWEY]

I do not get the double display. Post sample data.

 

[Dale Fye]

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

 

[Pete P.]

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.

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.

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.

 

[Dale Fye]

Pete,

My guess is that if you run a Find Duplicates query on each of these
queries, using only PurchaseID to determine the duplicates, you will find
that you have numerous instances where you have duplicate purchase IDs.
This is because you are grouping on numerous fields.

HTH

Dale

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.

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.

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.