Using Queries in Queries

[Dixen]

I have a query that is counting the number of times a member's name appears as 'Recruiter' called "Recruiting". I then use that query inside another query ("Membership&quot that my final membership report is based off of. When I run "Recruiting" it works flawlessly and the data is accurate. But when I insert that query into the 2nd query "Membership" and subsequently run it, the data contained becomes trashed. I currently have the "Recruiting" query linked to the Member's Database by the 'Recruiter' field. Can anyon provide any thoughts on the situation?

 

[mdthornton]

If you can post the SQL we should be able to get you going... Best Regards,
Mike

 

[Dixen]

SQL for "Recruting"
SELECT [Member's Database].Recruiter, Count([Member's Database].Recruiter) AS [Recruit Points]
FROM [Member's Database]
GROUP BY [Member's Database].Recruiter;


SQL for "Membership"
SELECT [Member's Database].Status, [Rank Order].[Rank Order], [Member's Database].[Member Name], [Member's Database].Rank, [Member's Database].Class, [Member's Database].Level, [Senority Points].[Senority Points], [Member's Points].[Loyality Points], [Recruit Points].[Recruit Points], [Senority Points.Senority Points]+[Member's Points.Loyality Points]+[Recruit Points.Recruit Points] AS [Total Primaty Points], Sum([Member's Points].[Tourney Points]) AS [SumOfTourney Points], Sum([Member's Points].[Quest Points]) AS [SumOfQuest Points], [Exp Points].[EXP Points], Sum([Member's Points].[End Game Points]) AS [SumOfEnd Game Points], [Member's Points.Tourney Points]+[Member's Points.Quest Points]+[EXP Points.EXP Points]+[Member's Points.End Game Points] AS [Total Secondary Points]
FROM [Recruit Points] INNER JOIN (((([Member's Database] INNER JOIN [Member's Points] ON [Member's Database].[Member Name] = [Member's Points].[Member Name]) INNER JOIN [Senority Points] ON [Member's Database].[Member Name] = [Senority Points].[Member Name]) INNER JOIN [Exp Points] ON [Member's Database].[Member Name] = [Exp Points].[Member Name]) INNER JOIN [Rank Order] ON [Member's Database].[Member Name] = [Rank Order].[Member Name]) ON [Recruit Points].Recruiter = [Member's Database].Recruiter
GROUP BY [Member's Database].Status, [Rank Order].[Rank Order], [Member's Database].[Member Name], [Member's Database].Rank, [Member's Database].Class, [Member's Database].Level, [Senority Points].[Senority Points], [Member's Points].[Loyality Points], [Recruit Points].[Recruit Points], [Senority Points.Senority Points]+[Member's Points.Loyality Points]+[Recruit Points.Recruit Points], [Exp Points].[EXP Points], [Member's Points.Tourney Points]+[Member's Points.Quest Points]+[EXP Points.EXP Points]+[Member's Points.End Game Points]
HAVING ((([Member's Database].Status)="A" Or ([Member's Database].Status)="R&quot);


Those are the exact contents of both queries... the first produces the field 'Recruit Points' that is later used in "Membership" to get 'Total Primary Points'... Like I said, the first query works ferfectly, the results have been verified... but once inserted into the second query the results are bugged and there is no pattern to the buggyness... Thanks again for any help you may have for me.

 

[mdthornton]

Serves me right for asking I suppose

Before we get too far there is no reference to "Recruting" in the second query. Is "Recruting" really "Recruit Points" or is the latter another table/query? Best Regards,
Mike

 

[Dixen]

Yes, my mistake... I have it written down as Recruiting, but the decision was made to change the name to make it more descriptive...

Recruiting as I have used it *IS* "Recruit Points"

 

[mdthornton]

This is a complex query to look at abstractly so bear with me.... do all of your joined tables have one for one entries. Ie is there always "Exp Points", "Rank Order", Senority Points" etc etc for each "Member Name" in the Member's Database???? If not you need to use outer (aka left) joins rather than inner joins.

To debug something like this I would go back to a simple query and build up things one table at a time so you identify where things are going wrong. Eg, start off by joining your members db with recruit points and go from there. Best Regards,
Mike

 

[Dixen]

There are two tables: Member's Database and Member's Points Joined by Member's Name. There is one record in both tables for each name. Is this what you are asking?

 

[mdthornton]

You also have joins to Exp Points, Senority Points & Rank Order. What I'm suggesting is that you start by joining JUST member's database and recruit points on recruiter check your results against recruit points alone. If this is OK add, members points, check results etc etc Best Regards,
Mike

 

[Dixen]

Okay, that was something I did not try was rebuilding it. I added the Recruit Points AFTER everything else... So, let's see where that get's me. Thank you!

 

[Dixen]

Oh man, I feel less than intelligent on this one. I "accendentally" change the link from Recruit Points to Member's Database From 'recruiter to recruiter' to 'Recruit Points.Recruit Points to Member's Database.Member Name' and the things works fine now.. ARG! Thank you for your help, had you not suggested rebuilding it, I would have NEVER gotten it figured out! Thank you again.

 

[mdthornton]

No worries, you can get too close to problems like this. Happens to me a lot, you just need a shunt to get to look at things slightly differently.
Best Regards,
Mike

 

[Dixen]

Okay, the new linking created another problem... I did start a new thread just in case someone else might see it as well.

When I link [Recruit Points] to [Member's Database] by the firld 'Recruiter' it will list all 81 records (criteria status = 'A' or 'R', 81 is thr correct number of records)... but when I change the link to 'Recruit Points.Recruit Points to Member's Database.Member Name' it only comes back with 48 records (the missing records are members who as of yet have zero recruit points... Thoughts?