Sum of multiple fields with same date

[vincentsp]

Hi All,

I'm trying (all freaking day ) to get the following done without very much luck..
By now it seems pretty much impossible to me, but perhaps someone is able to help me...

I have a number of records, each record got a field named "TotalOrder" which gives me the total price of this specific record.
I want to calculate the sum of all the records with the same date.


After getting the sum of all the records grouped by date I want to compare this to the minimum price per day.

So let's say the sum of all (10) the records with the same date is 100,- and the minimum is 90,- nothing happens and my query field "minimum" stays empty or 0
But if the total of all (10) records with the same date is 80,- and the minimum is 90,- I want the query field "minimum" have the value of 90,- divided by the number (in this case 10) records.

[June7]

What have you tried? Did you build an aggregate GROUP BY (Totals) query to summarize the price data by product and date?

Do you have a table that shows these 'minimum' values for each product? Join this table to the aggregate query.

[vincentsp]

I have only one value as minimum "900" I have managed to group the records by date and sum-up the total per date.
But when I include a IIF clause for values above or under 900 it splits up my group and applies the clause per record.

I can work my way around this by making a new query with the grouped records and applying the IFF clause in that query.
This works, but than I'm not able to split/divide the 900 over the number of records in the group.

[June7]

You also need to do a Count in the aggregate query?

Yes, do second query or a report bound to the aggregate query. Or maybe even a report based on the raw data and set up grouping in the report with aggregate calcs in group footer. Report allows display of detail records as well as summary data.

[vincentsp]

I have managed to get a count on the dates and calculated the minimum value divided by the date count.
This works but the records are now grouped by date, for example: Date 22-1-15 contains 3 records total value is <900 therefore the result is 900 divided by 3 = 300 (per record)

Now the final step, I'm trying to get the value (300 in this case) shown per record in a new query, in this case there would be 3 separate records with the same date 22-1-15 (not grouped like before) and a field that shows the number 300 for each of the three records.

[June7]

Sounds like to me should do the report with raw data I described instead of aggregate query.

[vincentsp]

Finally almost done using multiple(5) queries I ended up with the date and corresponding value. Forgive me for not using the report option as you suggested but I'm simply not that familiar with access (yet).

Now having the date and corresponding value I made a relation by date between my table (with all the data) and the query.
Is there a way to get the value from the query in the table having it related by date?

[June7]

5 queries!!!????

I don't understand the question in that last post.

[vincentsp]

The minimum value that I have calculated (with probably a query of two to many..) is based on the records grouped by date.
In my table the records are obviously not grouped together, but I'm trying to get the result from my query back in my table (if possible)

Example: in my table I have 8 record dated 10-01-2015, using the query I have calculated that the 8 records grouped together are below the minimum value I have set.
Therefore my calculation uses the minimum value I've set on 100 divided by the 8 records = 12.5 per record (which are still grouped together in my query).
Now having this value I want to put it back in my original table so that every record with the date 10-01-2015 (8 in this case) show the minimum value 12.5.

I hope this makes sence.. And thanks for your effort helping me!

[June7]

Saving calculated data is usually a bad idea. What you want will require VBA programming and/or an UPDATE sql action, if I correctly understand what you want.

[vincentsp]

Correct, the most ideal situation would be that I'm able to see my minimum value (if applicable) in my form or query split per date and together with my other data.
But if the only option for this would be to program this with VBA or a SQL action, then I would appreciate some help because I'm everything but familiar with VBA programming.

[June7]

I think it would be possible to show the minimum value divided by the number of records on form or report. It's saving these calculated values into table that gets tricky and I recommend not to do.

[vincentsp]

What would you recommend doing, if I want to be able to see my minimum value (if applicable) per record.

[June7]

On a report, set group based on date. Then in the group footer have textboxes that calculate the group count and group sum. Then in another textbox have IIf() expression referencing the group sum textbox. A textbox in the Detail section can reference the textboxes in the group footer.

This sort of calc would be more difficult on form because records cannot be grouped and will probably involve DCount and DSum domain aggregate functions (which can be slow performers). This is assuming you want the records to be editable.

[vincentsp]

Yes indeed the records have to be editable. I worked out your report idea and this does the trick.
Thanks for the effort!!