Query not showing correct information

[Mich2too]

We are trying to create a query to pull information from a linked table from a SQL database and a table we have created in Access. The SQL table contains a waiting list of people waiting for classes. The table (Removal)we have created has only two fields in it....the ID (which matches the ID in from the SQL field) and a yes/no check box to indicate if the name needs to be removed from the waiting list.

I have set up the query so that all of the fields from the SQL waiting list are shown and only those fields from the Removal table that match are shown. The criteria for the query is that the yes/no box must indicate no.

The problem is that when new people are added to the waiting list, they do not appear in the query.

I am not familiar with code, so you may have to lead the blind a bit if that is where we need to go with this issue.

THANKS for any assistance any of you can give us.

 

[Remou]

Have you tried adding Or Is Null after No for the Yes No criteria?

 

[Mich2too]

Using the Is Null in any part of the criteria, it comes back with a "no records" message and will not display the outcome.

 

[Remou]

Perhaps I did not explain right. I mean that the SQL would look something like:
[tt]SELECT tblPersons.PersonsKey, tblYesNo.YesNo
FROM tblPersons LEFT JOIN tblYesNo ON tblPersons.PersonsKey = tblYesNo.PersonsKey
WHERE (((tblYesNo.YesNo)=False Or (tblYesNo.YesNo) Is Null));[/tt]
Where YesNo field is a Yes/No field type.

 

[Golom]

It may be that you are adding new people to the waiting list (i.e. the SQL table) but you're not adding them to the Removal table in Access. If that's what's happening then an INNER JOIN between the two tables will show only those records where there is a match in both tables.

You can try a LEFT JOIN or you can modify your system to append a new record to "Removal" whenever one is added to "WaitingList".

 

[Mich2too]

Here's the code from the SQL view:

SELECT dbo_EDINQUIRIES.INQUIRY_ID, dbo_EDINQUIRIES.INQUIRY_DATE, dbo_EDINQUIRIES.NAME_LAST, dbo_EDINQUIRIES.NAME_FIRST, dbo_EDINQUIRIES.COURSE_ID, dbo_EDCOURSES.DESCRIPTION, dbo_EDINQUIRIES.ADR_CITY, dbo_EDINQUIRIES.ADR_COUNTY, dbo_EDINQUIRY_PHONES.PHONE, dbo_EDINQUIRIES.USER_FIELD, [Waiting List - Remove These].Remove
FROM ((dbo_EDINQUIRIES OUTER JOIN dbo_EDINQUIRY_PHONES ON dbo_EDINQUIRIES.INQUIRY_ID = dbo_EDINQUIRY_PHONES.INQUIRY_ID) INNER JOIN dbo_EDCOURSES ON dbo_EDINQUIRIES.COURSE_ID = dbo_EDCOURSES.COURSE_ID) LEFT JOIN [Waiting List - Remove These] ON dbo_EDINQUIRIES.INQUIRY_ID = [Waiting List - Remove These].[id#]
GROUP BY dbo_EDINQUIRIES.INQUIRY_ID, dbo_EDINQUIRIES.INQUIRY_DATE, dbo_EDINQUIRIES.NAME_LAST, dbo_EDINQUIRIES.NAME_FIRST, dbo_EDINQUIRIES.COURSE_ID, dbo_EDCOURSES.DESCRIPTION, dbo_EDINQUIRIES.ADR_CITY, dbo_EDINQUIRIES.ADR_COUNTY, dbo_EDINQUIRY_PHONES.PHONE, dbo_EDINQUIRIES.USER_FIELD, [Waiting List - Remove These].Remove
HAVING ((([Waiting List - Remove These].Remove)=No))
ORDER BY dbo_EDINQUIRIES.INQUIRY_DATE DESC , dbo_EDINQUIRIES.NAME_LAST, dbo_EDINQUIRIES.NAME_FIRST, dbo_EDINQUIRIES.COURSE_ID;


Where should I be making the Outer join change?

Am not sure how to go about modifying our system as the program which creates the SQL table data and the Access Database file are not open at the same time.

 

[Golom]

Try this. I just introduced some table aliases to ease reading.

SELECT Q.INQUIRY_ID, Q.INQUIRY_DATE, Q.NAME_LAST, Q.NAME_FIRST, Q.COURSE_ID, C.DESCRIPTION, Q.ADR_CITY, Q.ADR_COUNTY, P.PHONE, Q.USER_FIELD, RT.Remove

FROM ((dbo_EDINQUIRIES Q OUTER JOIN dbo_EDINQUIRY_PHONES P ON Q.INQUIRY_ID = P.INQUIRY_ID) 
INNER JOIN dbo_EDCOURSES C ON Q.COURSE_ID = C.COURSE_ID ) 
LEFT JOIN [Waiting List - Remove These] RT ON Q.INQUIRY_ID = RT.[id#]

WHERE RT.Remove=FALSE [COLOR=red]OR Rt.Remove IS NULL[/color]

GROUP BY Q.INQUIRY_ID, Q.INQUIRY_DATE, Q.NAME_LAST, Q.NAME_FIRST, Q.COURSE_ID, C.DESCRIPTION, Q.ADR_CITY, Q.ADR_COUNTY, P.PHONE, Q.USER_FIELD, RT.Remove

ORDER BY Q.INQUIRY_DATE DESC , Q.NAME_LAST, Q.NAME_FIRST, Q.COURSE_ID;

Generally, if you set criteria on the right table in a LEFT JOIN, the effect is that the LEFT JOIN works like an INNER JOIN.

 

[Mich2too]

I am not finding anywhere in the original SQL that has

WHERE RT.Remove=FALSE statement. Thought it was just me...but had a second person read through it and it still can't be found.

Like I said originally....I'm not familiar with SQL other than to view it. So...is this entire line (including your red text) something that I need to adding to the SQL??

 

[Golom]

Originally you had

RT.Remove=No

however there are intrinsic constants TRUE and FALSE that SQL recognizes as -1 and 0 respectively. A Boolean is really a numeric BIT type that may have values -1 (TRUE) or 0(False). I just changed "No" to "FALSE" because that's how SQL is expecting to do comparisons on Boolean data types.

 

[Mich2too]

We are not even finding the word WHERE any where in the SQL statement......

 

[Mich2too]

There is the word HAVING in the SQL statement and have tried changing it from

HAVING ((([Waiting List - Remove These].Remove)=0))

to

HAVING ((([Waiting List - Remove These].Remove)=0)) or ((( [Waiting List - Remove These].Remove) IS NULL))

and received a "no records" message again.

Then I tried changing that entire HAVE statement to

WHERE [Waiting List - Remove These].Remove=FALSE OR [Waiting List - Remove These].Remove IS NULL

and get a Snytax error message.

HELP????

 

[Golom]

True. The original was "HAVING ...".

A HAVING clause is used to apply constraints to aggregate functions. For example

HAVING SUM(SomeField) > 100

The SQL query designer in Access puts constraints into a HAVING clause if you have a GROUP BY (and you do) even if they are not in fact aggregate function constraints.

Your constraints are on an individual field ... not on aggregate functions. Individual field constraints should appear in a WHERE clause (although they will probably work in HAVING in your case.)

BTW: You don't really need the GROUP BY because your query has no aggregate functions and every field in the SELECT is also in the GROUP BY.

 

[Mich2too]

K.....Access put the GROUP BY statement in. Should I be taking it out??

So....what exactly should I be doing to make this query work? Seem to be right back where we started.

 

[Golom]

If you just changed "HAVING" to "WHERE" and did nothing else then you probably have

SELECT ...
FROM   ...
GROUP BY ...
WHERE ...
ORDER BY ...

the correct sequence is however

SELECT ...
FROM   ...[COLOR=red]
WHERE ...
GROUP BY ...[/color]
ORDER BY ...

 

[Mich2too]

Okay....have changed the sequence as given and am using the WHERE statement as I had listed earlier. When trying to view the results....get the same "No Records" message.

 

[Golom]

According to Books On Line

Microsoft SQL Server uses these SQL-92 keywords for outer joins specified in a FROM clause:

LEFT OUTER JOIN or LEFT JOIN
RIGHT OUTER JOIN or RIGHT JOIN
FULL OUTER JOIN or FULL JOIN


From which I infer that just "OUTER JOIN" should probably be "LEFT OUTER JOIN" or just "LEFT JOIN

 

[lespaul]

Way back at the beginning, Golom asked if the Removal table is updated with the new people when the Waiting List table had new people added to it and you never answered. I'm guessing there's not.

If you have:

[tt]
WaitingList
ID


Removal
ID
Remove Yes/No
[/tt]

and you are trying to find people in the WaitingList table that have a matching record in the removal table that has a 'No' in the Remove field, there MUST be a process that adds the new people to the Removal table since that's the SOURCE of the Yes/No field. There's NO WAY that you will ever see the new people in WaitingList if they are not added to Removal.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Golom]

Leslie

It's not that there's "NO WAY" (a LEFT JOIN will display them for example.) It's just that you won't have a "Yes" or "No" result for those missing IDs. You will have a NULL result and the interpretation of that is ???.

 

[lespaul]

true enough....but I'm not sure he's getting it!

Good luck!

les

 

[Mich2too]

Way back in the beginning....I stated that I am NOTfamilar with SQL coding....which everyone keeps ignoring. I have been doing my best to understand your programming language. All of the joint this joint that is beyond me...I'm used to inputting my criteria into the design veiw and going from there. But I'm still trying my best to keep up and try what is being suggested.

I also stated that the SQL program and the Access file are not typically opened at the same time and am able to relate to the issues caused by this. However, does anyone have any suggestions how to cure this issue so that these new folk's ID's ARE updated into the Remove table? This is really where the problem lies from my understanding of things.

And a minor FYI...I'm a female not a male.....