Can you force a record to display? 1

[Dixen]

I use several complicated Queries in my database. Some of my queries may not display all avail records that meet a given criteria because of "incomeplete" or missing data. Is there a way to force these records to show up in a query anyway?

i.e. I have Status, Member Name and several point categories. If one of these points is missing in a given record and my query criteria is based on the 'Status' field, the record will not display even though it does fit the criteria of (for example) Status = 'A' or 'R'... Thoughts?

 

[HowieG]

I can't tell for sure from your description, but it sounds to me like a problem with NULLs. If for example, the Status field for some record is NULL, then it will not meet the criteria of Status = 'A', and it will also not meet the criteria of Status <> 'A'. NULLs are an animal unto themselves...

To test for a null, use ISNULL(FieldName), which will give a true/false answer, or (generally easier), use
NZ(FieldName). NZ(FieldName) will return a default value for a field if it happens to be NULL. For a text field, it will return &quot;&quot;, for a number field it will return 0, etc. If you want it to specifically return something else, use NZ(FieldName, ValueIfNull).

 

[Dixen]

Actually, I do know for sure that every record does have a value for 'Status'. The problem is the 'Recruiter' field.

I have one Query that counts the number of times a member's name appears in that column:

*snip*
SELECT [Member's Database].[Recruiter], Count([Member's
Database].[Recruiter]) AS [Recruit Points]

FROM [Member's Database]

GROUP BY [Member's Database].[Recruiter];

*end snip*

I then have a second query that uses the field [Recruit Points] in it:

*snip*
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].[Member Name]

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)=&quot;A&quot; Or ([Member's
Database].Status)=&quot;R&quot);

*end snip*

When I run the second Query there *should* be 81 records that show up, currently there are only 48. The records that do not show up are those members whose name will not be in the [Recruiter] field as they have never recruited another player.

 

[HowieG]

So, am I to understand that the field [Member's Database].[Recruiter] is empty for a certain number of records in [Member's Database]?

If so, that's your problem. The first query is grouping by that field, so all the records that have a blank in that field are treated as one record instead of however many you expected.

If that is the case, you need another field in that query to be grouped on. Something like PlayerID that would be unique and exist for each player, so that your result is limited to one record for each player (whether or not they have a value in the Recruiter field).

 

[Dixen]

Not quite. Each record in the Member's Database has SOMETHING listed as [Recruiter]. The problem is that not every Member has recruited another player.

The first query is merely counting the number of times a given Member name appears as [Recruiter]. But if a member's name does not appear in that field then obviously there is no count on their name. So when the first query is returning values it is only returning 48 values... Then only the records that correspond with those 48 values are being displayed in the seconed query, ever though 81 records fit the query criteria (Status = 'A' or 'R').

If I try to include [Member Name] in the first query, Like so:

*snip
SELECT [Member's Database].[Member Name], [Member's
Database].Recruiter, Count([Member's Database].Recruiter)
AS [Recruit Points]
FROM [Member's Database]
GROUP BY [Member's Database].[Member Name], [Member's
Database].Recruiter;
*end snip*

Then the query simply lists [Member Name] [Recruiter] and &quot;1&quot; as [Recruit Points] for each record.

I'm starting to think I finally found something that really can't be done in SQL...

 

[HowieG]

I'm starting to think I finally found something that really can't be done in SQL...
Not likely

See if my understanding is correct now.
- You have a list of Members in a table.
- You have more than 81 members in that table.
- Of that complete member list, 81 have either 'A' or 'R' as their status.
- You want the resulting query (the second bigger one you showed) to return 81 records with various other information about these members.

If so, then it would make sense that you want the first query to return a list of members (ALL members, since there is no criteria listed in it). This list of ALL members would then get passed to the second query which would apply the filter based on status.

The first solution that comes to mind (there's usually more than one way to do these things), is to add a third query. Leave the first query as you had it originally so it returns a list of members who have recruited others. Then add a new query that compares the list of those that have recruited others to the full list of members. The output would be the full list with the Recruit Points field for those that have it, and NULL for those that don't.

Try something like:

SELECT [Member's Database].[Member Name], Query1.[Recruit Points]
FROM [Member's Database] LEFT JOIN Query1 ON [Member's Database].[Member Name] = Query1.Recruiter;

You might be able to roll this into your larger second query, but it's a little difficult to do manually.

I would also suggest you use some sort of MemberID field instead of Member Name to do the linking, but I didn't see any in your queries.

 

[Dixen]

Okay, Problem fixed... I just need to know one more thing. Where do you put an ISNull or NZ expression in one of the (now) three queries so that the end report has &quot;0&quot; instead of &quot; &quot;?

First:
SELECT [Member's Database].[Recruiter], Count([Member's Database].[Recruiter]) AS [Recruit Points Base]
FROM [Member's Database]
GROUP BY [Member's Database].[Recruiter];

Second:
SELECT [Member's Database].[Member Name], [Recruit Points Base].[Recruit Points Base] AS [Recruit Points Final]
FROM [Member's Database] LEFT JOIN [Recruit Points Base] ON [Member's Database].[Member Name]=[Recruit Points Base].[Recruiter];

Third:
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 Final].[Recruit Points Final], [Senority Points.Senority Points]+[Member's Points.Loyality Points]+[Recruit Points Final.Recruit Points Final] 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 Final] 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 Final].[Member Name]=[Member's Database].[Member Name]
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 Final].[Recruit Points Final], [Senority Points.Senority Points]+[Member's Points.Loyality Points]+[Recruit Points Final.Recruit Points Final], [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)=&quot;A&quot; Or ([Member's Database].Status)=&quot;R&quot);


Needless to say I need to mark a number of your posts as helpful! Thank you very much again for the help, and the for this one in advance!

 

[HowieG]

Just an aside...I would still recommend you make a unique ID field for your members (a simple unique primary key for each table). So if each of your members has a MemberID field (you can make it autonumber), then you can do the linking on that instead of the name. Imagine the chaos if you happen to get another user with the same name (admittedly unlikely in a small database). More likely, however, is misspelling someone's name. After you've used his name several times in several tables, changing the spelling of one causes the others to not link properly.

Back to your question...I recommend the NZ() function over the iif(isnull(),,) routine.
Use like:

NZ([Recruit Points Final].[Recruit Points Final])

in the SELECT part of the statement wherever a NULL might be returned. If you're adding fields together, wrap each in a NZ(), so:

NZ(field1) + NZ(field2) AS Field1and2

NZ will return a 0 or &quot;&quot; as it thinks is appropriate with the field type. Generally it does it fine, but to override, use: NZ(field, &quot;abc&quot to return &quot;abc&quot; where there is a null.

 

[Dixen]

Okay, so I would have to use the NZ() in the 3rd query as oppossed to either of the other two since I am using the Recruit Points as part of the Primary Points Exp?

 

[HowieG]

Yes, I'd use it in the third query.