I thought that POD was something like Paid On Date. I guess in this case a
POD is a pod!
Let's try to simplify things. Run the first two queries and see what they
return. The first should be just fine unless I mistyped it. The second could
cause problems. The last two are examples of Left joins. That's where there
is a record in one table but no matching record in the other. In an inner
join, no record is returned. In a left join, a record is returned with data
from one table but, possibly, no data from the other.
Think of Customers and Orders. If you did an Inner Join between those two
tables, only Customers with Orders would be returned. With a Left Join, the
return would also capture new Customers who haven't placed an Order yet. The
Order info is just blank.
SELECT
tblESSLog.SEP,
tblESSLog.EXERCISE,
tblESSLog.SHOWER,
tblESSLog.[YARD #],
tblESSLog.RAZOR,
tblESSLog.SCREAM,
tblESSLog.MIRROR,
tblESSLog.DATE
FROM tblESSLog;
SELECT inmtinfo.IN_INMNUM,
inmtinfo.IN_NAME,
inmtinfo.IN_BLDING,
inmtinfo.IN_SECTION,
inmtinfo.IN_CELLDRM
FROM inmtinfo
WHERE inmtinfo.IN_BLDING)="H"
AND inmtinfo.IN_SECTION)=[ENTER POD];
SELECT inmtinfo.IN_INMNUM,
inmtinfo.IN_NAME,
inmtinfo.IN_BLDING,
inmtinfo.IN_SECTION,
inmtinfo.IN_CELLDRM,
tblESSLog.SEP,
tblESSLog.EXERCISE,
tblESSLog.SHOWER,
tblESSLog.[YARD #],
tblESSLog.RAZOR,
tblESSLog.SCREAM,
tblESSLog.MIRROR,
tblESSLog.DATE
FROM tblESSLog LEFT JOIN inmtinfo ON tblESSLog.IN_INMNUM = inmtinfo.IN_INMNUM
WHERE (((inmtinfo.IN_BLDING)="H")
and ((inmtinfo.IN_SECTION)=[ENTER POD]));
SELECT inmtinfo.IN_INMNUM,
inmtinfo.IN_NAME,
inmtinfo.IN_BLDING,
inmtinfo.IN_SECTION,
inmtinfo.IN_CELLDRM,
tblESSLog.SEP,
tblESSLog.EXERCISE,
tblESSLog.SHOWER,
tblESSLog.[YARD #],
tblESSLog.RAZOR,
tblESSLog.SCREAM,
tblESSLog.MIRROR,
tblESSLog.DATE
FROM inmtinfo LEFT JOIN tblESSLog ON inmtinfo.IN_INMNUM = tblESSLog.IN_INMNUM
WHERE (((inmtinfo.IN_BLDING)="H")
and ((inmtinfo.IN_SECTION)=[ENTER POD]));
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Michelle said:
What is the difference between and INNER JOIN and an LEFT JOIN.
I work at a prison and this would be H = Housing Unit or RHU (Restricted
Housing Unit)
and the POD would be 1 of the 4 sections within the Unit...A, B, C or D no
date at all.
Jerry Whittle said:
There's an INNER JOIN. If you don't have a matching IN_INMNUM in both tables,
you will not get a record. You may need a LEFT JOIN.
The WHERE clause contains an AND. To return records both parts of it must be
true. If a record doesn't have a IN_BLDING = "H" and IN_SECTION = what you
type in, that record will not be returned.
Speaking of IN_SECTION, what kind of data is it? [ENTER POD] sounds like it
might be asking for a date; however, your SQL doesn't show you defining the
parameter's datatype. All kinds of wrong things can happen if IN_SECTION is a
date field being treated like text or, even worse, a text field treated like
a date.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Here is the code from another one I am working on it has the same problem...I
do not have the other DB on this PC.
SELECT inmtinfo.IN_INMNUM, inmtinfo.IN_NAME, inmtinfo.IN_BLDING,
inmtinfo.IN_SECTION, inmtinfo.IN_CELLDRM, tblESSLog.SEP, tblESSLog.EXERCISE,
tblESSLog.SHOWER, tblESSLog.[YARD #], tblESSLog.RAZOR, tblESSLog.SCREAM,
tblESSLog.MIRROR, tblESSLog.DATE
FROM tblESSLog INNER JOIN inmtinfo ON tblESSLog.IN_INMNUM = inmtinfo.IN_INMNUM
WHERE (((inmtinfo.IN_BLDING)="H") AND ((inmtinfo.IN_SECTION)=[ENTER POD]));
:
Help us help you: Show us the SQL. Open the query in SQL view and copy and
past it here. Information on primary keys and relationships would be a nice
touch too.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I have a DB that has 4 tables.
EMPINFO - linked table from a data push
ESSLOG
JOBCHG
INSP
I created a query that would use some info from the EMPINFO file and ESSLOG.
It prompst for the department and should return all the employee's Numbers,
Names and Office #'s for that Department. The user would then enter the
information needed for the ESSLOG part of the Query...example
I put in Business when I am prompted for the Department it would give me the
follwoing...(first 3 pulled fromt eh EMPINFO file) (Last 4 needed user to
enter data)
Emp # Name Office # Date Pulled Passed Failed
Percentage
5555 Smith, Lisa 1.25
however when I run it I get nothing it will not even pull the names from the
bussiness office...thaks for any help you can give me
