Multiply records on report (1 Viewer)

inke01

New member
Local time
Today, 21:37
Joined
Aug 10, 2020
Messages
15
Hy again to everyone, after problem with crossposting i'm back with my problem.
If there is anyone ho can help me/us to solve this problem.
What i really need is when i open report it groups data by Buyer -> Cobuyer _>type of milk -> Somaticcells.
Can i make something like showed on report: all results on report showed in detal when i type =sum(somatic_cells) it sums all of them no matter how many are there. Is any possibility to multiply them????
PS. Thata what be multiplyed will contain only 3 records couse query fiters data only for last 3 month and i have only one entery form smoatic cells in month.
Cant post link to original post it treats like spam. It's below what posted Micron as cross post :)
 

Attachments

  • raw report.png
    raw report.png
    9.6 KB · Views: 120
  • Somatic.png
    Somatic.png
    89.3 KB · Views: 115
Last edited:

inke01

New member
Local time
Today, 21:37
Joined
Aug 10, 2020
Messages
15
Anyone?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:37
Joined
May 7, 2009
Messages
19,169
create a Query from your Table and add Calculated column there.
if you can give a link (dropbox) to your database, then we can see
what have you tried so far.
 

inke01

New member
Local time
Today, 21:37
Joined
Aug 10, 2020
Messages
15
Can add couse all record are in same tabke field
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:37
Joined
Feb 19, 2013
Messages
16,553
if there are always only three records, create a query to link the three records on whatever you are grouping by

Code:
SELECT DISTINCT A.groupfield, [A].[fieldX]*[B].[fieldX]*[c].[fieldX] AS multiplied
FROM (myTable AS A INNER JOIN myTable AS B ON A.groupfield = B.groupfield) INNER JOIN myTable AS C ON A.groupfield = C.groupfield
WHERE (((B.ID)<>[A].[ID]) AND ((C.id) Not In ([A].[ID],[B].[ID])));

change names to suit your table and fields

you haven't provided detail on your table structure or content, so rather than using criteria on your ID's use something based on dates - perhaps

WHERE format(A.myDate,"yymm")=format(Dateadd("m",-3,date),"yymm")) AND format(B.myDate,"yymm")=format(Dateadd("m",-2,date),"yymm")) AND
format(C.myDate,"yymm")=format(Dateadd("m",-1,date),"yymm")) AND

you can then join this in your report recordsource or keep as a a separate query and use dlookup in your report
 

Micron

AWF VIP
Local time
Today, 16:37
Joined
Oct 20, 2018
Messages
3,476
So here is an example where the forum policy on links comes back to bite everyone. You can't get useful information that may save time and answer our questions from 3 pages of posts at the other forum because of the 10 post rule.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 28, 2001
Messages
27,001
There is no function in Access that does an Aggregate Product. The Aggregate Sum works no matter how many records. There is also no Domain Aggregate equivalent for products.

You COULD do something that computed the product of the individual fields using VBA code behind a report detail section and display it in a group footer section.

You COULD do some kind of VBA pre-processing function to step through a recordset to form the aggregate product and store that in a separate record somewhere that involves one less key-field than the total grouping. Like, if you grouped on A, B, C, and D, you would write some VBA to go through that list for all A, B, and C constant but stepping through values of D, and store in a record that holds you aggregate of all D for a given A, B, and C. Then you could easily retrieve it.

I won't try to build code for either of those because I don't know enough about the structure of the DB in question.
 

inke01

New member
Local time
Today, 21:37
Joined
Aug 10, 2020
Messages
15
I still cant provide link from other forum where i posted the db. or even cant upload raw db here any help about that or i have to talk to the admin?
Problem is still active.
I need some function that does same like sum on report db.
 

Micron

AWF VIP
Local time
Today, 16:37
Joined
Oct 20, 2018
Messages
3,476
You're at post 9. Make one more and you should be ok.
 

Micron

AWF VIP
Local time
Today, 16:37
Joined
Oct 20, 2018
Messages
3,476
Oops. I looked at the total count.
IF coffee = 0 Then
DontPost
End If

OP could mask the link to the other thread, or see post 2.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:37
Joined
Sep 21, 2011
Messages
14,047
I am looking at that link and the function has been changed to
Code:
Function CalculateSomatic() As Long TO Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngProduct As Long To Double

On Error GoTo errHandler
lngProduct = 1
Set db = CurrentDb
Set rs = db.OpenRecordset("query_somatic_cells", dbOpenDynaset)

If Not (rs.EOF And rs.BOF) Then
  rs.MoveFirst
  Do While Not rs.EOF
    lngProduct = lngProduct * rs.Fields("somatic_cells")
    Move Next rs.MoveNext
  Loop
End If

exitHere:
CalculateSomatic = lngProduct
Set db = Nothing
Set rs = Nothing
Exit Function

errHandler:
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Function

but nowhere is it taking into account the, to the power calculation of one third?
 

Micron

AWF VIP
Local time
Today, 16:37
Joined
Oct 20, 2018
Messages
3,476
You'd have to get the code from the posted db I think.
I'm still out of commission, BTW.
 

inke01

New member
Local time
Today, 21:37
Joined
Aug 10, 2020
Messages
15
I was trying but with no sucess. First report created with microns function and till now it works but will have to make it better couse i have manualy select from every buyer cobuyer and than print it that for cobuyers report at end of the month.
Another problem is report for buyer where are shown all cobuyers with arthimetic mean( taht is not problem i grouped on report by buyer, cobuyer and type of milk easy one with sum... ect. =sum(mlijecna_mast) -> Text10 in detal and than i put that value back to cobuyer with ect. =([Text10]/2)
Problem starts here on that same report i have to calculate agan geometric mean from somatic cells and bacteria, bacteria and soamtic_cells have to get from another query couse somatic_cells are filterd to last 3 moths and bacteria last two months. (note every record i have 2 enterys on month and somatic only one).
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:37
Joined
Feb 19, 2013
Messages
16,553
without a clear understanding of what you require, don't think I can take this further. Your original example says 3 records, you now appear to be saying 1 or 2.

Consider uploading a database with some example data to cover all eventualities together with what you want the expected result to look like based on that example data
 

inke01

New member
Local time
Today, 21:37
Joined
Aug 10, 2020
Messages
15
Here is an example from db. month report for buyers with his cobuyers all parameters are calculated with two parameters by type of milk like i mentioned in previous post. Only problem is with somatic cells and bacteria. No function like SUM.
1.png
 

inke01

New member
Local time
Today, 21:37
Joined
Aug 10, 2020
Messages
15
Here is examle of db. older version. But the data are the same
 

Attachments

  • BEKLAB.zip
    1.5 MB · Views: 113

Users who are viewing this thread

Top Bottom