Union Query Issue

[Michelle]

I have the follwoing query and it is used in a form to input data, the
problem is that once you put data in the ESSLog table when you open the query
back up it does not show the related info from the inmtinfo table.
Basicly what i am trying to do is once a day they have to fill this form
out, the next day it should still pull all of the data from the inmtinfo file
that meets the "H" and Pod that is entered with the rest of the fields being
blank...
I guess it would be if the Date does not match "today" then return all.

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])
AND ((tblESSLog.DATE) Is Null));

 

[Phil]

1.) This is not a union query.
2.) I am not sure what your problem is. Trying to visualize what you
are doing is giving me a headache. I have a wild guess...

You query is only pulling records if the related records in tblESSLog
have no date. If I was building a form for a daily activity log, I
would put a default of today's date in it, since after all, this is a
log, tracking what and when. In any event, I would at least have a
FIELD for date, and expect users to enter one. If you put a date in
this field as you enter the data, it will not come back next time,
because you are only looking for those records where tblESSLog.DATA is null.

What I THINK you want to do is pull out all of the info from inmtinfo
WITHOUT referencing any log data, since you are not LOOKING at log data,
but simply creating more of it. Liek the italian driver in cannonball
run said" What;s behind yu is not important." Your form will update log
data, (preferably on a subform) but your original query should not be
looking at log data. Build a query that just references the infotable,
and base your form on that. Then create a subform to fill in the
blanks, some from the info table, like inmate ID#, (IN_INNUM?) , the
rest filled on by the user on the form.

Consider a simple two table invoice system. One table will have your
customer, one table will have your invoice/purchase data. You will tie
the two together with the customer number. When you go to create a NEW
invoice, you do not want a query that will reference an old invoice, as
nothing on that old invoice will be relevant. Tthe only thing that the
old invoice and new invoice will have in common, is the custoemr
information. Each day of your log will not have neccessarily anything
in common with the previous day's log, an inmate may not take a shower,
or hit the excercise yard today, but they did yesterday. The only thing
in common between the two logs is the inmate whose activities you are
logging.

I have the follwoing query and it is used in a form to input data, the
problem is that once you put data in the ESSLog table when you open the query
back up it does not show the related info from the inmtinfo table.
Basicly what i am trying to do is once a day they have to fill this form
out, the next day it should still pull all of the data from the inmtinfo file
that meets the "H" and Pod that is entered with the rest of the fields being
blank...
I guess it would be if the Date does not match "today" then return all.

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])
AND ((tblESSLog.DATE) Is Null));