Making a table with titles for rows and columns

[djagla]

I am trying to match up information from two columns on one table to information from the intersection of a column and a row on the other table. By matching up the column and the row, the intersection of these two will give me one number which is the number that I am looking for Access to generate.

Example. If the maturity (column 1 table 1) is 2022 and the trade date is 10/1/2001 (column 2 table 1) then I want to match it up with the maturity (row 1 table 2) of 2022 and the trade date of 10/1/2001 (column 1 table 2) to get the piece of information that is the intersection between the row and column in table 2.

I am wondering both how to design the table and what the query would be in order to match up the information.

Thanks!

 

[mcalex]

By matching up the column and the row, the intersection of these two will give me one number which is the number that I am looking for Access to generate

This sounds more like spreadsheet work than a database.

Is the answer calculated? ie can you do a calculation on trade date and maturity to get the number you want or is it simply looking up a value that has no connection to the fields? If so you might be able to solve this by performing that calculation, instead of looking it up.

 

[djagla]

There is is a relation between the maturity date, trade date, and the number I am trying to look up, but it is not a calcuable relation.

How would I perform the calculation is this relationship did exist?

 

[mcalex]

umm, your query would probably look something like
select maturity, trade_date, ((maturity - trade_date) * interest) as the_answer
from my_table
where trade_date < date()

where interest is a number
assuming the answer is calculated by subtracting the trade date from maturity to get the number of years to multiply by some interest rate, and you're only interested in trade_dates before today. actually, you need to get the year out of trade_date to make it work properly, but i digress.

OTOH, and it might not be proper to say it in the forum, but maybe a spreadsheet is your solution?

 

[gemma-the-husky]

in any table dlookup basically retrieves a value

with this sort of syntax

dlookup("requiredcolumn","requiredtable/domain","identifier for row required")

similar ot dlookup/vlookup (i forget which) in excel