By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,256 Members | 1,663 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,256 IT Pros & Developers. It's quick & easy.

Unable to enter data into left join query

P: 3
I have a program that worked fine then they needed to be able to also see the empty cells (inmate cells) on a housing unit when they ran the query. So what I had to do was take two tables and combine them into one with a query.
The two tables are..inmtinfo and tblcelldata...
inmtinfo
IN_INNUM - primary key
IN_NAME
IN_RACE
IN_BLDING
IN_SECTION
IN_CELLDRM
IN_BED
IN_SEP

tblCellData
CellID - primary key
IN_BLDING
IN_SECTION
IN_CELLDRM
IN_BED
IN_SEP

Query that joins the two...
qurCellData

SELECT inmtinfo.IN_INMNUM, inmtinfo.IN_NAME, inmtinfo.IN_BLDING, tblCellData.IN_SECTION, tblCellData.IN_CELLDRM, tblCellData.IN_BEDNUM, tblCellData.SEP
FROM inmtinfo RIGHT JOIN tblCellData ON (inmtinfo.IN_BEDNUM = tblCellData.IN_BEDNUM) AND (inmtinfo.IN_CELLDRM = tblCellData.IN_CELLDRM) AND (inmtinfo.IN_SECTION = tblCellData.IN_SECTION) AND (inmtinfo.IN_BLDING = tblCellData.IN_BLDING)
WHERE (((inmtinfo.IN_BLDING)="H" Or (inmtinfo.IN_BLDING) Is Null))
ORDER BY tblCellData.IN_SECTION, tblCellData.IN_CELLDRM, tblCellData.IN_BEDNUM;

Now what I use to have was this...query that took inmtinfo and joined it with tblESSlog using a left join so I could us this in a form and enter data into the form.
tblESSLog
ESSID - primary key
IN_INMNUM
SEP
EXERCISE
SHOWER
YARD#
RAZOR
SCREAM
MIRROR
DATE

Query that joined the two and qurESSLog (this one works) but does not give the empy cells that they need on the report.

SELECT inmtinfo.IN_INMNUM, inmtinfo.IN_NAME, inmtinfo.IN_BLDING, inmtinfo.IN_SECTION, inmtinfo.IN_CELLDRM, inmtinfo.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]));

So far I have tried to first use the new qurCellData and join it with tbleESSLog I get the correct info but can not enter data into the ESS part of the query.

SELECT qurCellData.IN_INMNUM, qurCellData.IN_NAME, qurCellData.IN_BLDING, qurCellData.Section, qurCellData.[Cell#], qurCellData.SEP, tblESSLog.EXERCISE, tblESSLog.SHOWER, tblESSLog.[YARD #], tblESSLog.RAZOR, tblESSLog.SCREAM, tblESSLog.MIRROR, tblESSLog.DATE
FROM qurCellData LEFT JOIN tblESSLog ON qurCellData.IN_INMNUM = tblESSLog.IN_INMNUM
WHERE (((qurCellData.Section)=[ENTER POD]));

When that did not work I tried this use the qurCellData and make a tblRHUCellData with it then use the tblCellData and the tblESSLog and join them.

tblRHUCellData
CellId - Primary Key
IN_INNUM
IN_NAME
IN_RACE
IN_BLDING
IN_SECTION
IN_CELLDRM
IN_BED
IN_SEP

Query that Joins them (get correct data but can not enter information into ESS part)
SELECT tblRHUCellData.IN_INMNUM, tblRHUCellData.IN_NAME, tblRHUCellData.IN_BLDING, tblRHUCellData.IN_SECTION, tblRHUCellData.IN_CELLDRM, tblRHUCellData.SEP, tblESSLog.EXERCISE, tblESSLog.SHOWER, tblESSLog.[YARD #], tblESSLog.RAZOR, tblESSLog.SCREAM, tblESSLog.MIRROR, tblESSLog.DATE
FROM tblRHUCellData LEFT JOIN tblESSLog ON tblRHUCellData.IN_INMNUM = tblESSLog.IN_INMNUM
WHERE (((tblRHUCellData.IN_SECTION)=[ENTER POD]))
ORDER BY tblRHUCellData.IN_CELLDRM;


Please help I don't know what I did wrong in the second one (why would it work fine for one but not the other)
Sep 29 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.