Calculating using combobox values

[GoatieEddie]

Hi all,

I've got my knickers in a bit of a twist over this one and now can't see the wood for the trees!

I have a table that has three cols - name, value, date.

I then have a combo box that allows you to enter two dates.

I have a select query that takes the combo values and returns the values for each name for both the dates.

Where I get stuck is I want to show the percentage difference between these two values for each name. I can't work out how to reference the two value columns separately as they have names that change depending on what dates I select.

Can someone shed the light?!

Many thanks in advance,

GE

 

[Remou]

Hi
I think I must be missing some point. Why can you not find date 1 in the recordset and store the value for that date to a variable, then do the same for date 2?

 

[PHV]

Why not posting your actual SQL code, actual result and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GoatieEddie]

This is the SQL code for the select query. It produces a table with name, date, value = providing two values per name effectively. It works fine:

PARAMETERS [Forms]![Enter Dates To Compare Values]![CB_From] DateTime, [Forms]![Enter Dates To Compare Values]![CB_To] DateTime;
SELECT [T-Values History].[Ptf-Customer + Portfolio Number], [T-Values History].Date, Sum([T-Values History].Value) AS SumOfValue
FROM [T-Values History]
GROUP BY [T-Values History].[Ptf-Customer + Portfolio Number], [T-Values History].Date
HAVING ((([T-Values History].Date)=[Forms]![Enter Dates To Compare Values]![CB_From] Or ([T-Values History].Date)=[Forms]![Enter Dates To Compare Values]![CB_To]));

I have also successfully created a crosstab query.

PARAMETERS [Forms]![Enter Dates To Compare Values]![CB_From] DateTime, [Forms]![Enter Dates To Compare Values]![CB_To] DateTime;
TRANSFORM Sum([T-Values History].Value) AS SumOfValue
SELECT [T-Values History].[Ptf-Customer + Portfolio Number]
FROM [T-Values History]
WHERE ((([T-Values History].Date)=[Forms]![Enter Dates To Compare Values]![CB_From] Or ([T-Values History].Date)=[Forms]![Enter Dates To Compare Values]![CB_To]))
GROUP BY [T-Values History].[Ptf-Customer + Portfolio Number]
PIVOT [T-Values History].Date;

However the dates become the column headings and these can change each time the user wants them to and so I can't see how to use as in a generic formula. What I want is to add a third column with the % change. Surely I don't need to create two tables first with hard coded column headings?