Complex Query (For Me)

[fuzbuster83]

Just started working with Access yesterday and thought my original query was complex but now we've solved that I have to take it up a notch.


Here is the basic layout of the table:

Location Item# Description Reason Cost

Location - part of the building
Item# - Part number
Description - Item description
Reason - Reason given for incident
Cost - Cost incurred by the company

Each of these are many to many if that makes sense. I've got a query to pull out the sum of each cost grouped by item# an that works well. It collapses so there is only one record for each Item# and sums all of the Costs for that Item#.
The next step is to group Item#'s by their top 5 Reasons. The result I'm looking for would be:

Item1 Reason1 (most common)
Item1 Reason2 (next most common)
etc (top 5 most reoccurring Reasons)
Item2 Reason1 (most common)
Item2 Reason2 (next most common)
etc (top 5 most reoccurring Reasons)
etc (top 5 most reoccurring Item#'s)

What I've been able to come up with is
CountofItem# Item# CountofReason Reason
x Item1 x Reason1
y Item1 y Reason2
etc (top 5 for one Item#)

I can't seem to figure out how to make it do that but list out each Item#.

[dashiellx]

I'm not comfy with your table structure. It seems like it is working for you now, but as you get more complex, you may have issues.

For your immediate issue, check out the SELECT TOP Statement

[ranman256]

youd need 2 queries:
Q1 to count the items; ITEM, COST (you must leave off REASON since it is unique and messes up the sum)

Q2 to give detailed itemized account with sum (add in REASON field)
sort descending on QTY

you cannot get Top 5 and include reason in a normal query. Its another can of worms.
you'd have a 'reporting' table to hold the temp data to report on
a form would have a list of groups in a listbox.
vb code to cycle thru the list ,selecting 1 group at a time, putting the top 5 records into the report table

empty tReport table
select next group in list
run the append query for that group for only Top 5 recs.
next group
open report

query qaAdd1GrpData2Rpt=
INSERT INTO tReport (tData.*)
SELECT TOP 5 tData.Employee_ID, tData.*
FROM tData where [group]=forms!myForm!lstGrp