Same data - different criteria

[Jay974 via AccessMonster.com]

Hi Guys,

I am trying to produce a query based on balances of accounts with my company.
The data in the table contains the following information: Week Number,
Balance and Product Definition ID.

The queries I have designed so far (SQL below) do the following

1. This query splits the data into different product types (Product
Definition ID)

2. This query determines where each of the accounts from query 1 sit in the
balance tiers and assigns the tier name

3 This query displays the tiers, the sum of all balances on that tier, the
total number of accounts on that tier and the interest rate of the tier
(stored in another table).

What I have now been asked to do is produce a query which shows this
information over the past 4 weeks, so the columns would be tier, sum of
balances 3 weeks ago, count of accounts three weeks ago, sum of balances two
weeks ago..... and so on right up until the present week.

Is there anyone who can advise the easiest way to do this?

Thanks


SELECT qry_BUSBTiers.Expr1 AS Tier, tbl_Tiers.TierBalances, Count
(qry_BUSBTiers.ID) AS [Number], "£" & Round(Sum([Bal]/1000000),2) & "M" AS
Balance, tbl_Tiers.TierInterestRate
FROM qry_BUSBTiers INNER JOIN tbl_Tiers ON qry_BUSBTiers.Expr1 = tbl_Tiers.
TierName
GROUP BY qry_BUSBTiers.Expr1, tbl_Tiers.TierBalances, tbl_Tiers.
TierInterestRate;

 

[Jay974 via AccessMonster.com]

Sorry, SQL for query 1

SELECT CustomerBalanceTracking.ID, CustomerBalanceTracking.Bal,
CustomerBalanceTracking.[Product Definition Id], CustomerBalanceTracking.
WeekNumber
FROM CustomerBalanceTracking
WHERE (((CustomerBalanceTracking.[Product Definition Id])=10016 Or
(CustomerBalanceTracking.[Product Definition Id])=10026 Or
(CustomerBalanceTracking.[Product Definition Id])=14006));


SQL for Query 2

SELECT qry_BUSB.ID, BUSBTiers([Bal]) AS Expr1, qry_BUSB.Bal, qry_BUSB.
WeekNumber
FROM qry_BUSB;