complex grouping

  • Thread starter Thread starter James
  • Start date Start date
J

James

if i have an employee table that has a recruiterID field which joins to the
same employee table. how can i create a sql that groups by the recruits
sales + recruiter sales?
 
This question lacks specifics. Could you provide some sample records and
desired final display?
 
my db is access 97.
Two tables:
Recruiters (fields: RecruitID,RecruiterID, Name)
Orders (fields: RecruitID,OrderPrice)

i want to create a report that groups by the Recruiters.RecruiterID and
lists all orders for each recruit AND EACH RECRUITER and totals the
Orderprice at the bottom.

the hard part is getting it to show the Recruiter's orders grouped with the
recruit's orders.

Wanted output:
RECRUITER (main recruiter, has null for recruiterID)
Order#12321 price
Order#23644 price
Order#34363 price
RECRUIT
Order#34376 price
Order#34753 price
Order#34343 price
RECRUIT
Order#35634 price
Order#35353 price
Order#67567 price
RECRUITER
Order#34534 price
Order#64842 price
Order#43646 price
RECRUIT
Order#75745 price
Order#46464 price
Order#45454 price
ETC...

p.s. Once i figure out how solve the problem above; i would like to only
display one recruiter's orders on a report. for example: if a recruiter has
a recruit and that recruit has a recruit. i wouldn't want it to do another
grouping for that second recruiter. (this isn't nessiarly but only if you
have some brainstorming ideas i can start from)

lemme know if you need more info i will respond as soon as i see your post.


Duane Hookom said:
This question lacks specifics. Could you provide some sample records and
desired final display?
Click to expand...
 
I changed your table names so they wouldn't conflict with other tables in my
sample database. Try SQL like:
SELECT Nz([JamesRecruiters_1]![Name],[JamesRecruiters]![Name]) AS
RecruiterName, JamesRecruiters.Name, JamesOrders.OrderPrice
FROM (JamesOrders INNER JOIN JamesRecruiters ON JamesOrders.RecruitID =
JamesRecruiters.RecruitID) LEFT JOIN JamesRecruiters AS JamesRecruiters_1 ON
JamesRecruiters.RecruiterID = JamesRecruiters_1.RecruitID;


--
Duane Hookom
MS Access MVP


James said:
my db is access 97.
Two tables:
Recruiters (fields: RecruitID,RecruiterID, Name)
Orders (fields: RecruitID,OrderPrice)

i want to create a report that groups by the Recruiters.RecruiterID and
lists all orders for each recruit AND EACH RECRUITER and totals the
Orderprice at the bottom.

the hard part is getting it to show the Recruiter's orders grouped with
the recruit's orders.

Wanted output:
RECRUITER (main recruiter, has null for recruiterID)
Order#12321 price
Order#23644 price
Order#34363 price
RECRUIT
Order#34376 price
Order#34753 price
Order#34343 price
RECRUIT
Order#35634 price
Order#35353 price
Order#67567 price
RECRUITER
Order#34534 price
Order#64842 price
Order#43646 price
RECRUIT
Order#75745 price
Order#46464 price
Order#45454 price
ETC...

p.s. Once i figure out how solve the problem above; i would like to only
display one recruiter's orders on a report. for example: if a recruiter
has a recruit and that recruit has a recruit. i wouldn't want it to do
another grouping for that second recruiter. (this isn't nessiarly but only
if you have some brainstorming ideas i can start from)

lemme know if you need more info i will respond as soon as i see your
post.
Click to expand...
 
Back
Top