Count of field based on value 1

[ksdadguy]

I have a media database (ACC2000) in which I have a simply query which displays the count of total record and a SUM of the number of disks associated with each title.

I have been trying to add to the query to do a further breakdown based on the value within the field. The values for the TEXT field can be CD, DVD, VHS, DAT, etc.

I don't get any errors when saving the query, but when I try and run the query, I get a type mis-match error. Can I not perform a COUNT or SUM on a TEXT field. Do I need to use COUNTIF or something else?

Any help in pointing me in the right direction would be greatly appreciated.

Thanks,

KSDadGuy

 

[dhookom]

Share:
1) Your table structure(s)
2) Your sql syntax
3) Does this use Access tables or SQL Server?
4) Are any of the fields memo type?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ksdadguy]

Duane,

Thanks for the reply, here is the info as best as I can give for you.

Table Structure

Key FieldName Data Type
Description

X Media Key Text
User defined key structure. Max 100 char
X Serial Number Text
ISBN/Label Mfg ID/ASIN ID. Max 30 char
X Original Media Year Text
Year media was originally released. Max 4 char
X Media Format Text
Media format - CD/DVD/VHS/DAT etc. Max 10 char
X Media Title Text
Item Title. Max 255 char
Media Release Text
Year of Media Release. Max 4 char
SPAR-Rating Text
SPAR Code/Movie Rating/Game Rating. Max 10 char
Disk No Number
Nnumber of disks associated with title. Double prec
Media Category Text
Type of media - Movie/Game/Music Type. Max 75 char
In Library Date/Time
Date added to library. Medium date format DD-MMM-YY
Run Time Text
Approx runtime Max 8 char. Form 99999.99 or 99999:99
Collection Yes/No
Is this a greatest hits collection.
Artist Text Group/Artist of title. Max 100 char
Label Mfg Text
Label manufacturer of title. Max 50 char
Owner Text
Owner of title. Max 15 char
MediaOnLoan Yes/No
Yes/No - has media been loaned to anyone
BorrowedBy Text
Name of person who has borrowed the . Max 75 char
Borrow Date Date/Time
Date media was loaned. Medium date form DD-MMM-YY

This is an Access2000 table.

The only query I have got to work is testing for duplicates and that works okay. But I can't figure out how to get it to write to a report format instead of the standard query output.

No memo types in the structure

Thanks very much for your help

Jim Malay

 

[dhookom]

You should be able to create a query
SELECT [Media Format], Count(*) as NumOf
FROM tblMyMedia
GROUP BY [Media Format];

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ksdadguy]

Duane,

Thanks, the query works fine. I guess what I didn't explain fully was that I wanted to be able to display the results in a text box on a report

I currently have a report which has a couple of text boxes with the control source of the following for number of media titles and disks associated with each title

SumOfDisk No
CountofMedia Title

I was hoping to do a similar display with a breakdown of the media format types. Am I just trying to do something that is not possible?

Jim

 

[PaulBricker]

Well, you can try something like this in the Control Source for your textbox (as long as it's in a Group or Report footer).

="Total DVD's " & Sum(Abs([Media Format] = "DVD&quot)

See if that's what you want.

Paul

 

[dhookom]

I would create a separate, small report based on the last query and place the new report in the Report Footer of your existing report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ksdadguy]

Paul/Duane,

I tried the report footer option with the controlsource as you advised and got nothing in the the textbox.

I was up most of the night playing with this one. I even tried Dsum and different options that are in the Acc help file, but either got syntax errors or again - just no data

The GroupBy option worked but only pulled up a query results window. Just wish I could transfer the results of the query into textbox format

Thanks for your help. If there's any other resource you could point me to, it would be greatly appreciated.

Jim

 

[dhookom]

You can't show multiple records in a single text box without writing a function. My suggestion of creating a separate subreport based on the group by query and then placing the subreport in the main reports Report Footer Section has always worked for me. If you add other "group values" they will automatically display in the subreport with no design changes.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ksdadguy]

Duane,

Ok thanks, I think we've beat this thing to death enough. I'll play with it a little more and maybe just add the query on and let people decide which way they want to go with it.

Would you like a copy of the MDB file? I can zip it up and send it to whereever you'd like.

Thanks for all your help.

Jim

 

[dhookom]

Jim,
Thanks for the offer but I have too many copies of MDBs. I would rather solve issues and answer questions in public.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PaulBricker]

Thanks Duane. I'm interested in seeing what the answer is and gave you the star for keeping it online. I know it's not always easy that way, but I'm sure everyone following this post appreciates it.

Paul