G
Guest
My appologies for double posting in two locations, forms and queries. I had
meant to place it in queries.
I was hoping that someone could help me with this query and its results set.
I have a Access Application being written to replace a auto crash database.
This
application keeps track of people involved, depth of injuries or fatalities,
monitarial costs, locations, etc. This application needs to generate
multiple reports
and forms. One of which is a form letter for legal hearing on crash, so a
form letter
is sent to all people involved in crash. This application has 5 tables that
are
joined in a query to create this report. The primary table is
TST_FR_CASE_RECORDS,
with two-part key of CASE_NUM_YR & CASE_NUM. This table has a one to many
relationship with table TST_FR_CASE_OTHERS, with two-part key of CASE_NUM_YR
& CASE_NUM. This 2nd table is also indexed by SEQ_NUM field. There may be a
chance that there will be no corresponding records in the 2nd table for a
record that is in table 1. There can be more than 1 corresponding case in
record in table two, due to mulitiple drivers in the crash. I've been able
to query and utilize most data until
it gets down to the multiple 2nd database records. The results I have
places the
names of the 2nd parties involved in a print area, 1 line for each person.
I need to be able to concatenate these people in a single print area to
resemble the
cc: (carbon copy) list of names only area at the bottom of the form. The
fields I need to include in this area would be the following:
(TST_FR_CASE_RECORDS.LIC_FIRST_NME,
TST_FR_CASE_RECORDS.LIC_MIDDLE_NME,
TST_FR_CASE_RECORDS.LIC_LAST_NME,) and
TST_FR_CASE_RECORDS.DOA_NME, and
(TST_FR_ATTORNEY.FIRST_NME,
TST_FR_ATTORNEY.MIDDLE_NME,
TST_FR_ATTORNEY.LAST_NME,) and
TST_FR_ATTORNEY.FIRM_NME,
(TST_FR_CASE_OTHERS.OTHER_NME,
TST_FR_CASE_OTHERS.FIRM_NME,)
I need to create a print area as:
cc: John Doe, Jane Doe, John Williams - Williams & Williams Legal
Association,
Mary Doe, Marty Doe, Jimmy Doe, Lonnie Smith - State Farm Insurance,
Mike Doe
etc. Where John Doe name comes from LIC_FIRST_NME, LIC_MIDDLE_NME and
LIC_LAST_NME, Jane Doe comes from DOA_NME, John Williams comes from Attorney
First,Middle, & Last_NME, Williams & Williams Assoc, comes from Attorney
Firm_NME, and Mary, Marty, Jimmy, Doe, and Lonnie Smith are all 2nd table
records associated with the case coming from field
TST_FR_CASE_OTHERS.OTHER_NME, and State Farm comes from
TST_FR_CASE_OTHERS.FIRM_NME.
This is the select statement that correctly builds my associated records, I
just need ( I Hope) assistance in grouping properly the people & company
names in the cc field.
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_YR =
TST_FR_OTHERS.CASE_NUM_YR) AND (TST_FR_CASE_RECORDS.CASE_NUM =
TST_FR_CASE_OTHERS.CASE_NUM))
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;
2nd question is do I need to nest a 2nd select statement to concatenate the
names with this first select statement, if so, would it be:
SELECT Concatenate("OTHER_NME, FIRM_NME From TST_FR_CASE_OTHERS WHERE
CASE_NUM_YR = TST_FR_CASE_RECORDS.CASE_NUM_YR AND CASE_NUM =
TST_FR_CASE_RECORDS.CASE_NUM) AS OtherNames From TST_FR_CASE_RECORDS
If so can someone assist in this concatenated,nested select statement as I'm
truly
flustered in development of this query.
meant to place it in queries.
I was hoping that someone could help me with this query and its results set.
I have a Access Application being written to replace a auto crash database.
This
application keeps track of people involved, depth of injuries or fatalities,
monitarial costs, locations, etc. This application needs to generate
multiple reports
and forms. One of which is a form letter for legal hearing on crash, so a
form letter
is sent to all people involved in crash. This application has 5 tables that
are
joined in a query to create this report. The primary table is
TST_FR_CASE_RECORDS,
with two-part key of CASE_NUM_YR & CASE_NUM. This table has a one to many
relationship with table TST_FR_CASE_OTHERS, with two-part key of CASE_NUM_YR
& CASE_NUM. This 2nd table is also indexed by SEQ_NUM field. There may be a
chance that there will be no corresponding records in the 2nd table for a
record that is in table 1. There can be more than 1 corresponding case in
record in table two, due to mulitiple drivers in the crash. I've been able
to query and utilize most data until
it gets down to the multiple 2nd database records. The results I have
places the
names of the 2nd parties involved in a print area, 1 line for each person.
I need to be able to concatenate these people in a single print area to
resemble the
cc: (carbon copy) list of names only area at the bottom of the form. The
fields I need to include in this area would be the following:
(TST_FR_CASE_RECORDS.LIC_FIRST_NME,
TST_FR_CASE_RECORDS.LIC_MIDDLE_NME,
TST_FR_CASE_RECORDS.LIC_LAST_NME,) and
TST_FR_CASE_RECORDS.DOA_NME, and
(TST_FR_ATTORNEY.FIRST_NME,
TST_FR_ATTORNEY.MIDDLE_NME,
TST_FR_ATTORNEY.LAST_NME,) and
TST_FR_ATTORNEY.FIRM_NME,
(TST_FR_CASE_OTHERS.OTHER_NME,
TST_FR_CASE_OTHERS.FIRM_NME,)
I need to create a print area as:
cc: John Doe, Jane Doe, John Williams - Williams & Williams Legal
Association,
Mary Doe, Marty Doe, Jimmy Doe, Lonnie Smith - State Farm Insurance,
Mike Doe
etc. Where John Doe name comes from LIC_FIRST_NME, LIC_MIDDLE_NME and
LIC_LAST_NME, Jane Doe comes from DOA_NME, John Williams comes from Attorney
First,Middle, & Last_NME, Williams & Williams Assoc, comes from Attorney
Firm_NME, and Mary, Marty, Jimmy, Doe, and Lonnie Smith are all 2nd table
records associated with the case coming from field
TST_FR_CASE_OTHERS.OTHER_NME, and State Farm comes from
TST_FR_CASE_OTHERS.FIRM_NME.
This is the select statement that correctly builds my associated records, I
just need ( I Hope) assistance in grouping properly the people & company
names in the cc field.
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_YR =
TST_FR_OTHERS.CASE_NUM_YR) AND (TST_FR_CASE_RECORDS.CASE_NUM =
TST_FR_CASE_OTHERS.CASE_NUM))
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;
2nd question is do I need to nest a 2nd select statement to concatenate the
names with this first select statement, if so, would it be:
SELECT Concatenate("OTHER_NME, FIRM_NME From TST_FR_CASE_OTHERS WHERE
CASE_NUM_YR = TST_FR_CASE_RECORDS.CASE_NUM_YR AND CASE_NUM =
TST_FR_CASE_RECORDS.CASE_NUM) AS OtherNames From TST_FR_CASE_RECORDS
If so can someone assist in this concatenated,nested select statement as I'm
truly
flustered in development of this query.