Missing Data in Query Results

R

RNUSZ@OKDPS

I have two tables that I need to perform a query on, but my query misses some
data.

Table 1) named TST_FR_CASE_RECORDS, has a two part primary key field. 1st
field is: CASE_NUM_YR and 2nd field is CASE_NUM

Table 2) named TST_FR_CASE_OTHERS, has the same primary key field layout.
1st field is: CASE_NUM_YR and 2nd field is CASE_NUM. There is a secondary
index to this table (field # 3) SEQ_NUM.

Table 1 is the Parent Table, Table 2 is the child table. There is a
possibility that there may never be a matching record in table 2 for a record
in table 1. There is always a possiblity that there may be more than 1
records in table 2 that links to table 1, therefore a one-to-many condition
exists.

My query written as follows, produces a recordset results that lists all
records of table 1 that have a matching record(s) in table 2. I need this
query to have a result of all records in table 1 included, regardless if
there is a matching record in table 2. Can someone assist in helping me to
correct this query.

Query Follows:

SELECT TST_FR_CASE_RECORDS.CASE_NUM_YR, TST_FR_CASE_RECORDS.CASE_NUM,
TST_FR_CASE_RECORDS.PRTD_CDE, TST_FR_CASE_RECORDS.TYPIST_INIT_TXT,
TST_FR_CASE_RECORDS.ACC_DATE, TST_FR_CASE_RECORDS.CNTY_NUM,
TST_FR_CASE_RECORDS.HRG_DATE, TST_FR_CASE_RECORDS.HRG_TIME_TXT,
TST_FR_CASE_RECORDS.HRG_AM_PM_TXT, TST_FR_CASE_RECORDS.LOC_CDE,
TST_FR_CASE_RECORDS.ATTY_NUM, TST_FR_CASE_RECORDS.LIC_FIRST_NME,
TST_FR_CASE_RECORDS.LIC_MIDDLE_NME, TST_FR_CASE_RECORDS.LIC_LAST_NME,
TST_FR_CASE_RECORDS.LIC_SUBT_TXT, TST_FR_CASE_RECORDS.LIC_ADDR_TXT,
TST_FR_CASE_RECORDS.LIC_CITY_NME, TST_FR_CASE_RECORDS.LIC_STATE_CDE,
TST_FR_CASE_RECORDS.LIC_ZIP_CDE, TST_FR_CASE_RECORDS.DOA_NME,
TST_FR_CASE_RECORDS.DOA_ADDR_TXT, TST_FR_CASE_RECORDS.DOA_CITY_NME,
TST_FR_CASE_RECORDS.DOA_STATE_CDE, TST_FR_CASE_RECORDS.DOA_ZIP_CDE,
TST_FR_CASE_RECORDS.FLAG_CDE, TST_FR_CASE_RECORDS.BATCH_DATE,
TST_FR_CASE_RECORDS.BATCH_NUM, TST_FR_CASE_OTHERS.OTHER_NME,
TST_FR_CASE_OTHERS.FIRM_NME, TST_FR_CASE_OTHERS.OTHER_ADDR_TXT,
TST_FR_CASE_OTHERS.OTHER_CITY_NME, TST_FR_CASE_OTHERS.OTHER_STATE_CDE,
TST_FR_CASE_OTHERS.OTHER_ZIP_CDE, TST_FR_HEARING_LOC.LOC_NME,
TST_FR_HEARING_LOC.ROOM_NME, TST_FR_HEARING_LOC.MEMO_TXT,
TST_FR_ATTORNEY.FIRST_NME, TST_FR_ATTORNEY.MIDDLE_NME,
TST_FR_ATTORNEY.LAST_NME, TST_FR_ATTORNEY.SUBTITLE_TXT,
TST_FR_ATTORNEY.FIRM_NME, TST_FR_ATTORNEY.ADDR1_TXT,
TST_FR_ATTORNEY.ADDR2_TXT, TST_FR_ATTORNEY.CITY_NME,
TST_FR_ATTORNEY.STATE_CDE, TST_FR_ATTORNEY.ZIP_CDE
FROM ((TST_FR_CASE_RECORDS INNER JOIN TST_FR_ATTORNEY ON
TST_FR_CASE_RECORDS.ATTY_NUM = TST_FR_ATTORNEY.ATTY_NUM) INNER JOIN
TST_FR_CASE_OTHERS ON (TST_FR_CASE_RECORDS.CASE_NUM =
TST_FR_CASE_OTHERS.CASE_NUM) AND (TST_FR_CASE_RECORDS.CASE_NUM_YR =
TST_FR_CASE_OTHERS.CASE_NUM_YR)) INNER JOIN TST_FR_HEARING_LOC ON
TST_FR_CASE_RECORDS.LOC_CDE = TST_FR_HEARING_LOC.LOC_CDE
ORDER BY TST_FR_CASE_RECORDS.CASE_NUM_YR;


Thanks
 
C

Chris2

RNUSZ@OKDPS said:
I have two tables that I need to perform a query on, but my query misses some
data.

Table 1) named TST_FR_CASE_RECORDS, has a two part primary key field. 1st
field is: CASE_NUM_YR and 2nd field is CASE_NUM

Table 2) named TST_FR_CASE_OTHERS, has the same primary key field layout.
1st field is: CASE_NUM_YR and 2nd field is CASE_NUM. There is a secondary
index to this table (field # 3) SEQ_NUM.

Table 1 is the Parent Table, Table 2 is the child table. There is a
possibility that there may never be a matching record in table 2 for a record
in table 1. There is always a possiblity that there may be more than 1
records in table 2 that links to table 1, therefore a one-to-many condition
exists.

My query written as follows, produces a recordset results that lists all
records of table 1 that have a matching record(s) in table 2. I need this
query to have a result of all records in table 1 included, regardless if
there is a matching record in table 2. Can someone assist in helping me to
correct this query.

Query Follows:

SELECT TST_FR_CASE_RECORDS.CASE_NUM_YR
,TST_FR_CASE_RECORDS.CASE_NUM
,TST_FR_CASE_RECORDS.PRTD_CDE
,TST_FR_CASE_RECORDS.TYPIST_INIT_TXT
,TST_FR_CASE_RECORDS.ACC_DATE
,TST_FR_CASE_RECORDS.CNTY_NUM
,TST_FR_CASE_RECORDS.HRG_DATE
,TST_FR_CASE_RECORDS.HRG_TIME_TXT
,TST_FR_CASE_RECORDS.HRG_AM_PM_TXT
,TST_FR_CASE_RECORDS.LOC_CDE
,TST_FR_CASE_RECORDS.ATTY_NUM
,TST_FR_CASE_RECORDS.LIC_FIRST_NME
,TST_FR_CASE_RECORDS.LIC_MIDDLE_NME
,TST_FR_CASE_RECORDS.LIC_LAST_NME
,TST_FR_CASE_RECORDS.LIC_SUBT_TXT
,TST_FR_CASE_RECORDS.LIC_ADDR_TXT
,TST_FR_CASE_RECORDS.LIC_CITY_NME
,TST_FR_CASE_RECORDS.LIC_STATE_CDE
,TST_FR_CASE_RECORDS.LIC_ZIP_CDE
,TST_FR_CASE_RECORDS.DOA_NME
,TST_FR_CASE_RECORDS.DOA_ADDR_TXT
,TST_FR_CASE_RECORDS.DOA_CITY_NME
,TST_FR_CASE_RECORDS.DOA_STATE_CDE
,TST_FR_CASE_RECORDS.DOA_ZIP_CDE
,TST_FR_CASE_RECORDS.FLAG_CDE
,TST_FR_CASE_RECORDS.BATCH_DATE
,TST_FR_CASE_RECORDS.BATCH_NUM
,TST_FR_CASE_OTHERS.OTHER_NME
,TST_FR_CASE_OTHERS.FIRM_NME
,TST_FR_CASE_OTHERS.OTHER_ADDR_TXT
,TST_FR_CASE_OTHERS.OTHER_CITY_NME
,TST_FR_CASE_OTHERS.OTHER_STATE_CDE
,TST_FR_CASE_OTHERS.OTHER_ZIP_CDE
,TST_FR_HEARING_LOC.LOC_NME
,TST_FR_HEARING_LOC.ROOM_NME
,TST_FR_HEARING_LOC.MEMO_TXT
,TST_FR_ATTORNEY.FIRST_NME
,TST_FR_ATTORNEY.MIDDLE_NME
,TST_FR_ATTORNEY.LAST_NME
,TST_FR_ATTORNEY.SUBTITLE_TXT
,TST_FR_ATTORNEY.FIRM_NME
,TST_FR_ATTORNEY.ADDR1_TXT
,TST_FR_ATTORNEY.ADDR2_TXT
,TST_FR_ATTORNEY.CITY_NME
,TST_FR_ATTORNEY.STATE_CDE
,TST_FR_ATTORNEY.ZIP_CDE
FROM ((TST_FR_CASE_RECORDS
INNER JOIN
TST_FR_ATTORNEY
ON TST_FR_CASE_RECORDS.ATTY_NUM = TST_FR_ATTORNEY.ATTY_NUM)
LEFT JOIN
TST_FR_CASE_OTHERS
ON (TST_FR_CASE_RECORDS.CASE_NUM = TST_FR_CASE_OTHERS.CASE_NUM)
AND (TST_FR_CASE_RECORDS.CASE_NUM_YR =
TST_FR_CASE_OTHERS.CASE_NUM_YR))
INNER JOIN
TST_FR_HEARING_LOC
ON TST_FR_CASE_RECORDS.LOC_CDE = TST_FR_HEARING_LOC.LOC_CDE
ORDER BY TST_FR_CASE_RECORDS.CASE_NUM_YR;


That's untested, but try it.


Sincerely,

Chris O.
 
R

RNUSZ@OKDPS

Chris2,

My thanks in your support of my query question. Your response was very
accurate, I did make one additional changed, but would like to post the final
resulting query.

SELECT TST_FR_CASE_RECORDS.CASE_NUM_YR,
TST_FR_CASE_RECORDS.CASE_NUM,
TST_FR_CASE_RECORDS.PRTD_CDE,
TST_FR_CASE_RECORDS.TYPIST_INIT_TXT,
TST_FR_USER_TABLE.USER_NME,
TST_FR_CASE_RECORDS.ACC_DATE,
TST_FR_CASE_RECORDS.HRG_DATE,
TST_FR_CASE_RECORDS.HRG_TIME_TXT,
TST_FR_CASE_RECORDS.HRG_AM_PM_TXT,
TST_FR_CASE_RECORDS.LOC_CDE,
TST_FR_HEARING_LOC.LOC_NME,
TST_FR_HEARING_LOC.CITY_NME,
TST_FR_HEARING_LOC.STATE_CDE,
TST_FR_HEARING_LOC.ROOM_NME,
TST_FR_HEARING_LOC.MEMO_TXT,
TST_FR_CASE_RECORDS.LIC_FIRST_NME,
TST_FR_CASE_RECORDS.LIC_MIDDLE_NME,
TST_FR_CASE_RECORDS.LIC_LAST_NME,
TST_FR_CASE_RECORDS.LIC_ADDR_TXT,
TST_FR_CASE_RECORDS.LIC_CITY_NME,
TST_FR_CASE_RECORDS.LIC_STATE_CDE,
TST_FR_CASE_RECORDS.LIC_ZIP_CDE,
TST_FR_CASE_RECORDS.DOA_NME,
TST_FR_CASE_RECORDS.DOA_ADDR_TXT,
TST_FR_CASE_RECORDS.DOA_CITY_NME,
TST_FR_CASE_RECORDS.DOA_STATE_CDE,
TST_FR_CASE_RECORDS.DOA_ZIP_CDE,
TST_FR_CASE_RECORDS.FLAG_CDE,
TST_FR_CASE_RECORDS.BATCH_DATE,
TST_FR_CASE_RECORDS.BATCH_NUM,
TST_FR_CASE_OTHERS.SEQ_NUM,
TST_FR_CASE_OTHERS.OTHER_NME,
TST_FR_CASE_OTHERS.FIRM_NME,
TST_FR_CASE_OTHERS.OTHER_ADDR_TXT,
TST_FR_CASE_OTHERS.OTHER_CITY_NME,
TST_FR_CASE_OTHERS.OTHER_STATE_CDE,
TST_FR_CASE_OTHERS.OTHER_ZIP_CDE,
TST_FR_CASE_RECORDS.ATTY_NUM,
TST_FR_ATTORNEY.FIRST_NME,
TST_FR_ATTORNEY.MIDDLE_NME,
TST_FR_ATTORNEY.LAST_NME,
TST_FR_ATTORNEY.SUBTITLE_TXT,
TST_FR_ATTORNEY.FIRM_NME,
TST_FR_ATTORNEY.ADDR1_TXT,
TST_FR_ATTORNEY.ADDR2_TXT,
TST_FR_ATTORNEY.CITY_NME,
TST_FR_ATTORNEY.STATE_CDE,
TST_FR_ATTORNEY.ZIP_CDE
FROM (((TST_FR_CASE_RECORDS
INNER JOIN TST_FR_ATTORNEY
ON TST_FR_CASE_RECORDS.ATTY_NUM = TST_FR_ATTORNEY.ATTY_NUM)
LEFT JOIN TST_FR_CASE_OTHERS
ON (TST_FR_CASE_RECORDS.CASE_NUM = TST_FR_CASE_OTHERS.CASE_NUM)
AND (TST_FR_CASE_RECORDS.CASE_NUM_YR = TST_FR_CASE_OTHERS.CASE_NUM_YR))
INNER JOIN TST_FR_HEARING_LOC
ON TST_FR_CASE_RECORDS.LOC_CDE = TST_FR_HEARING_LOC.LOC_CDE)
INNER JOIN TST_FR_USER_TABLE
ON TST_FR_CASE_RECORDS.TYPIST_INIT_TXT = TST_FR_USER_TABLE.TYPIST_INIT_TXT
ORDER BY TST_FR_CASE_RECORDS.CASE_NUM_YR, TST_FR_CASE_RECORDS.CASE_NUM;

The above query could be modified and used to query by primary key fields
two tables that are interelated via Parent/Child link, (somehting like a
product and order table)
It helps merge the two record tables into one query, and does not drop any
record from table 1 that does not have records in table two. It keeps the
data intact.

Thanks again Chris.... and the others that support people like us just
learning the VB side of MsAccess.

It's much appreciated, and helps make our job easier.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top