Hi there, Basic query question (I hope)

[Zorkmid]

I have a database that logs incident reports. It logs things like the type of incident (complaint, fall, medication error), specific type (which are subtypes ex: falls -> slipped, tripped etc.) location, and that sort of thing.

I want to build a query that displays the totals for each specific type of incident for a single main type for a certain location, over a certain time.

-Z

 

[ajetrumpet]

take a look here first:

http://www.access-programmers.co.uk/forums/showthread.php?t=135763

 

[Zorkmid]

I think my question is even more general than that. What type of query do I need to do this? Crosstab? I want the end result to look like a pivot chart.

-Z

 

[Zorkmid]

I think I've nearly got it. The only problem is that I am not getting the totals over the selected time period, I am getting the totals for each day between 1/1/2008 and date() that fits the bill.

 

[ajetrumpet]

I think I've nearly got it. The only problem is that I am not getting the totals over the selected time period, I am getting the totals for each day between 1/1/2008 and date() that fits the bill.

this is just a statement. if you want help, tell us what's wrong with it, in terms of "Access" terminology, not in terms of your application's terminology (like "bills", etc...). We have no idea what that stuff means.

 

[Zorkmid]

Ok, sorry about that. I am using 4 fields in this query. Date, Type, Specific Type, and Location. There are 5 "types" of records, each with it's own set of "Specific Types". Location is a numeric field, and date is just a date type field.

I wish to display the totals of all the "specific type" of incident for one of my "type"'s (Treatment/Test/Procedure), since 2008 for one of my "location"'s

I currently am working on a crosstab query that is set up like this....

Field:Type
Type:Risk_Data_Query (This is just my entire table basically)
Total:Group By
Crosstab:
Sort:
Criteria: "Treatment/Test/Procedure"

Field:Specific Type
Type:Risk_Data_Query (This is just my entire table basically)
Total:Group By
Crosstab:Column Heading
Sort:
Criteria:

Fieldate
Type:Risk_Data_Query (This is just my entire table basically)
Total:Group By
Crosstab:Row Heading
Sort:
Criteria:Between #1/1/2008# And Date()

Field:Location
Type:Risk_Data_Query (This is just my entire table basically)
Total:Count
Crosstab:Value
Sort:
Criteria:



It is ALMOST doing what I need it to, but instead of totalling up all of the incidents since 2008, it is just picking out the specific dates for which there is a record and giving me a count of "Specific Type" for each of those dates.

(I tried to paste in a couple lines of the query result here but couldnt get it to work, I hope you can imagine it from my description.)


I'm not sure how to set it up to give me the total since 2008 instead of a total for each date, and I am having a hard time in general of wrapping my head around crosstab queries.

Sorry I was so vague before, I was rushing through things a bit. I hope this is more clear.


-Apologetically,
Z

 

[speakers_86]

I have this sneaking suspicion that you should not be using a crosstab query for this. I think maybe you should be using a query to filter your data, and then use total by some field. Since I don't know exactly what you are trying to do, I don't know for sure. Can you post your db?

 

[ajetrumpet]

Can you post your db?

I second this motion.

 

[Zorkmid]

Ok,

I've chopped it down to size enough so that I can upload it. I'm trying to get totals for the following:

Under the "type" "Treatment/Test/Procedure" I want the totals of each specific type (these can be found in the table: "SpecificType_Treatment/Test")

Please let me know if I was unclear.

-Thanks,
Z