Adding ALL to drp down list

[Chris Freeman]

to all,
I have a drop down and I want to add "ALL" to the head of the list. I found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

But in doing so, I show only the 'ALL' and the rest of the spaces are blank.
There are 10 records to be returned, and the drop down has one ALL and 10
blank rows. This is the code I'm using currently, but it references a static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by [UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return with
ALL at the top.

 

[Douglas J. Steele]

What happens if you just use SELECT DISTINCT tbl_User_Login.UserName FROM
tbl_User_Login order by [UserName] asc;? Do you get the correct data, or
just the 10 blank rows? If the latter, are you certain that there's data in
the UserName field?

 

[Mr. B]

Chirs,

There is quite a difference between the two sql statements that you refer
to. In the first one:
SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

You are attempting to only return a unique list of the values from the
UserName field and have the "All" option also available but you have started
with the "All" part. If this is really what you are wanting try this:
SELECT DISTINCT tbl_User_Login.UserName
FROM tbl_User_Login UNION select "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

In the last sql statement you are asking for unique records and including
what I assume is a unique value field (USER_ID). You did not indicate what
type of field that the USER_ID field is. If this is an autonumber field the
you will get every record because that field would make each recrod unique.
If this field is an auto number type field try this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login UNION select 0 as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

If the USER_ID field is a text type field you will need to enclose the zero
in quotes, like this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login UNION select ""0"" as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Chris Freeman]

Doug,
When I use this syntax, I have the same problem where the drop down is
populated with an 'ALL' at the top, and blank rows below, corresponding to
the number of records in the table. I confirmed the field names and values,
but for some reason, it seems to be reading a field that isn't there.

Mr B's response resulting in the same error in layout as well. I have done
this "ALL" function before, but never realized the difference between using
it on a field that has duplicate values.
--
Chris Freeman
IT Project Coordinator

What happens if you just use SELECT DISTINCT tbl_User_Login.UserName FROM
tbl_User_Login order by [UserName] asc;? Do you get the correct data, or
just the 10 blank rows? If the latter, are you certain that there's data in
the UserName field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

to all,
I have a drop down and I want to add "ALL" to the head of the list. I
found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

But in doing so, I show only the 'ALL' and the rest of the spaces are
blank.
There are 10 records to be returned, and the drop down has one ALL and 10
blank rows. This is the code I'm using currently, but it references a
static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by
[UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return with
ALL at the top.

 

[Chris Freeman]

Mr. B,
I still can't get this working correctly. the table has 10 records, all text:
freemach
freemach
mannalie
mannalie
mannalie
mannalie
freemach
freemach
tragarzb
tragarzb

What I'm looking for is a drop down that looks like this:
________
ALL
freemach
mannalie
tragarzb
________

Your first statement resulted in the drop down being completely blank, not
even the "ALL" displayed.

Your second statement resulted in error: Syntax error (missing operator) in
query expression ""0"". So I removed one set of qoutes, around the 0, like
this "0", and this results in returning a drop down with ALL and all of the
records.
Then I thought that because of the Used_ID field is unique, an autonumber,
this brings back unique records, and thereby duplicate UserName values. So
first I removed User_ID, but this errored out. then I switched User_ID and
UserName places in the statement, but this resulted in all the User_IDs
displaying in the drop down.

So next removed the User_ID tag, and this resulted in an error: "The number
of columns in the two selected tables or queries of a union query do not
match."

So from here I thought I would remove the "0" as bogus and that left me
with: "...UNION select "All" from tbl_User_Login...". unfortunately, this
left me with no error, but an empty drop down menu, no ALL, no values.

I have used this Union for adding "ALL" before, but now realize that those
were always on non-duplicated records. this is tunring into a real hair
puller.

--
Chris Freeman
IT Project Coordinator

Chirs,

There is quite a difference between the two sql statements that you refer
to. In the first one:
SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

You are attempting to only return a unique list of the values from the
UserName field and have the "All" option also available but you have started
with the "All" part. If this is really what you are wanting try this:
SELECT DISTINCT tbl_User_Login.UserName
FROM tbl_User_Login UNION select "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

In the last sql statement you are asking for unique records and including
what I assume is a unique value field (USER_ID). You did not indicate what
type of field that the USER_ID field is. If this is an autonumber field the
you will get every record because that field would make each recrod unique.
If this field is an auto number type field try this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login UNION select 0 as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

If the USER_ID field is a text type field you will need to enclose the zero
in quotes, like this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login UNION select ""0"" as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

to all,
I have a drop down and I want to add "ALL" to the head of the list. I found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

But in doing so, I show only the 'ALL' and the rest of the spaces are blank.
There are 10 records to be returned, and the drop down has one ALL and 10
blank rows. This is the code I'm using currently, but it references a static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by [UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return with
ALL at the top.

 

[Douglas J. Steele]

Are you certain that what you posted is the SQL you're actually using? You
didn't accidentally put UNION ALL instead of just UNION, did you?

UNION eliminates duplicates: UNION ALL doesn't.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Mr. B,
I still can't get this working correctly. the table has 10 records, all
text:
freemach
freemach
mannalie
mannalie
mannalie
mannalie
freemach
freemach
tragarzb
tragarzb

What I'm looking for is a drop down that looks like this:
________
ALL
freemach
mannalie
tragarzb
________

Your first statement resulted in the drop down being completely blank, not
even the "ALL" displayed.

Your second statement resulted in error: Syntax error (missing operator)
in
query expression ""0"". So I removed one set of qoutes, around the 0, like
this "0", and this results in returning a drop down with ALL and all of
the
records.
Then I thought that because of the Used_ID field is unique, an autonumber,
this brings back unique records, and thereby duplicate UserName values. So
first I removed User_ID, but this errored out. then I switched User_ID and
UserName places in the statement, but this resulted in all the User_IDs
displaying in the drop down.

So next removed the User_ID tag, and this resulted in an error: "The
number
of columns in the two selected tables or queries of a union query do not
match."

So from here I thought I would remove the "0" as bogus and that left me
with: "...UNION select "All" from tbl_User_Login...". unfortunately, this
left me with no error, but an empty drop down menu, no ALL, no values.

I have used this Union for adding "ALL" before, but now realize that those
were always on non-duplicated records. this is tunring into a real hair
puller.

--
Chris Freeman
IT Project Coordinator

Chirs,

There is quite a difference between the two sql statements that you refer
to. In the first one:
SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

You are attempting to only return a unique list of the values from the
UserName field and have the "All" option also available but you have
started
with the "All" part. If this is really what you are wanting try this:
SELECT DISTINCT tbl_User_Login.UserName
FROM tbl_User_Login UNION select "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

In the last sql statement you are asking for unique records and including
what I assume is a unique value field (USER_ID). You did not indicate
what
type of field that the USER_ID field is. If this is an autonumber field
the
you will get every record because that field would make each recrod
unique.
If this field is an auto number type field try this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login UNION select 0 as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

If the USER_ID field is a text type field you will need to enclose the
zero
in quotes, like this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login UNION select ""0"" as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

to all,
I have a drop down and I want to add "ALL" to the head of the list. I
found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

But in doing so, I show only the 'ALL' and the rest of the spaces are
blank.
There are 10 records to be returned, and the drop down has one ALL and
10
blank rows. This is the code I'm using currently, but it references a
static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by
[UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return
with
ALL at the top.

 

[Douglas J. Steele]

How are you populating the table in the first place? Is there a chance that
extraneous unprintable characters are getting into the field, so that while
they look like they're the same value repeated, they're really different?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
When I use this syntax, I have the same problem where the drop down is
populated with an 'ALL' at the top, and blank rows below, corresponding to
the number of records in the table. I confirmed the field names and
values,
but for some reason, it seems to be reading a field that isn't there.

Mr B's response resulting in the same error in layout as well. I have done
this "ALL" function before, but never realized the difference between
using
it on a field that has duplicate values.
--
Chris Freeman
IT Project Coordinator

What happens if you just use SELECT DISTINCT tbl_User_Login.UserName
FROM
tbl_User_Login order by [UserName] asc;? Do you get the correct data, or
just the 10 blank rows? If the latter, are you certain that there's data
in
the UserName field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

to all,
I have a drop down and I want to add "ALL" to the head of the list. I
found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

But in doing so, I show only the 'ALL' and the rest of the spaces are
blank.
There are 10 records to be returned, and the drop down has one ALL and
10
blank rows. This is the code I'm using currently, but it references a
static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by
[UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return
with
ALL at the top.

 

[Chris Freeman]

Doug,
Thanks for jumping in here. OK, so here's what I have so far:
SELECT tbl_User_Login.USER_ID,tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCTROW 'ALL','ALL' FROM tbl_User_Login order by
[UserName] ASC;

The code above returns all of the values in the table, but displays
duplicates as well. So from there I removed one 'ALL' as you suggest:
SELECT tbl_User_Login.USER_ID,tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCTROW 'ALL' FROM tbl_User_Login order by [UserName] ASC;

This code now generates an error: "The number of columns in the two
selected tables or queries of a union query do not match."

I removed the Used_ID field, which I believe is causing the duplication
problem anyway. So now the code is:
SELECT tbl_User_Login.UserName FROM tbl_User_Login UNION SELECT DISTINCTROW
'ALL' FROM tbl_User_Login order by [UserName] ASC;

But now, the drop down opens, and there are rows but no data. No 'ALL', no
employee user names. But the size of the drop down matches the numbers of
records, or to say it another way, its the same size as drop down list when
the records do display.

So now I start changing the settings via the properties dialog box; Bound
Column to 0, number of columns to 1, and nothing changes.

Oh my God .... in writing this, I was looking at the properties to confirm
the settings, and realized the Column Widths were set to 0",1". So everything
was working, but removing the ID field shifted the Name field to Column 0,
which had 0 width, and was invisible.

Thanks for pushing me to review the details a little more. It's working as
intended now.

Thanks
--
Chris Freeman
IT Project Coordinator

Are you certain that what you posted is the SQL you're actually using? You
didn't accidentally put UNION ALL instead of just UNION, did you?

UNION eliminates duplicates: UNION ALL doesn't.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Mr. B,
I still can't get this working correctly. the table has 10 records, all
text:
freemach
freemach
mannalie
mannalie
mannalie
mannalie
freemach
freemach
tragarzb
tragarzb

What I'm looking for is a drop down that looks like this:
________
ALL
freemach
mannalie
tragarzb
________

Your first statement resulted in the drop down being completely blank, not
even the "ALL" displayed.

Your second statement resulted in error: Syntax error (missing operator)
in
query expression ""0"". So I removed one set of qoutes, around the 0, like
this "0", and this results in returning a drop down with ALL and all of
the
records.
Then I thought that because of the Used_ID field is unique, an autonumber,
this brings back unique records, and thereby duplicate UserName values. So
first I removed User_ID, but this errored out. then I switched User_ID and
UserName places in the statement, but this resulted in all the User_IDs
displaying in the drop down.

So next removed the User_ID tag, and this resulted in an error: "The
number
of columns in the two selected tables or queries of a union query do not
match."

So from here I thought I would remove the "0" as bogus and that left me
with: "...UNION select "All" from tbl_User_Login...". unfortunately, this
left me with no error, but an empty drop down menu, no ALL, no values.

I have used this Union for adding "ALL" before, but now realize that those
were always on non-duplicated records. this is tunring into a real hair
puller.

--
Chris Freeman
IT Project Coordinator

Chirs,

There is quite a difference between the two sql statements that you refer
to. In the first one:
SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

You are attempting to only return a unique list of the values from the
UserName field and have the "All" option also available but you have
started
with the "All" part. If this is really what you are wanting try this:
SELECT DISTINCT tbl_User_Login.UserName
FROM tbl_User_Login UNION select "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

In the last sql statement you are asking for unique records and including
what I assume is a unique value field (USER_ID). You did not indicate
what
type of field that the USER_ID field is. If this is an autonumber field
the
you will get every record because that field would make each recrod
unique.
If this field is an auto number type field try this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login UNION select 0 as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

If the USER_ID field is a text type field you will need to enclose the
zero
in quotes, like this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login UNION select ""0"" as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm


:

to all,
I have a drop down and I want to add "ALL" to the head of the list. I
found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

But in doing so, I show only the 'ALL' and the rest of the spaces are
blank.
There are 10 records to be returned, and the drop down has one ALL and
10
blank rows. This is the code I'm using currently, but it references a
static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by
[UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return
with
ALL at the top.

 

[Douglas J. Steele]

You misinterpretted my comment.

I was specifically talking about joining the subqueries using UNION ALL, as
in

SELECT Field1, Field2 FROM Table1
UNION ALL
SELECT "All", "All" FROM Table1

as opposed to

SELECT Field1, Field2 FROM Table1
UNION
SELECT "All", "All" FROM Table1

However, I finally took a closer look at your queries. So you're seeing
UserName duplicated. Does each duplicated row has a different value for
USER_ID?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,
Thanks for jumping in here. OK, so here's what I have so far:
SELECT tbl_User_Login.USER_ID,tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCTROW 'ALL','ALL' FROM tbl_User_Login order by
[UserName] ASC;

The code above returns all of the values in the table, but displays
duplicates as well. So from there I removed one 'ALL' as you suggest:
SELECT tbl_User_Login.USER_ID,tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCTROW 'ALL' FROM tbl_User_Login order by [UserName]
ASC;

This code now generates an error: "The number of columns in the two
selected tables or queries of a union query do not match."

I removed the Used_ID field, which I believe is causing the duplication
problem anyway. So now the code is:
SELECT tbl_User_Login.UserName FROM tbl_User_Login UNION SELECT
DISTINCTROW
'ALL' FROM tbl_User_Login order by [UserName] ASC;

But now, the drop down opens, and there are rows but no data. No 'ALL', no
employee user names. But the size of the drop down matches the numbers of
records, or to say it another way, its the same size as drop down list
when
the records do display.

So now I start changing the settings via the properties dialog box; Bound
Column to 0, number of columns to 1, and nothing changes.

Oh my God .... in writing this, I was looking at the properties to
confirm
the settings, and realized the Column Widths were set to 0",1". So
everything
was working, but removing the ID field shifted the Name field to Column 0,
which had 0 width, and was invisible.

Thanks for pushing me to review the details a little more. It's working as
intended now.

Thanks
--
Chris Freeman
IT Project Coordinator

Are you certain that what you posted is the SQL you're actually using?
You
didn't accidentally put UNION ALL instead of just UNION, did you?

UNION eliminates duplicates: UNION ALL doesn't.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Mr. B,
I still can't get this working correctly. the table has 10 records, all
text:
freemach
freemach
mannalie
mannalie
mannalie
mannalie
freemach
freemach
tragarzb
tragarzb

What I'm looking for is a drop down that looks like this:
________
ALL
freemach
mannalie
tragarzb
________

Your first statement resulted in the drop down being completely blank,
not
even the "ALL" displayed.

Your second statement resulted in error: Syntax error (missing
operator)
in
query expression ""0"". So I removed one set of qoutes, around the 0,
like
this "0", and this results in returning a drop down with ALL and all of
the
records.
Then I thought that because of the Used_ID field is unique, an
autonumber,
this brings back unique records, and thereby duplicate UserName values.
So
first I removed User_ID, but this errored out. then I switched User_ID
and
UserName places in the statement, but this resulted in all the User_IDs
displaying in the drop down.

So next removed the User_ID tag, and this resulted in an error: "The
number
of columns in the two selected tables or queries of a union query do
not
match."

So from here I thought I would remove the "0" as bogus and that left me
with: "...UNION select "All" from tbl_User_Login...". unfortunately,
this
left me with no error, but an empty drop down menu, no ALL, no values.

I have used this Union for adding "ALL" before, but now realize that
those
were always on non-duplicated records. this is tunring into a real hair
puller.

--
Chris Freeman
IT Project Coordinator


:

Chirs,

There is quite a difference between the two sql statements that you
refer
to. In the first one:
SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

You are attempting to only return a unique list of the values from the
UserName field and have the "All" option also available but you have
started
with the "All" part. If this is really what you are wanting try this:
SELECT DISTINCT tbl_User_Login.UserName
FROM tbl_User_Login UNION select "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

In the last sql statement you are asking for unique records and
including
what I assume is a unique value field (USER_ID). You did not indicate
what
type of field that the USER_ID field is. If this is an autonumber
field
the
you will get every record because that field would make each recrod
unique.
If this field is an auto number type field try this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName
FROM
tbl_User_Login UNION select 0 as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

If the USER_ID field is a text type field you will need to enclose the
zero
in quotes, like this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName
FROM
tbl_User_Login UNION select ""0"" as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm


:

to all,
I have a drop down and I want to add "ALL" to the head of the list.
I
found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName]
asc;

But in doing so, I show only the 'ALL' and the rest of the spaces
are
blank.
There are 10 records to be returned, and the drop down has one ALL
and
10
blank rows. This is the code I'm using currently, but it references
a
static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by
[UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return
with
ALL at the top.

 

[gael]

to all,
I have a drop down and I want to add "ALL" to the head of the list. I
found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

But in doing so, I show only the 'ALL' and the rest of the spaces are
blank. (e-mail address removed)...
There are 10 records to be returned, and the drop down has one ALL and 10
blank rows. This is the code I'm using currently, but it references a
static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by
[UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return with
ALL at the top.

 

[Chris Freeman]

Doug,
Each row has the same value for User ID, which is why this was so confusing
as to why it didn't work. Using the UNION ALL SELECT returned multiple
values, instead of distinct value. UNION SELECT returned the distinct value.
The final code is working properly: SELECT tbl_Check_Reissue.UserName FROM
tbl_Check_Reissue UNION ALL SELECT DISTINCT 'ALL' FROM tbl_Check_Reissue
order by [UserName] ASC;

Thanks
--
Chris Freeman
IT Project Coordinator

You misinterpretted my comment.

I was specifically talking about joining the subqueries using UNION ALL, as
in

SELECT Field1, Field2 FROM Table1
UNION ALL
SELECT "All", "All" FROM Table1

as opposed to

SELECT Field1, Field2 FROM Table1
UNION
SELECT "All", "All" FROM Table1

However, I finally took a closer look at your queries. So you're seeing
UserName duplicated. Does each duplicated row has a different value for
USER_ID?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,
Thanks for jumping in here. OK, so here's what I have so far:
SELECT tbl_User_Login.USER_ID,tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCTROW 'ALL','ALL' FROM tbl_User_Login order by
[UserName] ASC;

The code above returns all of the values in the table, but displays
duplicates as well. So from there I removed one 'ALL' as you suggest:
SELECT tbl_User_Login.USER_ID,tbl_User_Login.UserName FROM tbl_User_Login
UNION SELECT DISTINCTROW 'ALL' FROM tbl_User_Login order by [UserName]
ASC;

This code now generates an error: "The number of columns in the two
selected tables or queries of a union query do not match."

I removed the Used_ID field, which I believe is causing the duplication
problem anyway. So now the code is:
SELECT tbl_User_Login.UserName FROM tbl_User_Login UNION SELECT
DISTINCTROW
'ALL' FROM tbl_User_Login order by [UserName] ASC;

But now, the drop down opens, and there are rows but no data. No 'ALL', no
employee user names. But the size of the drop down matches the numbers of
records, or to say it another way, its the same size as drop down list
when
the records do display.

So now I start changing the settings via the properties dialog box; Bound
Column to 0, number of columns to 1, and nothing changes.

Oh my God .... in writing this, I was looking at the properties to
confirm
the settings, and realized the Column Widths were set to 0",1". So
everything
was working, but removing the ID field shifted the Name field to Column 0,
which had 0 width, and was invisible.

Thanks for pushing me to review the details a little more. It's working as
intended now.

Thanks
--
Chris Freeman
IT Project Coordinator

Are you certain that what you posted is the SQL you're actually using?
You
didn't accidentally put UNION ALL instead of just UNION, did you?

UNION eliminates duplicates: UNION ALL doesn't.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mr. B,
I still can't get this working correctly. the table has 10 records, all
text:
freemach
freemach
mannalie
mannalie
mannalie
mannalie
freemach
freemach
tragarzb
tragarzb

What I'm looking for is a drop down that looks like this:
________
ALL
freemach
mannalie
tragarzb
________

Your first statement resulted in the drop down being completely blank,
not
even the "ALL" displayed.

Your second statement resulted in error: Syntax error (missing
operator)
in
query expression ""0"". So I removed one set of qoutes, around the 0,
like
this "0", and this results in returning a drop down with ALL and all of
the
records.
Then I thought that because of the Used_ID field is unique, an
autonumber,
this brings back unique records, and thereby duplicate UserName values.
So
first I removed User_ID, but this errored out. then I switched User_ID
and
UserName places in the statement, but this resulted in all the User_IDs
displaying in the drop down.

So next removed the User_ID tag, and this resulted in an error: "The
number
of columns in the two selected tables or queries of a union query do
not
match."

So from here I thought I would remove the "0" as bogus and that left me
with: "...UNION select "All" from tbl_User_Login...". unfortunately,
this
left me with no error, but an empty drop down menu, no ALL, no values.

I have used this Union for adding "ALL" before, but now realize that
those
were always on non-duplicated records. this is tunring into a real hair
puller.

--
Chris Freeman
IT Project Coordinator


:

Chirs,

There is quite a difference between the two sql statements that you
refer
to. In the first one:
SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName] asc;

You are attempting to only return a unique list of the values from the
UserName field and have the "All" option also available but you have
started
with the "All" part. If this is really what you are wanting try this:
SELECT DISTINCT tbl_User_Login.UserName
FROM tbl_User_Login UNION select "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

In the last sql statement you are asking for unique records and
including
what I assume is a unique value field (USER_ID). You did not indicate
what
type of field that the USER_ID field is. If this is an autonumber
field
the
you will get every record because that field would make each recrod
unique.
If this field is an auto number type field try this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName
FROM
tbl_User_Login UNION select 0 as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

If the USER_ID field is a text type field you will need to enclose the
zero
in quotes, like this:
SELECT DISTINCTROW tbl_User_Login.USER_ID, tbl_User_Login.UserName
FROM
tbl_User_Login UNION select ""0"" as bogus, "All" from tbl_User_Login
ORDER BY tbl_User_Login.UserName;

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm


:

to all,
I have a drop down and I want to add "ALL" to the head of the list.
I
found
some code from a 2007 post:

SELECT "ALL" as UserName FROM tbl_User_Login UNION SELECT DISTINCT
tbl_User_Login.UserName FROM tbl_User_Login order by [UserName]
asc;

But in doing so, I show only the 'ALL' and the rest of the spaces
are
blank.
There are 10 records to be returned, and the drop down has one ALL
and
10
blank rows. This is the code I'm using currently, but it references
a
static
table and shows repeating entries:

SELECT tbl_User_Login.USER_ID, tbl_User_Login.UserName FROM
tbl_User_Login
UNION SELECT DISTINCT 'ALL','ALL' FROM tbl_User_Login order by
[UserName]
ASC;

Any ideas what I'm doing wrong? I just want an DITINCT record return
with
ALL at the top.