Complex Query (to me it is)

[leewisener]

I have all my data in 1 table, it contains account that are in arrears with their payments.

Every day about 6,000 records are updated into the table so we can compare movemements from day to day.

What I want to do is produce a query that takes the position of all cases allocated to each collector on the 1st of each month so I can display the total each collector had available to collect on that day.

Then I want to have a column that looks up today so I can compare if each collectors accounts are increasing or decreasing.

I can get the first set of figures no problem to display the position as at the 1st but I cant get the query to then pick out all the accounts for each collector today and display a total for each.

The fields I have are:

Date
Account Number
MIA (months in arrears)
Payment Due
Total paid this month so far
Amount Overdue
Collectors Name

Can anyone help?

 

[Remou]

Can you post the SQL for the first set of numbers, please?

 

[leewisener]

This generates the main part, names, amount due at the beginning of the month and amount paid so far, really I want the amount paid so far to be using the figures from 04/08/06

SELECT data.Date, data.[Current Collector], Sum(data.[Arrears Amount]) AS [SumOfArrears Amount], Sum(data.[Paid This Month]) AS [SumOfPaid This Month]
FROM data
GROUP BY data.Date, data.[Current Collector]
HAVING (((data.Date)=#8/3/2006#));

 

[Remou]

I still don't get it, I'm afraid. Is a new row added to the table duplicating most of the data every time an amount is paid, with an appropriate paid date?

 

[leewisener]

Yes.

Every day a a download comes to me in the form of an excel spreadsheet which I upload. Many rows will be identical, the only difference being the date in the first column.

If someone pays then obviously the date paid, arrears amount and possibly the Months in arrears will change.

 

[lespaul]

can you post some sample data and your expected results from that data?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[leewisener]

for example:

Date Collector SumOfArrears SumOfPaid
03/08/06 Agent1 579603.74 28755.85
03/08/06 Agent2 669152.28 26773.84
03/08/06 Agent3 823692.36 45458.18
03/08/06 Agent4 569671.02 10905.02
03/08/06 Agent5 326419.56 45422.96
03/08/06 Agent6 639465.39 21336.66

That is the output to get the total amount of arrears outstanding at the beginning of the month for each group of collectors accounts (SumofArrears).

This also show the amount paid so far in the month (SumofPaid).

I enter the date of the 3rd of the month which is where we start from, so SumofArrears is correct and I dont want that figure to change through the whole of August.

Its the SumofPaid thats not right. I need the query to look at all the records uploaded today and count up the total amount that has been paid to date on the accounts.

Any better sense?

 

[lespaul]

so if you were to put this in two queries, one for SumofArrears and one for SumofPaid, the SumofArrears query and criteria would be:
[tt]
SELECT data.[Date], data.[Current Collector], Sum(data.[Arrears Amount]) AS [SumOfArrears Amount]
FROM data
WHERE Data.[Date] = #8/3/2006#
GROUP BY data.Date, data.[Current Collector];
[/tt]
what would the query and criteria be for SumOfPaid query below?
[tt]
SELECT data.[Date], data.[Current Collector], Sum(data.[Paid This Month]) AS [SumOfPaid This Month]
FROM data
WHERE ?????
GROUP BY data.Date, data.[Current Collector];[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Remou]

Just a thought, if this data is being duplicated line by line, perhaps there is a max of month paid to date which can be use to compare with a min (first record)?

 

[leewisener]

what would the query and criteria be for SumOfPaid query below?

SELECT data.[Date], data.[Current Collector], Sum(data.[Paid This Month]) AS [SumOfPaid This Month]
FROM data
WHERE ?????
GROUP BY data.Date, data.[Current Collector];

It would be:

SELECT data.[Date], data.[Current Collector], Sum(data.[Paid This Month]) AS [SumOfPaid This Month]
FROM data
WHERE Data.[Date] = date()

GROUP BY data.Date, data.[Current Collector];

Easy enough as two queries, but getting the info from 1 query is the challenge.

Just looking at the data table again the accounts are all assigned on the first day of collections, i.e 3/08/06, I only have a value assigned to [Collector Name] on 1 date.

 

[lespaul]

so you want to sum where [DATE] = today?

Leslie

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[leewisener]

Yes, thats right but I cant figure it out in the query, I can get various results where I create the query but none that give the right figures.

 

[lespaul]

try this:

SELECT data.[Date], data.[Current Collector], Sum(data.[Arrears Amount]) AS [SumOfArrears Amount], b.[Paid This Month] As [SumOfPaid]
FROM data
INNER JOIN (SELECT [Current Collector], Sum([Paid This Month]) FROM data WHERE [Date] = #8/4/2006#) As b.[Current Collector] = data.[Current Collector]
WHERE Data.[Date] = #8/3/2006#
GROUP BY data.Date, data.[Current Collector];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[leewisener]

Geeting an error at the bold point below

SELECT data.[Date], data.[Current Collector], Sum(data.[Arrears Amount]) AS [SumOfArrears Amount], b.[Paid This Month] As [SumOfPaid]
FROM data
INNER JOIN (SELECT [Current Collector], Sum([Paid This Month]) FROM data WHERE [Date] = #8/4/2006#) As b.[Current Collector] = data.[Current Collector]
WHERE Data.[Date] = #8/3/2006#
GROUP BY data.Date, data.[Current Collector];

 

[Golom]

SELECT data.[Date], data.[Current Collector], Sum(data.[Arrears Amount]) AS [SumOfArrears Amount], b.[[COLOR=red]SPaid[/color]] As [SumOfPaid]
FROM data
INNER JOIN (SELECT [Current Collector], Sum([Paid This Month])[COLOR=red] As [SPaid][/color] FROM data WHERE [Date] = #8/4/2006#[COLOR=red] Group By [Current Collector]) As b ON [/color]b.[Current Collector] = data.[Current Collector]
WHERE Data.[Date] = #8/3/2006#
GROUP BY data.Date, data.[Current Collector][COLOR=red], b.[SPaid][/color];

Do you really mean to have two different dates in the main query and the sub query?

 

[leewisener]

I solved it eventually and got exactly what I require by doing this. I changed things about a bit that actually will help with other things in the future.

SELECT [Start Date].Collector, [Start Date].Start, Current.Current, [Start]-[Current] AS Movement, [Movement]/[Start]*100 AS [% Collected]
FROM [Start Date] INNER JOIN [Current] ON [Start Date].Collector = Current.[Current collector]
GROUP BY [Start Date].Collector, [Start Date].Start, Current.Current;

 

[lespaul]

yes according to the above posting, the OP wanted two different dates. My bad on the alias, thanks for correcting that golom!!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi