Summation of Types (based on text criteria)

[justinwright]

I have a table with historical entries. First off, there is a field called DESC, text, that can be ThingA, ThingB, or ThingC (it's possible that it could be different, but those are the only three things that really matter for the sake of my question).

ThingC is a monthly thing so is structured as "January ThingC"
December ThingC
etc.
Always the month + space + ThingC for the DESC field

ThingA and ThingB are always either "ThingA" or "ThingB" respectively.

Each item has an AMOUNT field (Number, General Number, Double data type), regardless of type, as well as a DateAdded field.

I wanted to generate a report where it will total the entires for this given a date constraint by the user (txtDateFrom and txtDateTo on a form called FrmDateRangeSpec) contained in the DateAdded field (date/time data type).

For instance, txtDateFrom being 1/1/2011 and txtDateTo being 1/31/2011

It would generate a report that would only show the total number and total amount of entires for each one (given they are within the date constraint mentioned above by their DateAdded fields). Individual entires (since tehre will be a lot of them) are irrelevent since I just want the totals for the types of entries.

------------------------
EX.

Number Type Amount
2 ThingA $2.15
65 ThingB $13513.13
0 ThingC $0

-----------------------

It would ideally remove the month from the front of the DESC for ThingC (just saying ThingC instead of saying January ThingC) on the report.

The names for the actual things (ThingA, ThingB, ThingC) will not change(case, space, or otherwise).

I found this:

https://www.accessforums.net/reports...port-9073.html

Which is basically the same question I'm asking, but I uh... don't really understand what he means :\

Is he talking about on the report grouping, the advanced option that says "Keep whole group together on one page?" If so, how would that account for the ThingC? If it wasn't really possible to seperate the names of ThingC, then I would be completely okay with just leaving January ThingC and February ThingC as two seperate entries (groupings?).

I hope this question is clear (feel free to ask if I'm just talking in circles), and as always I appreciate any help you guys are willing to offer!

Thanks!



Edit: In my example those things above were all neatly spaced out like the fields headers/data on a normal Access report T_T

It would also be helpful if it was in the VBA part of it (since I still remain totally lost with the built-in Access Queries), but beggars can't be choosey as the saying goes

[maximus]

I have to replicate the problem that u had mentioned in your post.

I Have done the following:
1) Created a Table with Fields ID, Desc, D_Date
2) The Desc Field will Contain data Like January ThingA, January ThingB, January ThingC

I have created a query. I have used Right Function to display ThingA, ThingB, ThingC. I have used Select distinct to select the Distinct Items in the table. (Note: You can use Like function to Display the items names which has thing in it). Expr1 Displays the names without the months.

SELECT DISTINCT Right([Desc],6) AS Expr1

I have created another calculative filed Expr2:

DCount("[ID]","table3","Right([Desc],6) ='" & [Expr1] & "' And [D_Date] Between #" & [Forms]![Form1]![Text0] & "# And #" & [Forms]![Form1]![Text2] & "#") AS Expr2

This part of the Sql calculates the count of entries based on the Criteria that is Expr1 and date that is got from Text0 and Text2 from a form Form1.

The entire SQL is as below:

SELECT DISTINCT Right([Desc],6) AS Expr1, DCount("[ID]","table3","Right([Desc],6) ='" & [Expr1] & "' And [D_Date] Between #" & [Forms]![Form1]![Text0] & "# And #" & [Forms]![Form1]![Text2] & "#") AS Expr2
FROM table3;

[justinwright]

Is the ID field an autonumbered primary key field?

Also with the SQL statement you're working towards, can I use that as the "where" filter in DoCmd.OpenReport?

From looking at the statement, it looks like it's going to group ALL the records based on the type. The table I have can contain things other than items like "January ThingC," "ThingA," and "ThingB." How would I just group/display those on the report? The other entries that aren't ThingA, ThingB, and ThingC aren't really of consequence for the purpose of the report I'm making, but they are still present in the same table.

Thanks for the help so far!


EDIT: As an update (if this helps at all), I used DoCmd.OpenReport and the where filter to only display the data on the three items I'm concerned with (also filtering it by the date range I specify). The only thing that's really making me run into a brick wall here is combining all of those like entries into one line with a total of all (i.e. ThingA $5 each for 5 entries in a list would just show ThingA then $25).

On the report itself, in Design View, I created a Grouping that is:

Group on DESC, With A on top, by entire value, with PAMOUNT totaled, with title Explanation, with a header section, without a footer section, keep whole group together on one page

And it is grouping all of the entries together by type, but it is showing each individual amount (tons of records with ThingA on the left and the amounts listed to the right going down.

I tried the "Hide Duplicates" option to on but it just shows a blank space there (so huge gaps in the report when all I really need is a summary). I can set the detail section to not visible, which looks almost exactly like what I need, but it won't show me the totals for all of them (just a list with ThingA, ThingB, ThingC and nothing else... no amounts or anything).

I was going to use an IIf Sum to display it, but since the ThingC can (and will) change based on the month, it will be rather difficult to do so (especially since the position of the groupings will change (the Month being the leading word in ThingC changing the alphabetical sort order on the left, if that makes sense).

[justinwright]

It just occurred to me how to fix it, so I figured I'd post my solution. In my grouping, I went to the "with PAMOUNT totaled" part and checked the "Show in Group Header" box, thus displaying the totals!

Note that I also kept the Details section invisible for the purposes of calculations only.

Hopefully this helps someone else, thanks for the help though Maximus!

[maximus]

Hello justinwright,

Answer to the question that you had............................

"From looking at the statement, it looks like it's going to group ALL the records based on the type. The table I have can contain things other than items like "January ThingC," "ThingA," and "ThingB." How would I just group/display those on the report? The other entries that aren't ThingA, ThingB, and ThingC aren't really of consequence for the purpose of the report I'm making, but they are still present in the same table."

How about using a Like to select only the Items in your table that has thing in it

example:

Criteria of Expr1 = Like "Thing*"
So the calculative field Expr1 in the query will only show the items you actually need i.e. which has the word thing.

Anyways happy that u were able to solve your problem.