Creating master table to summarize data on a monthly basis

jturn00

Board Regular
Joined
Jul 21, 2004
Messages
80
Hi,

I currently have data in two tables. In one table is the master record for a loan. It has fields like name, id, loan id, loan date, and amount.

The other table has details on the amounts given out to the person. since the start of the loan. A loan period is 10 years. I wanted to create a table (or query) that puts the amounts in monthly "buckets" from the loan start date so I can trace the disbursements. I want to do this in a table or query so I can run some other calculations. Like possibly grouping the monthly periods into annual periods.

I am also not sure what is the first date of the first loan in the data so how would the table / query be flexible if I say need to start in 1984 but go through 2004 or later?

Thanks,
Jeff
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

cstlaurent

Board Regular
Joined
Jan 14, 2005
Messages
182
you need to create a querry that will look like that, is a

SELECT Table1.nom, Format([tr_date],"""yyyy""/mm") AS [date], Sum(Table2.tr_montant) AS SommeDetr_montant
FROM Table1 INNER JOIN Table2 ON Table1.customer_ID = Table2.custumer_ID
GROUP BY Table1.nom, Format([tr_date],"""yyyy""/mm")
HAVING (((Format([tr_date],"""yyyy""/mm")) Between [forms]![frmxy]![txtstart] And [forms]![frmxy]![txtend]));

it will regroup by month and customer the amount of money received

good luck
 

Forum statistics

Threads
1,148,396
Messages
5,746,452
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top