MS Access Query Issue

I have the following query;
SELECT dbo_SYS_INFO.SYS_NME, dbo_SYS_INFO.SYS_URL,
dbo_SYS_INFO.TEST_BEGIN_DATE, dbo_SYS_INFO.TEST_END_DATE,
dbo_FINDG.FINDG_STAT_DATE, dbo_FINDG_STAT.FINDG_STAT, dbo_FINDG.SMRY AS
SMRY, dbo_FINDG.RSK_DESC AS RSK_DESC, dbo_FINDG.RCMN AS RCMN,
dbo_FINDG.FINDG_NO, dbo_FINDG_RSK_LVL.FINDG_RSK_LVL, dbo_FINDG.CMNT AS
CMNT, dbo_FINDG.FINDG_RSK_LVL_ID, dbo_FINDG.FINDG_NME, dbo_FINDG.PLCY1,
dbo_FINDG.PLCY2, dbo_FINDG.PLCY3, dbo_FINDG.PLCY4, dbo_FINDG.PLCY5,
dbo_FINDG.PLCY6, dbo_FINDG.PLCY7, dbo_FINDG.PLCY8, dbo_FINDG.PLCY9,
dbo_FINDG.NEW_CMNT AS NEW_CMNT, dbo_FINDG.URL1, dbo_FINDG.URL2,
dbo_FINDG.URL3, dbo_FINDG.URL4, dbo_FINDG.URL5, dbo_FINDG.URL6,
dbo_FINDG.URL7, dbo_FINDG.URL8, dbo_FINDG.URL9,
dbo_SYS_INFO.SYS_ID_CODE
FROM ((dbo_SYS_INFO INNER JOIN dbo_FINDG ON
dbo_SYS_INFO.SYS_ID_CODE=dbo_FINDG.SYS_ID_CODE) LEFT JOIN
dbo_FINDG_STAT ON dbo_FINDG.FINDG_STAT_ID=dbo_FINDG_STAT.FINDG_STAT_ ID)
LEFT JOIN dbo_FINDG_RSK_LVL ON
dbo_FINDG.FINDG_RSK_LVL_ID=dbo_FINDG_RSK_LVL.FINDG _RSK_LVL_ID
WHERE (((dbo_SYS_INFO.SYS_ID_CODE)=Forms!frmSystem!SYS_I D_CODE))
ORDER BY dbo_FINDG.FINDG_RSK_LVL_ID DESC , dbo_FINDG.FINDG_NO;

// I am having a problem with the SYS_NME and SYS_URL,TEST_BEGIN_DATE,
TEST_END_DATE i want these field to apear once in the query. The above
query gives me each record and in each record i see the repeated
SYS_NME and SYS_URL,TEST_BEGIN_DATE, TEST_END_DATE. I do not want to
see these field with each record, instead i want to see it with only
first record. The reason why i want it this way because i am mail
merging this query's results into MS word and when i mail merge it,
these fields apear with every record but in the word document i only
want to see it once.
Is there any function in ms access query that can do such thing?

your help would be greatly appreciated
thanks
Moe

Dear Moe:

I have edited your query for my personal reading preferences to help me
study it:

SELECT S.SYS_NME, S.SYS_URL, S.TEST_BEGIN_DATE,
S.TEST_END_DATE, F.FINDG_STAT_DATE,
FS.FINDG_STAT, F.SMRY AS SMRY,
F.RSK_DESC AS RSK_DESC, F.RCMN AS RCMN,
F.FINDG_NO, FR.FINDG_RSK_LVL, F.CMNT,
F.FINDG_RSK_LVL_ID, F.FINDG_NME, F.PLCY1,
F.PLCY2, F.PLCY3, F.PLCY4, F.PLCY5, F.PLCY6,
F.PLCY7, F.PLCY8, F.PLCY9, F.NEW_CMNT,
F.URL1, F.URL2, F.URL3, F.URL4, F.URL5, F.URL6,
F.URL7, F.URL8, F.URL9, S.SYS_ID_CODE
FROM ((dbo_SYS_INFO S
INNER JOIN dbo_FINDG F
ON S.SYS_ID_CODE = F.SYS_ID_CODE)
LEFT JOIN dbo_FINDG_STAT FS
ON F.FINDG_STAT_ID = FS.FINDG_STAT_ID)
LEFT JOIN dbo_FINDG_RSK_LVL FR
ON F.FINDG_RSK_LVL_ID = FR.FINDG_RSK_LVL_ID
WHERE S.SYS_ID_CODE = Forms!frmSystem!SYS_ID_CODE
ORDER BY F.FINDG_RSK_LVL_ID DESC, F.FINDG_NO;

This query returns 33 columns.

Now, given the other columns in this query, you must defile two additional
sets of columns.

1. What column(s) define the "group" of rows over which you want to see
only the "first row"

2. What column(s) define the ordering of rows on the basis of which you
define the order which then defines which is "first"

I believe these requirements for the problem are natural and necessary.

The sets of columns for the group and for the ordering are mutually
exclusive. No column could appear in both sets.

If you define this, I may be able to suggest how to achieve what you want.

Tom Ellison


"FA" wrote in message
oups.com...
I have the following query;
SELECT dbo_SYS_INFO.SYS_NME, dbo_SYS_INFO.SYS_URL,
dbo_SYS_INFO.TEST_BEGIN_DATE, dbo_SYS_INFO.TEST_END_DATE,
dbo_FINDG.FINDG_STAT_DATE, dbo_FINDG_STAT.FINDG_STAT, dbo_FINDG.SMRY AS
SMRY, dbo_FINDG.RSK_DESC AS RSK_DESC, dbo_FINDG.RCMN AS RCMN,
dbo_FINDG.FINDG_NO, dbo_FINDG_RSK_LVL.FINDG_RSK_LVL, dbo_FINDG.CMNT AS
CMNT, dbo_FINDG.FINDG_RSK_LVL_ID, dbo_FINDG.FINDG_NME, dbo_FINDG.PLCY1,
dbo_FINDG.PLCY2, dbo_FINDG.PLCY3, dbo_FINDG.PLCY4, dbo_FINDG.PLCY5,
dbo_FINDG.PLCY6, dbo_FINDG.PLCY7, dbo_FINDG.PLCY8, dbo_FINDG.PLCY9,
dbo_FINDG.NEW_CMNT AS NEW_CMNT, dbo_FINDG.URL1, dbo_FINDG.URL2,
dbo_FINDG.URL3, dbo_FINDG.URL4, dbo_FINDG.URL5, dbo_FINDG.URL6,
dbo_FINDG.URL7, dbo_FINDG.URL8, dbo_FINDG.URL9,
dbo_SYS_INFO.SYS_ID_CODE
FROM ((dbo_SYS_INFO INNER JOIN dbo_FINDG ON
dbo_SYS_INFO.SYS_ID_CODE=dbo_FINDG.SYS_ID_CODE) LEFT JOIN
dbo_FINDG_STAT ON dbo_FINDG.FINDG_STAT_ID=dbo_FINDG_STAT.FINDG_STAT_ ID)
LEFT JOIN dbo_FINDG_RSK_LVL ON
dbo_FINDG.FINDG_RSK_LVL_ID=dbo_FINDG_RSK_LVL.FINDG _RSK_LVL_ID
WHERE (((dbo_SYS_INFO.SYS_ID_CODE)=Forms!frmSystem!SYS_I D_CODE))
ORDER BY dbo_FINDG.FINDG_RSK_LVL_ID DESC , dbo_FINDG.FINDG_NO;

// I am having a problem with the SYS_NME and SYS_URL,TEST_BEGIN_DATE,
TEST_END_DATE i want these field to apear once in the query. The above
query gives me each record and in each record i see the repeated
SYS_NME and SYS_URL,TEST_BEGIN_DATE, TEST_END_DATE. I do not want to
see these field with each record, instead i want to see it with only
first record. The reason why i want it this way because i am mail
merging this query's results into MS word and when i mail merge it,
these fields apear with every record but in the word document i only
want to see it once.
Is there any function in ms access query that can do such thing?

your help would be greatly appreciated
thanks
Moe

Correction: not defile but define. If you wish to defile them as well, go
ahead, but don't ask, don't tell.

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Moe:

I have edited your query for my personal reading preferences to help me
study it:

SELECT S.SYS_NME, S.SYS_URL, S.TEST_BEGIN_DATE,
S.TEST_END_DATE, F.FINDG_STAT_DATE,
FS.FINDG_STAT, F.SMRY AS SMRY,
F.RSK_DESC AS RSK_DESC, F.RCMN AS RCMN,
F.FINDG_NO, FR.FINDG_RSK_LVL, F.CMNT,
F.FINDG_RSK_LVL_ID, F.FINDG_NME, F.PLCY1,
F.PLCY2, F.PLCY3, F.PLCY4, F.PLCY5, F.PLCY6,
F.PLCY7, F.PLCY8, F.PLCY9, F.NEW_CMNT,
F.URL1, F.URL2, F.URL3, F.URL4, F.URL5, F.URL6,
F.URL7, F.URL8, F.URL9, S.SYS_ID_CODE
FROM ((dbo_SYS_INFO S
INNER JOIN dbo_FINDG F
ON S.SYS_ID_CODE = F.SYS_ID_CODE)
LEFT JOIN dbo_FINDG_STAT FS
ON F.FINDG_STAT_ID = FS.FINDG_STAT_ID)
LEFT JOIN dbo_FINDG_RSK_LVL FR
ON F.FINDG_RSK_LVL_ID = FR.FINDG_RSK_LVL_ID
WHERE S.SYS_ID_CODE = Forms!frmSystem!SYS_ID_CODE
ORDER BY F.FINDG_RSK_LVL_ID DESC, F.FINDG_NO;

This query returns 33 columns.

Now, given the other columns in this query, you must defile two additional
sets of columns.

1. What column(s) define the "group" of rows over which you want to see
only the "first row"

2. What column(s) define the ordering of rows on the basis of which you
define the order which then defines which is "first"

I believe these requirements for the problem are natural and necessary.

The sets of columns for the group and for the ordering are mutually
exclusive. No column could appear in both sets.

If you define this, I may be able to suggest how to achieve what you want.

Tom Ellison


"FA" wrote in message
oups.com...
I have the following query;
SELECT dbo_SYS_INFO.SYS_NME, dbo_SYS_INFO.SYS_URL,
dbo_SYS_INFO.TEST_BEGIN_DATE, dbo_SYS_INFO.TEST_END_DATE,
dbo_FINDG.FINDG_STAT_DATE, dbo_FINDG_STAT.FINDG_STAT, dbo_FINDG.SMRY AS
SMRY, dbo_FINDG.RSK_DESC AS RSK_DESC, dbo_FINDG.RCMN AS RCMN,
dbo_FINDG.FINDG_NO, dbo_FINDG_RSK_LVL.FINDG_RSK_LVL, dbo_FINDG.CMNT AS
CMNT, dbo_FINDG.FINDG_RSK_LVL_ID, dbo_FINDG.FINDG_NME, dbo_FINDG.PLCY1,
dbo_FINDG.PLCY2, dbo_FINDG.PLCY3, dbo_FINDG.PLCY4, dbo_FINDG.PLCY5,
dbo_FINDG.PLCY6, dbo_FINDG.PLCY7, dbo_FINDG.PLCY8, dbo_FINDG.PLCY9,
dbo_FINDG.NEW_CMNT AS NEW_CMNT, dbo_FINDG.URL1, dbo_FINDG.URL2,
dbo_FINDG.URL3, dbo_FINDG.URL4, dbo_FINDG.URL5, dbo_FINDG.URL6,
dbo_FINDG.URL7, dbo_FINDG.URL8, dbo_FINDG.URL9,
dbo_SYS_INFO.SYS_ID_CODE
FROM ((dbo_SYS_INFO INNER JOIN dbo_FINDG ON
dbo_SYS_INFO.SYS_ID_CODE=dbo_FINDG.SYS_ID_CODE) LEFT JOIN
dbo_FINDG_STAT ON dbo_FINDG.FINDG_STAT_ID=dbo_FINDG_STAT.FINDG_STAT_ ID)
LEFT JOIN dbo_FINDG_RSK_LVL ON
dbo_FINDG.FINDG_RSK_LVL_ID=dbo_FINDG_RSK_LVL.FINDG _RSK_LVL_ID
WHERE (((dbo_SYS_INFO.SYS_ID_CODE)=Forms!frmSystem!SYS_I D_CODE))
ORDER BY dbo_FINDG.FINDG_RSK_LVL_ID DESC , dbo_FINDG.FINDG_NO;

// I am having a problem with the SYS_NME and SYS_URL,TEST_BEGIN_DATE,
TEST_END_DATE i want these field to apear once in the query. The above
query gives me each record and in each record i see the repeated
SYS_NME and SYS_URL,TEST_BEGIN_DATE, TEST_END_DATE. I do not want to
see these field with each record, instead i want to see it with only
first record. The reason why i want it this way because i am mail
merging this query's results into MS word and when i mail merge it,
these fields apear with every record but in the word document i only
want to see it once.
Is there any function in ms access query that can do such thing?

your help would be greatly appreciated
thanks
Moe

Column which is based on S.SYS_NME, S.TEST_BEGIN_DATE, S.TEST_END_DATE
are those in which i want to the data in rows to be apear once only.
All these columns are based on single table SYS_INFO. The rest of the
colums should display all the data.
Actually the logic is
One SYS_NME,TEST_BEGIN_DATE,TEST_END_DATE has many FINDG_NO, FINDG_NME
and so on ,,,,.
So these field data should apear once only and the rest should apear
repeatedly. I know i can handle the groupby in MS access reports but in
MS Word mail merge i dont know how let these fields from SYS_INFO to
apear only once in the doucment.

Thanks Millions for your help Tom
Moe

Dear FA:

Every row in the results of a query must contain the same set of columns.
How can you have what you describe? How can certain columns appear only
once (in one row) while others appear repeatedly?

Perhaps you can give an example.

Tom Ellison


"FA" wrote in message
oups.com...
Column which is based on S.SYS_NME, S.TEST_BEGIN_DATE, S.TEST_END_DATE
are those in which i want to the data in rows to be apear once only.
All these columns are based on single table SYS_INFO. The rest of the
colums should display all the data.
Actually the logic is
One SYS_NME,TEST_BEGIN_DATE,TEST_END_DATE has many FINDG_NO, FINDG_NME
and so on ,,,,.
So these field data should apear once only and the rest should apear
repeatedly. I know i can handle the groupby in MS access reports but in
MS Word mail merge i dont know how let these fields from SYS_INFO to
apear only once in the doucment.

Thanks Millions for your help Tom
Moe

So that means i have to find something in MS Word so that those field
do not repeat with every record. Thanks so m uch Tom i really
appreciate your help.

have a great weekend.

Moe

Dear Moe:

Not necessarily. You can suppress repeating values in a column in a Report.
It could be done in a query (if this is indeed what you want) but that's a
lot of work and is probably not the solution you seek. Look at reports to
see if they do what you want.

Tom Ellison


"FA" wrote in message
ups.com...
So that means i have to find something in MS Word so that those field
do not repeat with every record. Thanks so m uch Tom i really
appreciate your help.

have a great weekend.

Moe

Thats exactly what they want me to do. Create report via mail merge in
MS Word and they want the SYS_NME, SYS_URL, TEST_BEGIN_DATE,
TEST_END_DATE to be apeared once only on the top of the report. In Ms
access creating this type of the report is easy since MS access report
has group options. I do not find a way to do this kind of work in MS
word the only way i can do such thing is if i can do somthing like this
in query and then dump that results into Word.

If you please help me, i would really really appreciate it. I have been
trying hard to achieve this and its just i havent been able to achieve
it ;(

THanks
Moe

Dear Moe:

OK, let's go. Now, the first step will be feasability.

Now, as I understand you, there are some fields in the merge document that
appear only once in the document. However, the document must be based on a
variable number of related rows because you have some columns that do vary.

Somewhere inside the merged document you will be showing:

FINDG_NO, FINDG_NME and so on ,,,,.

as a table of values with an indefinite number of rows.

Can you get Word to do that? Will word accept two separate but related
query sources for the merge?

I can get the query to provide the unvarying columns so they repeat in each
of the rows of the query. I can get them to show up in only the first row
of the query (whatever that means!) and be blank or NULL in all the other
rows. What I do not know is how Word will be able to use all this. You may
need some expertise in Word to do this. If you get a Word MVP involved in
one of their NGs, let me know, and I can join the discussion there to iron
out the details. If you already know what the query must look like, you can
specify that now.

Tom Ellison


"FA" wrote in message
ups.com...
Thats exactly what they want me to do. Create report via mail merge in
MS Word and they want the SYS_NME, SYS_URL, TEST_BEGIN_DATE,
TEST_END_DATE to be apeared once only on the top of the report. In Ms
access creating this type of the report is easy since MS access report
has group options. I do not find a way to do this kind of work in MS
word the only way i can do such thing is if i can do somthing like this
in query and then dump that results into Word.

If you please help me, i would really really appreciate it. I have been
trying hard to achieve this and its just i havent been able to achieve
it ;(

THanks
Moe

Tom have got my problem absulotely right and i also do understand what
you are trying to say.What if i dump the queries into a temp table and
then do the word merge with that temp table?? what do you think about
that ???