Query not working as planned

[themastertaylor]

I received some help a while ago setting up a system whereby orders
placed and payments made were logged into a system to display the
remaining amount of credit with each supplier. for example if we have
a credit limit of £1000 orders totalling £7000 and £2000 is paid on
the account the credit remaining is displayed as £5000

i thought i had this working but today noticed a problem, the total
value of orders is working fine, however where there are multiple
orders with a supplier the payments are included as many times as
there are orders - i.e. if there are five orders with supplier a - a
payment of £200 is coming up as £1000.


the tables in use are named orders and payments. i have a query on the
orders table "nmorders" with a calculated value of quantity 1 x rate +
quantity 2 x rate2 etc for multiple items. the field is named value
and displays correctly. this is sorted by supplierid


on payments table i have a query to display payment ammount and
supplier id, again sorted by supplierid.


then i have a third query based on these queries and my orders table
called "nmordersquery". supplier id is taken from both queries,
payment amount is taken from nmpayments and value from nmorders. i
then have these figures summed with the following expression
respectively


Sum Of value: Sum(Nz(nmorders.value))
Sum Of Payment Amount: Sum(Nz(nmpayments.[Payment Amount]))


i also have a type 2 join between the queries and supplier table to
ensure all suppliers are included.


from this information can anybody work out how to stop the payments
being entered multiple times? by the looks of things each order has the supplier id in its data, as such when they are all summed the id is being picked up multiple times, the relationship seems to be transfering this to the payments table i.e. if supplier id is present 5 times in orders, each payment is being picked out 5 times. each individual query works fine its the query based on the other 2 thats causing the problem.


Thanks in advance

 

[neileg]

Without seeing your table structures it's a bit tricky to advise.

 

[themastertaylor]

Without seeing your table structures it's a bit tricky to advise.

suppliers table is as follows

supplierid
name
credit limit

payments is as follows

paymentid
supplierid
payment amount

orders is as follows (item quantiy and rate have identicle fields for item 1 to 6 etc)

orderid
supplierid
siteid
item1
quantity1
rate1

sites table is as follows

siteid
site name
address1
address2
address3
address4

all the relationships are by the relevant id

the query nmorders sums the orders for the suppliers by multiplying rate and quantity fields as stated in the first post.

query nmpayments picks out all the payments to each supplier

and the third query combines them to show total payments and total value of orders for each supplier , but here as there are multiple orders the payments are being copied multiple times, i've included the sql for each below

nmorders

SELECT Orders.supplierid, Orders.Quantity, Orders.Rate, Orders.Quantity2, Orders.Rate2, Orders.Quantity3, Orders.Rate3, Orders.Quantity4, Orders.Rate4, Orders.Quantity5, Orders.Rate5, Orders.Quantity6, Orders.Rate6, ([quantity]*[rate])+([quantity2]*[rate2])+([quantity3]*[rate3])+([quantity4]*[rate4])+([quantity5]*[rate5])+([quantity6]*[rate6]) AS [value]
FROM Orders
ORDER BY Orders.supplierid;


nmpayments

SELECT payment.supplierid, payment.[Payment Amount]
FROM payment
ORDER BY payment.supplierid;

nmordersquery

SELECT DISTINCTROW Sum(Nz(nmorders.value)) AS [Sum Of value], Sum(Nz(nmpayments.[Payment Amount])) AS [Sum Of Payment Amount], Suppliers.[Credit Limit], Suppliers.Name
FROM (Suppliers LEFT JOIN nmpayments ON Suppliers.SupplierID = nmpayments.supplierid) LEFT JOIN nmorders ON Suppliers.SupplierID = nmorders.supplierid
GROUP BY Suppliers.[Credit Limit], Suppliers.Name, nmorders.supplierid, nmpayments.supplierid
ORDER BY Suppliers.Name;

i realise the payments query could be removed and the table used, however i had it in the system previously to sum the payments for each supplier (as multiple payments will be made) but any null values weren't being found by nmordersquery and left blank, this caused problems in my calculated field for credit remaining on a form.