Get data from two excel worksheets 1

[Deb100]

How do I get data from two Excel worksheets I need the curr_price from tblPrice when a record is selected from tblDay. I have linked them in the Database Editor with the field 'Stock' which is on both worksheets.

I am using the following formula to pick up the current price:

if {'tblDay'.Details} = ("Purchase") then {'tblPrice'.Curr_Price}

This works perfectly for 1 stockholding, but not the 20 or so others. I cannot see why it works for one and not the others.

When I browse the field data it only shows 8.45 (the one that I am getting) and 0.00. None of the other prices are listed in the browse field box.

I have Verified the database but it made no difference.

My Group Selection is
Maximum ({'tblDay'.Details}, {'tblDay'.Stock}) = "Purchase"

My Record Selection is
{'tblDay'.Details} in ["Purchase", "Sold"]

I would be grateful for any help you can give me.

Regards

 

[lbass]

What are you trying to do? Your group selection formula will only result in a display of those stocks that have only a purchase, since the maximum of ["purchase","Sold"] is "Sold". The only time "Purchase" would be the maximum is if there is no "Sold" detail.

-LB

 

[Deb100]

Hi lbass

Thanks for your response. What do you mean by "maximum of ["purchase","Sold"] is "Sold""? I don't really understand how 'maximum' works.

Deb100

 

[lbass]

I'm saying that your group selection formula:

Maximum ({'tblDay'.Details}, {'tblDay'.Stock}) = "Purchase"

...will ONLY return groups where there is a value "Purchase" and no "Sold" value, because if you insert a maximum on a text field, the maximum will be based on which value is later in the alphabet. For "Purchase" to be the maximum, there would have to be no records with "Sold" in the stock group.

Why did you use this group selection formula?

-LB

 

[Deb100]

Hi lbass

I understand what I have done wrong now. Thank you so much for your help.

Regards

Deb100