Query one record from a different table

[vb6rookie]

I have a drill down report that returns several records from 2 tables. In the group footer after each section I want to query another table for one record for the same date.
Neither
IF {tsFinancial.CODE} = "2013" then {tsFinancial.AMOUNT}
OR
IF {tsFinancial.CODE} = &quot;2013&quot; AND {tsUsageVariance.BusinessDate} >= DATETIME(2001, 10, 8, 0, 0, 0) AND {tsUsageVariance.BusinessDate} < DATETIME(2001, 10, 15, 0, 0, 0) then {tsFinancial.AMOUNT}

Returns data. How can I extract the info from tsFinancial when the main guts of the data comes from tsUsageVariance?

 

[databaseman]

I hope that I am reading your meaning correctly, if not, do please forgive me.
Are you linking the tsFinancial table to tsUsageVariance? Or do you want the one field as an unlinked field? Are you certain that {tsFinancial.CODE} does = &quot;2013&quot;?

In any case, there are several options. A subreport may be your best bet.

I am not clear enough on what you are trying to do. Can you reply with more info about what the report needs to do?

Thanks Database Man
Defender of Life, Liberty and the pursuit of accurate database reporting! Follow my free tek-tip adventures at:

http://www.hammerman.com/dbman.html

 

[vb6rookie]

The main part of the report comes from tsUsageVariance and tsInventory. This returns several records for a date then &quot;starts over&quot; on the next business date.
For the same date I need to find 2013 in the code field of tsfinancial and when I find it &quot;capture&quot; the &quot;amount&quot; field
and use that data to calculate a percent on the running total from one of the fileds in the tsUsageVariance
i.e. from tsUsageVariance and tsInventory
meat usage $50
chicken usage $60
milk usage $5
Total usage $115

from tsfinancial
sales $3,500

formula result = $115/$3500 = %3.28