help on calculation please

[earnshaw]

Hi there I have been trying to figure this out for ages but just keep getting incorrect figures. please help me.....

I have two calculations in a report that I am trying to combine together. I have got the two fields working which are

1)the total number of orders taken ** =Count(IIf([Call Generated Order]="Yes",0))+Count(IIf([Call Generated Order]="Would have placed",0)) **

2)the total number of payments taken ** =Count(IIf([Payment Value]>".0",0)) **

I am trying to work out the total number of records that placed an order and made a payment at the same time (not add the two together)

Many thanks for any help

cheers Jay

 

[MichaelRed]

not any actual help here, just a suggestion.

" ... at the same time ... " is essntaily meaingless in a db. At least you need the date / time of the transactions (order placement and Payment received). Assuming these are seperatly available, there is virtually no possability they would be the same, so " ... at the same time ... " must be changed to " ... within {some interval} of each other ... "

Additional requirements are to be able to tie the payment to the order, In general, this will require that the payment be related (or at least relatable) to the order, usually through the generic mechanisim refered to as a foregin key (unique order id in the payment table) adnd your expression would need to have the order id in the payment table exactly equal hte order id in the order table.

In general, the question you pose is usually 'answered' via a query using the conseptual relationships noted above. The specific syntax you post suggest thast the expressions are the control source of text boxes on forms (or reports). This in turn impllies that you are not using a query to get the information. I would not attempt to use an expression in a control source to get the information on relational elements of a database. Not to say it is not possible, but it does seem like the hard way to do it.




MichaelRed

 

[earnshaw]

Hi MichaelRed, I am using a query in the database on call type and campaign of order, then producing a report on the queried data, its the report fields (text boxes)that I am adding the formula's to

cheers jay