Difficult query

[Ruth Isaacs]

Hello All

I am really struggling to get the output I need from 3 tables in my db.
Essentially, I need to report on months in which a payment has not been
received (by the 'practice') at all, or it has been for the wrong amount.

I have the following tables:

[x confirmed]
[practices]
[payedoc accounts]

[practices] has key field 'prac name', which is related to field 'practice'
in table [x confirmed] and also to field 'practice' in table [payedoc
accounts].
[practices] also has currenc fields 'fee' and 'newfee'.
[x confirmed] also has integer field 'month number', related to 'mth' in
table [payedoc accounts].
[payedoc accounts] also has currency field [credit].

A payment is due for a given 'practice' and 'month number' if there is one
or more records in [x confirmed] with the corresponding values in fields
'practice' and 'month number'. The amount of the payment due is in table
[practices], by the value in 'newfee' if 'month number' is < 85 otherwise by
the value in 'fee'.

Field 'credit' in table [payedoc accounts] contains the payments that have
been received, so it is this field that needs to be compared to 'newfee' or
'fee' (depending on the value of 'month number') for each 'practice'.

Can anyone help with this?
Ultimately need a report that, for any missing or incorrect payments, will
show (grouped by Practice) the Practice name, the month number, the fee due,
the fee received and the (thus) the discrepency.

I have wrestled with this for days now, and can't get it right, so would be
very grateful if anyone can assist.
If anyone is able to help but feels that it would take longer than they
could reasonably spend on it for free, I would be prepared to pay a
(reasonable!) fee for the complete solution.

Many thanks
Leslie Isaacs

 

[David S via AccessMonster.com]

OK, let's break this down:
Table [x confirmed], Fields [prac name], [month number] - shows which
practices have fees due
Table [practices], Fields [practice], [fee], [newfee] - shows what those fees
are
Table [payedoc accounts], Fields [practice], [mth], [credit] - show what has
been paid to those practices

Are you familiar with SQL at all? It's easier posting that in this forum than
it it trying to describe Query Designer actions, but hopefully you should be
able to map from one to the other.

First, let's set up a [FeesDue] query that looks like:
SELECT [practice], [month number], iif ([month number] < 85, [newfee], [fee])
as [fees due]
FROM [x confirmed], [practices]
WHERE [practice] = [prac name]

You can then join this up with the credit table in a [Account] query:
SELECT [FeesDue].[practice], [FeesDue], iif ([credit] is null, 0, [credit])
as [FeeReceived], iif ([credit] is null, [FeesDue[, [FeesDue] - [credit]) as
[FeesOutstanding]
FROM [FeesDue] LEFT JOIN [payedoc accounts]
WHERE [FeesDue].[practice] = [payedoc accounts].[practice]
AND [FeesDue].[month number] = [payedoc accounts].[mth]

I may not have the syntax exactly right there - I don't have Access on this
PC just now.

That will be the base of it - note that this query won't show practices who
have received a payment but not charged anything. To add those in, you will
need a separate set of queries that you then join up to the one above using a
UNION query. Let us know how it goes.

 

[Leslie Isaacs]

Hello David

Many thanks for your reply.

I am 'slightly' familiar with sql!
I set up the first query [FeesDue] as you suggested, and that worked fine.
When I set up the second query [Account] I got "Syntax error in FROM
clause", which I could not correct.

(I did change the following:
iif ([credit] is null, [FeesDue[, [FeesDue] - [credit]) as [FeesOutstanding]
to
iif ([credit] is null, [FeesDue], [FeesDue] - [credit]) as [FeesOutstanding]
but that made no difference)

I then created tried the following:

SELECT IIf([credit] Is Null,0,[credit]) AS FeeReceived, IIf([credit] Is
Null,[Fees Due],[Fees Due]-[credit]) AS FeeOutstanding, FeesDue.practice,
FeesDue.[month number], FeesDue.[fees due]
FROM FeesDue INNER JOIN [payedoc accounts] ON (FeesDue.[month number] =
[payedoc accounts].mth) AND (FeesDue.practice = [payedoc
accounts].practice);

which does seem to work , but - as I think you said - it does not pick up
those records returned by query [FeesDue] where there is no corresponding
record at all in table [payedoc accounts] - i.e. I am only finding 'wrong'
fees', not 'missing' fees. I'm afraid I cannot see how to get at these, and
I am not confident how to use union queries.

Two questions:
1. how is my second query different from your intended second query?
2. where do I go from here?

Many thanks for your continued help

Regards
Leslie isaacs

OK, let's break this down:
Table [x confirmed], Fields [prac name], [month number] - shows which
practices have fees due
Table [practices], Fields [practice], [fee], [newfee] - shows what those fees
are
Table [payedoc accounts], Fields [practice], [mth], [credit] - show what has
been paid to those practices

Are you familiar with SQL at all? It's easier posting that in this forum than
it it trying to describe Query Designer actions, but hopefully you should be
able to map from one to the other.

First, let's set up a [FeesDue] query that looks like:
SELECT [practice], [month number], iif ([month number] < 85, [newfee], [fee])
as [fees due]
FROM [x confirmed], [practices]
WHERE [practice] = [prac name]

You can then join this up with the credit table in a [Account] query:
SELECT [FeesDue].[practice], [FeesDue], iif ([credit] is null, 0, [credit])
as [FeeReceived], iif ([credit] is null, [FeesDue[, [FeesDue] - [credit]) as
[FeesOutstanding]
FROM [FeesDue] LEFT JOIN [payedoc accounts]
WHERE [FeesDue].[practice] = [payedoc accounts].[practice]
AND [FeesDue].[month number] = [payedoc accounts].[mth]

I may not have the syntax exactly right there - I don't have Access on this
PC just now.

That will be the base of it - note that this query won't show practices who
have received a payment but not charged anything. To add those in, you will
need a separate set of queries that you then join up to the one above using a
UNION query. Let us know how it goes.

 

[David S via AccessMonster.com]

When I set up the second query [Account] I got "Syntax error in FROM

clause", which I could not correct.

Hi Leslie, sorry about the typos - my PC had crashed and I didn't have Access
at the time, so I was building the SQL from memory and clearly got some
things wrong.

I then created tried the following:

That's a pretty good effort - the main thing is that you're using an INNER
JOIN, which will only pick up the records that are in both tables. The LEFT
JOIN I was suggesting will pick up all records from the FeesDue table and any
matching records from the payedoc table (I now have Access agian, so I can
say this will definitely work!)

SELECT FeesDue.practice, FeesDue.[month number], FeesDue.[fees due],
IIf([credit] Is Null,0,[credit]) AS [fees received],
IIf([credit] Is Null,[fees due],[fees due]-[credit]) AS [fees outstanding]
FROM FeesDue LEFT JOIN [payedoc accounts]
ON (FeesDue.[month number] = [payedoc accounts].mth) AND (FeesDue.practice =
[payedoc accounts].[prac name]);

OK, that gives you all records where someone owes us money and any matching
payments. It won't tell us who has paid but without incurring any fees. To
add this, we first need a query to find all the payments that don't have a
matching fee. [Payments with no fees] will use a RIGHT JOIN instead of a LEFT
JOIN and, furthermore, only we want those that have no fees confirmed:

SELECT [payedoc accounts].[prac name], [payedoc accounts].mth, 0 AS [fees due]
,
[payedoc accounts].credit AS [fees received], [credit]*-1 AS [fees
outstanding]
FROM FeesDue RIGHT JOIN [payedoc accounts]
ON (FeesDue.practice = [payedoc accounts].[prac name]) AND (FeesDue.[month
number] = [payedoc accounts].mth)
WHERE (((FeesDue.[fees due]) Is Null));

Then, we can join the two queries up with
SELECT * FROM Account UNION SELECT * FROM [Payments with no fees]

Union queries do have a few quirks: for example, they doesn't match column
names, so you have to make sure that you get the order right. Secondly, the
name of the column in the union query is always the name of the columns in
your first query, irrespective of what the column names are in your second or
later queries. But the above should do the job for you...

 

[Leslie Isaacs]

David

Many thanks for your help - I'm up and running now.
This was my first foray into union queries, so I'm quite pleased with
myself!

Cheers
Les

When I set up the second query [Account] I got "Syntax error in FROM
clause", which I could not correct.

Hi Leslie, sorry about the typos - my PC had crashed and I didn't have Access
at the time, so I was building the SQL from memory and clearly got some
things wrong.

I then created tried the following:

That's a pretty good effort - the main thing is that you're using an INNER
JOIN, which will only pick up the records that are in both tables. The LEFT
JOIN I was suggesting will pick up all records from the FeesDue table and any
matching records from the payedoc table (I now have Access agian, so I can
say this will definitely work!)

SELECT FeesDue.practice, FeesDue.[month number], FeesDue.[fees due],
IIf([credit] Is Null,0,[credit]) AS [fees received],
IIf([credit] Is Null,[fees due],[fees due]-[credit]) AS [fees outstanding]
FROM FeesDue LEFT JOIN [payedoc accounts]
ON (FeesDue.[month number] = [payedoc accounts].mth) AND (FeesDue.practice =
[payedoc accounts].[prac name]);

OK, that gives you all records where someone owes us money and any matching
payments. It won't tell us who has paid but without incurring any fees. To
add this, we first need a query to find all the payments that don't have a
matching fee. [Payments with no fees] will use a RIGHT JOIN instead of a LEFT
JOIN and, furthermore, only we want those that have no fees confirmed:

SELECT [payedoc accounts].[prac name], [payedoc accounts].mth, 0 AS [fees due]
,
[payedoc accounts].credit AS [fees received], [credit]*-1 AS [fees
outstanding]
FROM FeesDue RIGHT JOIN [payedoc accounts]
ON (FeesDue.practice = [payedoc accounts].[prac name]) AND (FeesDue.[month
number] = [payedoc accounts].mth)
WHERE (((FeesDue.[fees due]) Is Null));

Then, we can join the two queries up with
SELECT * FROM Account UNION SELECT * FROM [Payments with no fees]

Union queries do have a few quirks: for example, they doesn't match column
names, so you have to make sure that you get the order right. Secondly, the
name of the column in the union query is always the name of the columns in
your first query, irrespective of what the column names are in your second or
later queries. But the above should do the job for you...