Reports and the Expression Builder

[bobunknown]

Hi all you Report wizards

im trying to learn more about reports. Specifically the expression builder.

*Context
I have a database for Waste removal (bins and stuff) and I want a report that tells me how many bins of a particular size we have out currently. The database I have has a "Bin Size ID" assigned to each different size of bin (120L = 1 , 140L = 2, 240L = 3 etc).

My idea was to use the expression builder to count how many times the ID 4 (for example) shows up but the expression builder has other ideas.

This is what I have so far:

=DCount("[Bin Pricing]![Bin Size ID] = 4 and [Collections]>0")

(The bit at the end is because I don't want it counting bins we don't collect)

Any thoughts? is my approach correct? or is there anther way?

 

[MajP]

Google "access aggregate query". An aggregate query allows you to group things an do different types of calculations on the group. It will allow you to group on bin size and count the number per bin.

In a report you can also do sorting and grouping and do group summation.

Group Report

https://support.office.com/en-us/ar...y-report-f23301a1-3e0a-4243-9002-4a23ac0fdbf3

Aggregate query

https://support.office.com/en-gb/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

 

[pbaldy]

I'd probably also use the aggregate query, but a reference for why your syntax was wrong:

http://www.theaccessweb.com/general/gen0018.htm

 

[bobunknown]

Thanks for the info im reading up on it and making a little headway but I've not managed to get a query that can be shown in a report yet only one using the totals button in datasheet view

Im having trouble making a query in the Criteria box, when I enter Count("1") I keep getting an error saying that that cant happen. Do I need to write it outs as a string even though I have defined where I want the data from by using the query design form?

 

[Minty]

Can you post up an example of your starting data and what your end goal is, you can use a spreadsheet if you want, or a stripped down version of your database.

Make sure we have enough sample data to give you your results.

 

[bobunknown]

Hi all sorry about the long lack of reply. I have trying to teach myself before annoying anymore of you. Those links that have been posted were massively useful thanks.

I went with using a query as suggested and have made one that should suit my needs (after some trying) however when it comes to getting it in to a report I have hit a snag.

I am trying to use the following:

=Count([Total bins])

From what I have read this should add up all the returns in the query and display them.... but it doesn't. All I get is a #Error message that isn't to useful as it gives me no direction.

Everything is spelled correctly im sure of that so dose anyone have any suggestions?

 

[pbaldy]

Where is it? I don't think it will work in the page footer. It should work in a group or report footer.

 

[bobunknown]

Okey, ill do some more reading on the suggested. I wanna try solve it on my own just need prodding in the right direction.

Thanks

 

[pbaldy]

No problem, post back if you get stuck. Another thought, [Total bins] has to be a field in the report's record source. It can't be a calculated control on the report, like

=Quantity * Price