Table with Different Data Types

[ADRIAN.CANTU]

I have a table with account opening balances by year. So for account 100-cash i have a opening balance of $200 for 1999, for $500 in 2000 and so on. Additionally i have a table that has monthly transitional data. So for account 100-Cash i have $50 worth of transactions in Jan, $(20) in Feb and so on. My problem in i want to combine these tables into one. I would like to show like this: Account Opening Bal Jan Bal Feb Bal ......

any ideas?thanks.

ac

 

[xenou]

Try this:

• Write a query to sum the monthly transactions from the transactions table (unless it is already summed up by account/month).
• Write a query to join monthly transaction summary by account to the opening balance by account
• Crosstab the results.

You need to be sure you have a way to join the tables properly so that the opening balance is for the correct year - each query should have year and month or fiscal period identifiers, and should have only one record per fiscal period per account. All tables are generally joined on Year/Month/Account (unless the fiscal period is the month ending date, in which case its FiscalPeriod/Year).

It may be useful to create a form where you can enter a year parameter so that you can use the same query for any year that you'd like to see.

 

[ADRIAN.CANTU]

thanks i ll give it a try