access2007query
New member
- Local time
- Today, 09:44
- Joined
- Jan 28, 2023
- Messages
- 10
I'm stuck! on a really basic problem, helping a friend in a small local club who uses French-language Access2007 to track contacts/members, payments, and payment-details (such as subscriptions, donations, sales of documents). Neither of us is really familiar with Access although my friend uses it daily to keep track of things. We have both tried queries and variations on SQL but so far to no avail.
I have simplified the lengthy French table- and column-names for the three tables we are working with :
Contacts table (call it C) contains [C].id , [C].name, ..
Payments table (P) contains [P].id , [P].date , ..
Details (D) contains [D].code , [D].value , ..
Contacts link [C].id to zero or one or more [P].id payments
Payments link [P].id to one or more [D].id details; there may be zero or one or several D lines for each [D].code, per payment.
We need to create a table for 2022 with the following columns on one line per [C].id :
[C.id] , sum ( [D].value) for [D].code = DON* , sum ( [D].value) for [D].code = SUB* , ...
What is/are the easy (easiest) way to do this (it seems a trivial enough problem) for simple users using the query wizards (with which my friend is familiar) or using SQL in Access2007?
I have simplified the lengthy French table- and column-names for the three tables we are working with :
Contacts table (call it C) contains [C].id , [C].name, ..
Payments table (P) contains [P].id , [P].date , ..
Details (D) contains [D].code , [D].value , ..
Contacts link [C].id to zero or one or more [P].id payments
Payments link [P].id to one or more [D].id details; there may be zero or one or several D lines for each [D].code, per payment.
We need to create a table for 2022 with the following columns on one line per [C].id :
[C.id] , sum ( [D].value) for [D].code = DON* , sum ( [D].value) for [D].code = SUB* , ...
What is/are the easy (easiest) way to do this (it seems a trivial enough problem) for simple users using the query wizards (with which my friend is familiar) or using SQL in Access2007?
Last edited: