Beginner query question

[RET79]

I got two tables.

One has the following data:

ID, Premium, PremiumDate, DeathAmount, DeathDate, IncomeAmount, Incomedate

The next table just has a column with dates from 1/1/98 to 1/1/2005

I want to join the tables so that you have dates on left hand side and amounts coming out on the rhs when applicable.

I'm struggling to set this up with design view. I've tried linking the list of dates with the PremiumDate, DeathDate, IncomeDate but it fails to show any records but works if I just link up the premium date however then it just shows one date and wrongly allocated values to the death and income as they don't come out that day

 

[Gregc]

Are you trying to figure out amount by date? If there were 10 payments on 1/1/04 you want to know the sum of the 10 payments on that date?

 

[RET79]

Assume for now there is only one payment per date. I want all the dates listed on the LHS then the premium appearing next to the date it's paid in only.

 

[Gregc]

I'm getting closer. I think. You want all date 1/1/?? through 12/31/05 from your date table to show up? Next to the dates you want all the information to show up next to the date the premium was paid?

 

[RET79]

I want a table with 5 columns.

1st column showing all the dates from 1/1/98 to 1/1/05
2nd column showing the ID (which will be the same all the way down)
3rd column showing premium amount but only next to the date it was paid
4th colum showing death benefit amount but only next to the death date
5th column showing income amount but only next to the dates it was paid out.

Does this make sense? Then a 6th column could be col3-col4-col5 = change in fund value, say.

The idea is to run all the ID's through this table and record the fund value at different dates, which will probably require a macro.

 

[Gregc]

I can get you close. You need to make a query that has the date table and the other other table, no links between the two.

Make a query query2 with Query1 and Premium table in it. Join the two from query1 to Premium Table with a join between date and premium date with all the records from Query 1 and only records from premium table where joined fields are equal. In this query put ID, and Premium date from Query1 and Premium from Premium table.

Then make a third query and use query2 and Premuim table with the same type join, joining death date this time. In this query put Date, ID, Premium from Query2 and Deathamount from premium table.

Keep going with this query a query thing until you have all the things you want. This puts the amounts only next to the dates paid.

I think this is what you are looking for. I tried it with two records in the table and you get a ton of records. Let me know if this is what you are looking for.

Greg