Here is the result of a query. This query was created using 2 temporary
tables which hold every combined total of a number of payments ( I have
allowed up to 10) and every combined total of charges for spaces

The raw data is
Payments £79 and £147
Space Charges £26.50, 52.50 and £147


MemberID TotPayments TotPaymentIDs PID1 PID2 PID3 PID4 PID5 PID6 PID7 PID8 PID9 PID10 TotSpaceIDs SID1 SID2 SID3 SID4
969 £147.00 1 3267 1 230

969 £147.00 1 3267 1 230

969 £147.00 1 3267 1 230

969 £79.00 1 2850 2 397 94

969 £226.00 2 2850 3267 3 397 94 230

969 £226.00 2 2850 3267 3 397 94 230

969 £226.00 2 2850 3267 3 397 94 230

PID are Payment IDs and SID are Spaces being paid for.
Now it is obvious that PaymentID 3267 pays for SpaceID 230 and PaymentID 2850
pays for spaces 307 and 94

The SQL is SELECT TempPayments.*, TempStorageFees.TotSpaceIDs,
TempStorageFees.SID1, TempStorageFees.SID2, TempStorageFees.SID3,
TempStorageFees.SID4, TempStorageFees.SID5, TempStorageFees.SID6,
TempStorageFees.SID7, TempStorageFees.SID8, TempStorageFees.SID9,
TempStorageFees.SID10 FROM TempPayments INNER JOIN TempStorageFees ON
(TempPayments.MemberID = TempStorageFees.MemberID) AND
(TempPayments.TotPayments = TempStorageFees.TotStorageFeesPaid) ORDER BY
TempStorageFees.TotSpaceIDs, TempPayments.TotPaymentIDs;

Any ideas on how to extract this information to get the result above so that
I can write the appropriate transaction?

Your table is badly denormalized. Correct the design so that you don't have
repeating groups and you should find the query much easier to construct.

You've got a classic many-to-many relationship: one payment can apply to
multiple spaces, one space can be paid by multiple payments. That implies
you need three tables: one for payments, one for spaces, and one to resolve
that many-to-many relationship.

If the concept of database normalization isn't familiar to you, take a look
at some of the resources Jeff Conrad lists at
http://www.accessmvp.com/JConrad/acc...abaseDesign101

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

On 02/06/2010 11:20:15, "Douglas J. Steele" wrote:
Thanks for coming back Douglas.

I have the thre tables that you mentioned. I know what spaces a person has
and their cost. I know the payments received. What I am tring to do is deduce
from the payment which spaces he is paying for, hence the query that shows
all possible totals of payments and all possible totals of space charges.
What I am trying to avoid is using a payment of £147 to create 1 transaction
of £26.50, one of £52.50 and a third for the balance of £68 towards the
charge of £147. Then later, when a payment of £79 came in, there would be a
second transaction created against the £147 space for £79

I hate the way newsgroup readers seem to muck up displaying tables. If you
copy my example of the query into word, landscape and no margines, it is
easyier to read

Many thanks

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

If you're trying to create all of the possible combinations of Cost and
Payment, about the idea of creating a Cartesian Product of the Spaces and
Payments tables?

See whether
http://www.databasejournal.com/featu...-MS-Access.htm
helps.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

On 03/06/2010 15:32:58, "Douglas J. Steele" wrote:
Sorry for the delay in coming back.

That is basically what I have done, and the example data that was on my
original post is the result of a query where basically I said show the
results where the sum of the payments matches the sum of the charges. My
problem is to reduce the 7 records in the query to the 3 records that I need
to create the transactions.

Thanks for your thoughts
Phil