grouping

  • Thread starter Thread starter pammy
  • Start date Start date
P

pammy

I have a query on a database I imported from Excel, the datatbase shows all
the users we have and what access they have on a particular module. In this
query I only care about the users with the same Y or N's.
There are 25 columns (named: add, modify, delete,etc) and 1900 rows with
user names. In the the columns there is either a Y or N. I ran a query in
ascending order on the columns so I would have all the information that is
the same put together. .
Sample:
N, N, Y, N, Y, Y, Y
N, N, Y, N, Y, Y, Y
N, N, Y, Y, N, N, Y
Y, N, Y, Y, N, N, Y
Y, Y, Y, N, N, Y, Y
Y, Y, Y, N, N, Y, Y
Question: Is there a way I could group the ones together that are the same
so I can easily see the difference, may a line between the groups? as shown
in the sample below? Thanks,
N, N, Y, N, Y, Y, Y
N, N, Y, N, Y, Y, Y

N, N, Y, N, Y, Y, Y
 
pammy said:
I have a query on a database I imported from Excel, the datatbase shows all
the users we have and what access they have on a particular module. In this
query I only care about the users with the same Y or N's.
There are 25 columns (named: add, modify, delete,etc) and 1900 rows with
user names. In the the columns there is either a Y or N. I ran a query in
ascending order on the columns so I would have all the information that is
the same put together. .
Sample:
N, N, Y, N, Y, Y, Y
N, N, Y, N, Y, Y, Y
N, N, Y, Y, N, N, Y
Y, N, Y, Y, N, N, Y
Y, Y, Y, N, N, Y, Y
Y, Y, Y, N, N, Y, Y
Question: Is there a way I could group the ones together that are the same
so I can easily see the difference, may a line between the groups? as shown
in the sample below? Thanks,
N, N, Y, N, Y, Y, Y
N, N, Y, N, Y, Y, Y

N, N, Y, Y, N, N, Y
Click to expand...
 
I have a query on a database I imported from Excel, the datatbase shows all
the users we have and what access they have on a particular module. In this
query I only care about the users with the same Y or N's.
There are 25 columns (named: add, modify, delete,etc) and 1900 rows with
user names. In the the columns there is either a Y or N.
Click to expand...

As I've said before, this is a decent design for a Spreadsheet but a VERY BAD
design for a relational database. Normalize!!!
I ran a query in
ascending order on the columns so I would have all the information that is
the same put together. .
Sample:
N, N, Y, N, Y, Y, Y
N, N, Y, N, Y, Y, Y
N, N, Y, Y, N, N, Y
Y, N, Y, Y, N, N, Y
Y, Y, Y, N, N, Y, Y
Y, Y, Y, N, N, Y, Y
Question: Is there a way I could group the ones together that are the same
so I can easily see the difference, may a line between the groups? as shown
in the sample below? Thanks,
N, N, Y, N, Y, Y, Y
N, N, Y, N, Y, Y, Y

N, N, Y, N, Y, Y, Y
Click to expand...

Try adding a field to the query by typing

AllYN: [Add] & [Modify] & [Delete] & <etc through all 25 fields>

This will be a text string like

NNYNYYY...

Create a Report based on your query; use the Sorting and Grouping dialog to
group by ALLYN, and just put a single blank line in that grouping's Footer.

John W. Vinson [MVP]
 
pammy said:
I have a query on a database I imported from Excel, the datatbase shows all
the users we have and what access they have on a particular module. In this
query I only care about the users with the same Y or N's.
There are 25 columns (named: add, modify, delete,etc) and 1900 rows with
user names. In the the columns there is either a Y or N. I ran a query in
ascending order on the columns so I would have all the information that is
the same put together. .
Sample:
N, N, Y, N, Y, Y, Y
N, N, Y, N, Y, Y, Y
N, N, Y, Y, N, N, Y
Y, N, Y, Y, N, N, Y
Y, Y, Y, N, N, Y, Y
Y, Y, Y, N, N, Y, Y
Question: Is there a way I could group the ones together that are the same
so I can easily see the difference, may a line between the groups? as shown
in the sample below? Thanks,
N, N, Y, N, Y, Y, Y
N, N, Y, N, Y, Y, Y

N, N, Y, N, Y, Y, Y
Click to expand...


You can use a calculated field like:
[add] & [modify] & [delete] & . . .
and the use that to group by.

The idea of leaving a blank line is a presentation issue and
is not appropriate in a query. Instead you should use the
query as the record source for a report where you can use
Sorting and Grouping to add header/footer sections with
lines, counts, etc.
 
Back
Top