Summarizing related records

[Tpaairman]

In my file, I have one table that is a list of transactions.  Then, I have another table that on it's layout, summarizes things from a few other tables, including the transactions, and it has a start date field and an end date field.  Those fields are simply the first of the month, and the last day of the month.

In my relation between the two, it's the transaction date is greater than or equal to the start date, and less than or equal to the end date.  So far all is well.  On my summarize view layout, I can see the transactions for that month as I'm supposed to.

Here's the problem.  I want to have two fields, one of which shows the total transaction amounts from the 1st to the 15th, then another that shows is a sum of transaction amounts from the 16th to the end of the month.  I have a field in my transactions table that if the date is between the 1st and 15th, the result is "A", and if it's the 16th to the end of the month, it is "B".  On the transactions layout, it does show A or B as it should, so I know it's differentiating each half of the month.  But I can't figure out on the summarize view layout how to have the one field sum of transactions that are A and the other sum of those that are B.

[comment]

There are (at least) 3 different ways you could approach this. I don't know enough about your particular situation to suggest which one is the best for you.

1. Define two dedicated relationships from your summarizing table to the transaction table, using the A/B field as an additional match field.

2. Place summary fields defined in the transaction table in filtered portals.

3. Keep a separate record for each half of the month in the summarizing table (with the option of combining them using a sub-summary).

 

[Tpaairman]

I'd say #1 is the only one of those three that would work.  The fields are not in a portal.  I'd have to have two portals, each with one field in it (only one portal row.)  And 3 is out because I need to have the summary page for the whole month.  I'll give #1 a go.

[comment]

19 minutes ago, Tpaairman said:

I'd have to have two portals, each with one field in it (only one portal row.) 

Why, yes - that's exactly what I meant by #2. Why do you think that would not work?

 

20 minutes ago, Tpaairman said:

And 3 is out because I need to have the summary page for the whole month.

And that is exactly what I meant by "combining them using a sub-summary".

In fact, if you hadn't mentioned summarizing data from other tables too, I would have recommended you simply use a report produced directly from the transactions table, summarized by month and by half-month. That would be the simplest solution, IMHO. 

 

[Søren Dyhr]

2 hours ago, Tpaairman said:

I'd say #1 is the only one of those three that would work.  The fields are not in a portal.  I'd have to have two portals, each with one field in it (only one portal row.)  And 3 is out because I need to have the summary page for the whole month.  I'll give #1 a go.

Take a look here: 

...instead of "labour" as grouping - use an autoenter giving which half part of the month the transaction have been made in....

--sd

Edited May 7 by Søren Dyhr

[Tpaairman]

@ comment - I should have clarified better.  #2 is out because I don't want the extra portals on the layout.  I'm a bit limited for room, and I just want the two simple fields.

On #3, I can't have a separate record for each half of the month.  This table is summarizing things from several tables, with a record for each month as a whole.  Adding another record for the second half of the month would mean two records for each month, each with one difference, being these fields.

6 hours ago, Søren Dyhr said:

Take a look here:

I will look it over later this evening, however, I did use comment's first suggestion of a second relation, and it's working.

[comment]

I wouldn't be so quick to dismiss the other options:

 

1 hour ago, Tpaairman said:

I'm a bit limited for room, and I just want the two simple fields.

A one-row portal containing a single field can be exactly the same size as the field.

 

1 hour ago, Tpaairman said:

Adding another record for the second half of the month would mean two records for each month,

True - but you could make only one of the pair related to the other table/s.

 

[Søren Dyhr]

2 hours ago, Tpaairman said:

I will look it over later this evening, however, I did use comment's first suggestion of a second relation, and it's working.

One thing you could obtain, by this is making the summarized values stored, and by it, then searchable, such as which half-month were we performing better than?....

--sd