Logic Required! - Connecting Quarterly Revenue With Calendar Years 1

[dominicdunmow]

Hi there,

I am trying to tie together fields from 2 tables, these are simplified below. (Quarters refer to calendar quarters)

Table 1:

ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
--- -- -- -- -- -- -- -- --
1 4 6 3 6 2 7 4 8
2 4 6 2 4 8 9 6 7
3 5 8 3 2 6 4 6 7

Table 2:

ID Due Date(yyyy) Due Date(Q) Contract Term
-- -------------- ----------- -------------
1 2005 Q2 2
2 2005 Q3 2
3 2006 Q4 2

The "Q" fields in Table 1 refer to payments each quarter after the due date. E.g. "Q1" in the case of "ID1" actually refers to Q3 2005 (but its the first quarterly payment). This may seem a little strange - but constraints forced me to work this way.

My problem is that I have to provide total revenue per year figures, so the total revenue to be recouped in 2005, 2006 etc. The difficulty is designing a query field that can determine which quarterly payments belong to which year. In reality I have 20 quarters and not 8 which makes things a little more long winded.

Can anyone help me with the logic required in a query field to accomplish this please?

 

[GingerR]

Hi there--I highly suggest that you normalize your first table. You can see already the kinds of problems you will come up against for the rest of this db's existance.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Great advice GingerR. Too many times advice is handed out that works "around" un-normalized or poor table structures. The only thing I would add is if the table structure can't be changed, you might be able to normalize by creating a union query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]