calculated field on a report

Mi

Board Regular
Joined
Sep 19, 2004
Messages
77
Hello,
I have a report that is opens with via a filter form. User selects week date and unit. Report then shows the number of accounts. What I would like to do is have next to the account field the number of times that account has been on the listings. Listings are pulled weekly. Thus if on 10/17, acount "alpha" is on the list and it has been on the previous 5 weeks, to show 5 next to it in another field. This is the last step needed for this project to be completed. Any help would be appreciated.

Mi
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
A report is not based on a form. Its record source is either a table or query; likewise a form relates to a table or query. I'm guessing that it's a query. So you want to modify that query to also grab a field with the number-of-times-on-listing value, which you should save in some distinct table.

Now as to the value, which is how many times an account has been on the listings, I don't think there's an easy solution. What comes to mind is to have Visual Basic code under a button, that increments the table keeping those account counts. It would run an update query. This would wrap around an aggregate (group by) subquery that produces a unique list of accounts.

Is this already too far over the top for you or do you want more details?
 
Upvote 0
I sort of having it working now. It totals the number times the account number is listed. that works but I want it to only count the number of times listed prior to a certain date ( the selected filtered date). Just need to figure that part out.

Mi
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top