(Msg. 1) Posted: Tue Aug 18, 2009 12:05 pm
Post subject: SQL Union to create Header row for a combobox Archived from groups: microsoft>public>access (more info?)
Greetings,
I posted in the vb.net forums as I'm developing a vb.net[2008] app with the
backend database being Access 07 (Connection string:
Microsoft.ACE.OLEDB.12.0).
I want a header row in a combobox with a user friendly message e.g. "---
Please choose a member ----"
I suggested I had looked at both vb.net control settings for a combo box and
SQL opportunities to find a solution.
I got a reply to investigate using the UNION ALL in SQL to create a header
row, having played around in Access query design, I cannot resolve my
problem.
The SQL currently used to populate the combobox is below :-
SELECT M.strMembershipId + ' ' + M.strTitle + ' ' + M.strForeNames + ' ' +
M.strSurname as memLookUp, M.*
FROM tblMembers M ORDER BY M.strMembershipId ASC
I select everything from the tblMembers table as it is used to populate a
form based on the value of the strMembershipId primary field.
The code below ties in the selection from the combobox to the primary field
cboSearchKey.ValueMember = "strMembershipId"
I display the alias field Lookup to the user with the following code:-
cboSearchKey.DisplayMember = "memLookUp"
Now to do this with a union I would have to ensure that the number of
columns remain the same for all of select statements, which I find could be
difficult
as I'm selecting everything from the table, many columns.
Also I don't know how to tie in the valuemember of strMembershipId to the
pseudo header row as it won't have a strMembershipId value.
Hope this problem is clear and someone can provide some assistance of how I
can create a header row, which will always appears at the top.
(Msg. 2) Posted: Tue Aug 18, 2009 12:05 pm
Post subject: Re: SQL Union to create Header row for a combobox [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"Rob W" <robwha.TakeThisOut@o2.co.uk> wrote in message
news:OLUeZjBIKHA.2516@TK2MSFTNGP02.phx.gbl...
> Now to do this with a union I would have to ensure that the number of
> columns remain the same for all of select statements, which I find could
> be difficult
> as I'm selecting everything from the table, many columns.
Unfortunately, there's no way around this. You could always write code that
generates the SQL for you, based on the number of fields in the table in
case it changes.
> Also I don't know how to tie in the valuemember of strMembershipId to the
> pseudo header row as it won't have a strMembershipId value.
Since all fields must match up, you have no choiice but to assign a value
for strMembershipId for the pseudo row. Choose a value you know will never
occur (like 0 or -1)
(Msg. 3) Posted: Tue Aug 18, 2009 1:05 pm
Post subject: Re: SQL Union to create Header row for a combobox [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks for the confirmation.
I'm going to take a long break away from my pc and then start to write a
query.
"Douglas J. Steele" <NOSPAM_djsteele.TakeThisOut@NOSPAM_gmail.com> wrote in message
news:elXkyqBIKHA.1252@TK2MSFTNGP04.phx.gbl...
> "Rob W" <robwha.TakeThisOut@o2.co.uk> wrote in message
> news:OLUeZjBIKHA.2516@TK2MSFTNGP02.phx.gbl...
>
>> Now to do this with a union I would have to ensure that the number of
>> columns remain the same for all of select statements, which I find could
>> be difficult
>> as I'm selecting everything from the table, many columns.
>
> Unfortunately, there's no way around this. You could always write code
> that generates the SQL for you, based on the number of fields in the table
> in case it changes.
>
>> Also I don't know how to tie in the valuemember of strMembershipId to the
>> pseudo header row as it won't have a strMembershipId value.
>
> Since all fields must match up, you have no choiice but to assign a value
> for strMembershipId for the pseudo row. Choose a value you know will never
> occur (like 0 or -1)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele > (no e-mails, please!)
>
>
>
(Msg. 4) Posted: Tue Aug 18, 2009 1:05 pm
Post subject: Re: SQL Union to create Header row for a combobox [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Well I created a small test database and table.
The Union statement below creates a header row but for every single row in
the person table.
From testing it appeared the first select MUST always have a FROM clause
though so Im clueless how to create a single row header.
SELECT 0 as PseudoRow, "" as id, "" as firstname, "" as surname
FROM Person p
UNION
SELECT 1, P.*
FROM Person p
ORDER by PseudoRow;
How can I create a SINGLE header row?
Thanks
Rob
"Douglas J. Steele" <NOSPAM_djsteele DeleteThis @NOSPAM_gmail.com> wrote in message
news:elXkyqBIKHA.1252@TK2MSFTNGP04.phx.gbl...
> "Rob W" <robwha DeleteThis @o2.co.uk> wrote in message
> news:OLUeZjBIKHA.2516@TK2MSFTNGP02.phx.gbl...
>
>> Now to do this with a union I would have to ensure that the number of
>> columns remain the same for all of select statements, which I find could
>> be difficult
>> as I'm selecting everything from the table, many columns.
>
> Unfortunately, there's no way around this. You could always write code
> that generates the SQL for you, based on the number of fields in the table
> in case it changes.
>
>> Also I don't know how to tie in the valuemember of strMembershipId to the
>> pseudo header row as it won't have a strMembershipId value.
>
> Since all fields must match up, you have no choiice but to assign a value
> for strMembershipId for the pseudo row. Choose a value you know will never
> occur (like 0 or -1)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele > (no e-mails, please!)
>
>
>
(Msg. 5) Posted: Tue Aug 18, 2009 1:15 pm
Post subject: Re: SQL Union to create Header row for a combobox [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
You can either use a table which you know will always contain exactly one
row, or add a criteria which limits output to a single row. I'm not sure
which criteria Access allows but something like this might work:
Select Top 1 0 as PseudoRow, ...
The Top 1 sql clause limits output to a single row.
Or From Person p Where p.personID = (Select Max(personID) From Person).
Access might not accept a scalar subquery for the where clause, in which
case you could change that to:
From Person p
Where Not Exists (
Select * From Person as P2 Where P2.personID > p.personID
)
Or you could add a table of sequential numbers which comes in handy for some
other sql chores as well:
Create Table SequentialNumber
(ID as long Not Null).
Fill that table with sequential numbers up to however many seem useful, and
then use that table to generate your pseudorow with a Where ID=1 clause.
"Rob W" <robwha DeleteThis @o2.co.uk> wrote in message
news:unmySQCIKHA.2516@TK2MSFTNGP02.phx.gbl...
> Well I created a small test database and table.
>
> The Union statement below creates a header row but for every single row in
> the person table.
> From testing it appeared the first select MUST always have a FROM clause
> though so Im clueless how to create a single row header.
>
> SELECT 0 as PseudoRow, "" as id, "" as firstname, "" as surname
> FROM Person p
> UNION
> SELECT 1, P.*
> FROM Person p
> ORDER by PseudoRow;
>
> How can I create a SINGLE header row?
>
> Thanks
> Rob
>
>
>
> "Douglas J. Steele" <NOSPAM_djsteele DeleteThis @NOSPAM_gmail.com> wrote in message
> news:elXkyqBIKHA.1252@TK2MSFTNGP04.phx.gbl...
>> "Rob W" <robwha DeleteThis @o2.co.uk> wrote in message
>> news:OLUeZjBIKHA.2516@TK2MSFTNGP02.phx.gbl...
>>
>>> Now to do this with a union I would have to ensure that the number of
>>> columns remain the same for all of select statements, which I find could
>>> be difficult
>>> as I'm selecting everything from the table, many columns.
>>
>> Unfortunately, there's no way around this. You could always write code
>> that generates the SQL for you, based on the number of fields in the
>> table in case it changes.
>>
>>> Also I don't know how to tie in the valuemember of strMembershipId to
>>> the pseudo header row as it won't have a strMembershipId value.
>>
>> Since all fields must match up, you have no choiice but to assign a value
>> for strMembershipId for the pseudo row. Choose a value you know will
>> never occur (like 0 or -1)
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele >> (no e-mails, please!)
(Msg. 6) Posted: Tue Aug 18, 2009 2:05 pm
Post subject: Re: SQL Union to create Header row for a combobox [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks I used a criteria (aggregate function) to limit output to a single
row as you suggested.
Thanks.
"Paul Shapiro" <paul.DeleteThis@hideme.broadwayData.com> wrote in message
news:eO1V6dCIKHA.5956@TK2MSFTNGP03.phx.gbl...
> You can either use a table which you know will always contain exactly one
> row, or add a criteria which limits output to a single row. I'm not sure
> which criteria Access allows but something like this might work:
>
> Select Top 1 0 as PseudoRow, ...
> The Top 1 sql clause limits output to a single row.
>
> Or From Person p Where p.personID = (Select Max(personID) From Person).
>
> Access might not accept a scalar subquery for the where clause, in which
> case you could change that to:
> From Person p
> Where Not Exists (
> Select * From Person as P2 Where P2.personID > p.personID
> )
>
> Or you could add a table of sequential numbers which comes in handy for
> some other sql chores as well:
> Create Table SequentialNumber
> (ID as long Not Null).
>
> Fill that table with sequential numbers up to however many seem useful,
> and then use that table to generate your pseudorow with a Where ID=1
> clause.
>
> "Rob W" <robwha.DeleteThis@o2.co.uk> wrote in message
> news:unmySQCIKHA.2516@TK2MSFTNGP02.phx.gbl...
>> Well I created a small test database and table.
>>
>> The Union statement below creates a header row but for every single row
>> in the person table.
>> From testing it appeared the first select MUST always have a FROM clause
>> though so Im clueless how to create a single row header.
>>
>> SELECT 0 as PseudoRow, "" as id, "" as firstname, "" as surname
>> FROM Person p
>> UNION
>> SELECT 1, P.*
>> FROM Person p
>> ORDER by PseudoRow;
>>
>> How can I create a SINGLE header row?
>>
>> Thanks
>> Rob
>>
>>
>>
>> "Douglas J. Steele" <NOSPAM_djsteele.DeleteThis@NOSPAM_gmail.com> wrote in message
>> news:elXkyqBIKHA.1252@TK2MSFTNGP04.phx.gbl...
>>> "Rob W" <robwha.DeleteThis@o2.co.uk> wrote in message
>>> news:OLUeZjBIKHA.2516@TK2MSFTNGP02.phx.gbl...
>>>
>>>> Now to do this with a union I would have to ensure that the number of
>>>> columns remain the same for all of select statements, which I find
>>>> could be difficult
>>>> as I'm selecting everything from the table, many columns.
>>>
>>> Unfortunately, there's no way around this. You could always write code
>>> that generates the SQL for you, based on the number of fields in the
>>> table in case it changes.
>>>
>>>> Also I don't know how to tie in the valuemember of strMembershipId to
>>>> the pseudo header row as it won't have a strMembershipId value.
>>>
>>> Since all fields must match up, you have no choiice but to assign a
>>> value for strMembershipId for the pseudo row. Choose a value you know
>>> will never occur (like 0 or -1)
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele >>> (no e-mails, please!)
>
(Msg. 7) Posted: Tue Aug 18, 2009 3:38 pm
Post subject: Re: SQL Union to create Header row for a combobox [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Even if there are multiple rows in the Person table, the fact that you're
using UNION will eliminate any duplicates.
You'd only have a problem if you used UNION ALL instead.
"Rob W" <robwha.DeleteThis@o2.co.uk> wrote in message
news:unmySQCIKHA.2516@TK2MSFTNGP02.phx.gbl...
> Well I created a small test database and table.
>
> The Union statement below creates a header row but for every single row in
> the person table.
> From testing it appeared the first select MUST always have a FROM clause
> though so Im clueless how to create a single row header.
>
> SELECT 0 as PseudoRow, "" as id, "" as firstname, "" as surname
> FROM Person p
> UNION
> SELECT 1, P.*
> FROM Person p
> ORDER by PseudoRow;
>
> How can I create a SINGLE header row?
>
> Thanks
> Rob
>
>
>
> "Douglas J. Steele" <NOSPAM_djsteele.DeleteThis@NOSPAM_gmail.com> wrote in message
> news:elXkyqBIKHA.1252@TK2MSFTNGP04.phx.gbl...
>> "Rob W" <robwha.DeleteThis@o2.co.uk> wrote in message
>> news:OLUeZjBIKHA.2516@TK2MSFTNGP02.phx.gbl...
>>
>>> Now to do this with a union I would have to ensure that the number of
>>> columns remain the same for all of select statements, which I find could
>>> be difficult
>>> as I'm selecting everything from the table, many columns.
>>
>> Unfortunately, there's no way around this. You could always write code
>> that generates the SQL for you, based on the number of fields in the
>> table in case it changes.
>>
>>> Also I don't know how to tie in the valuemember of strMembershipId to
>>> the pseudo header row as it won't have a strMembershipId value.
>>
>> Since all fields must match up, you have no choiice but to assign a value
>> for strMembershipId for the pseudo row. Choose a value you know will
>> never occur (like 0 or -1)
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele >> (no e-mails, please!)
>>
>>
>>
>
>
(Msg. 8) Posted: Tue Aug 18, 2009 5:05 pm
Post subject: Re: SQL Union to create Header row for a combobox [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Yes, I was mistakenly selecting real data from the table instead of the made
up data for the head row.
Thanks
"Douglas J. Steele" <NOSPAM_djsteele.TakeThisOut@NOSPAM_gmail.com> wrote in message
news:%23B2S7tDIKHA.5256@TK2MSFTNGP05.phx.gbl...
> Even if there are multiple rows in the Person table, the fact that you're
> using UNION will eliminate any duplicates.
>
> You'd only have a problem if you used UNION ALL instead.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele > (no e-mails, please!)
>
>
> "Rob W" <robwha.TakeThisOut@o2.co.uk> wrote in message
> news:unmySQCIKHA.2516@TK2MSFTNGP02.phx.gbl...
>> Well I created a small test database and table.
>>
>> The Union statement below creates a header row but for every single row
>> in the person table.
>> From testing it appeared the first select MUST always have a FROM clause
>> though so Im clueless how to create a single row header.
>>
>> SELECT 0 as PseudoRow, "" as id, "" as firstname, "" as surname
>> FROM Person p
>> UNION
>> SELECT 1, P.*
>> FROM Person p
>> ORDER by PseudoRow;
>>
>> How can I create a SINGLE header row?
>>
>> Thanks
>> Rob
>>
>>
>>
>> "Douglas J. Steele" <NOSPAM_djsteele.TakeThisOut@NOSPAM_gmail.com> wrote in message
>> news:elXkyqBIKHA.1252@TK2MSFTNGP04.phx.gbl...
>>> "Rob W" <robwha.TakeThisOut@o2.co.uk> wrote in message
>>> news:OLUeZjBIKHA.2516@TK2MSFTNGP02.phx.gbl...
>>>
>>>> Now to do this with a union I would have to ensure that the number of
>>>> columns remain the same for all of select statements, which I find
>>>> could be difficult
>>>> as I'm selecting everything from the table, many columns.
>>>
>>> Unfortunately, there's no way around this. You could always write code
>>> that generates the SQL for you, based on the number of fields in the
>>> table in case it changes.
>>>
>>>> Also I don't know how to tie in the valuemember of strMembershipId to
>>>> the pseudo header row as it won't have a strMembershipId value.
>>>
>>> Since all fields must match up, you have no choiice but to assign a
>>> value for strMembershipId for the pseudo row. Choose a value you know
>>> will never occur (like 0 or -1)
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele >>> (no e-mails, please!)
>>>
>>>
>>>
>>
>>
>
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other