How to ... Multiple Records from Same Table on one Sub-Form?

[simonpickering]

Hi,

I'm new to the forum, struggling and would really appreciate some help with an Access Form problem.

I'm building a database that will track check payments made to individuals.

Each single payment consists of multiple elements that, when added together, result in one check being made payable to the person. These Payment Elements (or categories) are currently 'Hotel Room', 'Travel', 'Food' and 'Honorarium'.

I'd like to keep the elements separate so that I can sum, say, all the 'Food' costs for a period.

So I've made tables similar to these:

tblPerson
ID, FirstName, Surname, DoB etc.

tblPayment
ID, PersonID, Date, Method, etc.

tblPaymentElements


ID, PaymentID, PaymentAmount, ElementType

tlbElementTypes
ID, Description

PaymentElements are the many side of a one-to-many with Payments. ElementTypes is just a lookup table for the descriptions (1 = 'Hotel Room', 2 = Travel etc).

So now I'm stuck.

I'd like a sub-form that contains rows of Payments - one row for each check payment (not one row for each element/category). There would be text boxes for Date and then the four current Payment Element types (and possibly an unbound textbox with some VBA that adds the payments to display a total).

As I'm new to the forum I can't post mock-up images but I might be able to do it with text. My form would like something like this:

Person Form
---------------
Name: Fred Smith
DoB: 1954-10-30

Sub-Form
------------
Labels -> Date; Hotel; Travel; Food; Honorarium
------ Rows -----
[09-02-2012] [$55.00] [$11.56] [$15.00] [$45.00]
[10-21-2012] [$65.00] [$23.46] [$09.14] [$45.00]
[10-30-2012] [$47.00] [$03.23] [$19.12] [$45.00]

So the PaymentElements table might have:

PersonID, PaymentID, PaymentAmount, ElementType
0000001, 0000001, 55.00, 1
0000001, 0000001, 11.56, 2
0000001, 0000001, 15.00, 3
0000001, 0000001, 45.00, 4
0000001, 0000002, 65.00, 1
0000001, 0000002, 23.46, 2
0000001, 0000002, 9.14, 3
0000001, 0000002, 45.00, 4
etc.

As you can see, the textboxes are not the fields within a row of a record but the same field name taken from multiple rows within the same table that share a PaymentID.

I can't work out how to filter each Payment Element text box on the sub form so that the first one only shows the record for Payment Element Type 1 (Hotel), the second for Payment Element Type 2 (Travel), the third for Type 3 (Food) etc.

It's as if I need four different sub-forms within my sub-form - but that seems complicated! I must be able to filter for the PaymentElement ID - within the text box perhaps?

Am I on the right lines? My only other way I can think of is to create separate tables for each payment type - but I think that breaks normalization rules.

Thank you for your time,

Simon

[ssanfu]

You might try a crosstab query..


Also, I would recommend that "ID" in each table be changed to reflect which ID the field belongs to. In "tblPerson", "ID" be changed to "PersonID" since you used that as the FK in "tblPayment", "ID" in "tblPayment" changed to "PaymentID" , etc.
Having 4 fields named "ID" gets confusing.

[simonpickering]

Hi Ssanfu,

Thanks for your reply. I don't know about crosstab queries. Are they updatable? Can you recommend a source for me to learn from?

[ssanfu]

Are they updatable?

Don't know. I've only had to use a crosstab one time. I normally use two queries to simulate a crosstab.

Can you recommend a source for me to learn from?

Access Help , Google, check your local used book stores for books on your version of Access.

[June7]

Aggregate queries are by their nature not updatable. Crosstab is a type of data aggregation query.

Here is a site with SQL tutorials http://www.w3schools.com/SQl/default.asp

If you want the payment elements to display in horizontal (left to right) arrangement, this is a data pivot. Crosstab is one way to accomplish. However, stabilizing report on crosstab query to run perpetually can be difficult because crosstab structure is dynamic. Assuring that the same fields will always be present is tricky.

You might try basing the subform RecordSource on a GROUP BY (Totals) aggregate query and see if that satisfies your output needs.