will a form work?

[themastertaylor]

I'm working on a system to log the orders we place with suppliers and
in addition to monitor the value of orders placed in respect to our
credit limit with that supplier. I'm trying to produce a form that
didplays all of my suppliers and the remaining value of credit. I can
display the credit remaining using an unbound text box with the
following code.

= ([Credit Limit])-DSum("[quantity]*[rate]","[orders]","[supplierid] =
Form![SupplierID]")+DSum("[payment amount]","[payment]","[supplierid]
= Form![SupplierID]")


this basically sums the value of the orders for each supplier and
reduces the credit limit, the payments bit refers to payments made
which will obviously increase the credit limit.


the form generated returns the credit remaining values but if there
are multiple orders with the same supplier i get duplicate entries for
the remaining credit with that supplier.


in a usenet grup i was told i had 2 options, a form and subform
approach or using select distinct. I have tried running a query on my
supplier table to return
id, name and credit limit. then i placed these fields in a form,
using a subform to calculate the credit remaining by modifying the
code to reference the information from the form. this works fine for
the first supplier, but my subform doesn't update when i move to the
next record. in addition it looks a though if this method works i
would only see 1 supplier at a time, using the previous method (with
duplicate suppliers returned) i could view the form as a datasheet
and
show all my suppliers on one screen, which is the ideal scenario for
the system. could you please advise if this outcome is possible
using
a form/subform approach.


as for using SELECT DISTINCT i read around on it and really can't get
it to work, i'm thinking that the supplier id needs to be distinct
from the orders table, and tried to enter select distinct in pretty
much every position possible within my code but with no success. i
either returned #name or #error

 

[Tim L]

I'm not sure if you need to rethink the construction of your db, but what you are trying to do would probably be best done by a series of queries.

First, put a query together which totals up all of the outstanding payments that you have to make, group it by the suppliers.

Next, do the same for the payments that you have made, excluding any for orders that are not current.

Finally, you should be able to combine the information from both of these to get an overall positive/negative value and the credit limit plus other information.

HTH

Tim

 

[themastertaylor]

I'm not sure if you need to rethink the construction of your db, but what you are trying to do would probably be best done by a series of queries.

First, put a query together which totals up all of the outstanding payments that you have to make, group it by the suppliers.

Next, do the same for the payments that you have made, excluding any for orders that are not current.

Finally, you should be able to combine the information from both of these to get an overall positive/negative value and the credit limit plus other information.

HTH


works a treat thanks a lot for the help