totals in report

[jwb]

Ok, I'm not an expert, I need basic help.

I have a table that I use to inventory a team's uniforms. I have a field
that is used to record the 'size' of the item, the possible entries are S, M,
L, XL

I have a report that shows rows of players with multiple columns for each
uniform item.

I want to 'total' the number of Small (S), Medium (M) etc. for each uniform
item. for example:


name : jersey: shorts:

Johnny S M
Bobby M L
Tommy M L

at the bottom of the report would be a summary of how many "S" jerseys there
are.

Is there an 'easy' button'?

Thanks,

 

[Duane Hookom]

Please share you table structure and some sample records.

 

[jwb]

Duane, believe it or not I use Access a lot, but am self taught. I am not
sure how to best share with you the table structure or sample records.

I learn because of digging and trying and great people like yourself that
are willing to help me once in a while.

Thanks,

 

[Duane Hookom]

I'm also self-taught (a degree in Parks and Recreation). To share your table
structure and sample data, you type the stuff in a reply.

 

[jwb]

Ok, you got me there, I was thinking you were looking for me to send the file
or something more complicated... thanks.

Structure sample: All the fields that I am interested in having totals for
are 'text' fields.

Field Name: Data Type:
Fname text
Lname text
dark jersey size text
light jersey size text
Shorts size text


First Name Last Name Dark Jersey Light Jersey
Shorts
Size
Size Size

Rachel Anderson M M M
Jane Eustice M M M
Amanda Mundy S S S

I would like to be able to do is add a summary at the bottom of the report
that would show how many "S" Light Jerseys there are and "M", "L", "XL" etc.
by counting the text strings for each field that match S, M, L or XL.

Does that make sense? Is it a reasonable thing to want.
Thanks again.

 

[Duane Hookom]

I would first normalize your table structure using a union query

=== quniItemSizes ======
SELECT FName, LName, "Dark Jersey" as Item, [Dark Jersey Size] as Size
FROM tblNoName
UNION ALL
SELECT FName, LName, "Light Jersey", [Light Jersey Size]
FROM tblNoName
UNION ALL
SELECT FName, LName, "Shorts", [Shorts Size]
FROM tblNoName;

You can then create a totals query for use as the record source for a
subreport:

SELECT Item, Size, Count(Size) as NumOf
FROM quniItemSizes
GROUP BY Item, Size;

Your issue is that you are storing data values "Light Jersey" and "Shorts"
in field names. What happens if you issue additional items such as Dark or
Light Shorts? You shouldn't have create new fields, controls, etc to add
items. These should be stored in fields in records, not field names.