If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
I have two separate fields in an employee records table for 'FirstName' and
'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? |
#2
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
"Graham A" wrote in message ... I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? |
#3
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
I'd be curious to see the SQL associated with the query you've already got.
Using SELECT DISTINCT [FirstName] & " " & [LastName] FROM MyTable ORDER BY [FirstName], [LastName] or SELECT DISTINCT [LastName] & ", " & [FirstName] FROM MyTable ORDER BY [LastName], [FirstName] should work fine. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? |
#4
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
On Oct 24, 7:28 pm, Graham A
wrote: I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? I'm not sure I fully understand your question. If you want to populate (for instance) a Combo Box with correct names, then I have a Query that does that in the Query field of the Drop-down list. It goes as follows SELECT PlayerID, [FirstName] & " " & [LastName] as [Name] From [Employees] You can then handle the "NotInList" event and parse the input the user typed, then do an insert into your table and requery the combobox. I'm not sure how you will handle the parsing if there are multiple parts to a last name or first name (eg. Jan De Boer). Perhaps you can create a pop-up form to ask specifically for the first name and last name in two fields and then perform the insert. Perhaps this will get you on the way until a more competent authority responds. M |
#5
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
Thanks for the info Douglas - hopefully I will get the time to try this
tomorrow. I would gladly copy the query I was using before to you, to satisfy your curiosity, but I deleted it (and its predecessors) when it didn't work the way I wanted it to! I think I tried various versions of Make Table and Update Table Queries.... "Douglas J. Steele" wrote: I'd be curious to see the SQL associated with the query you've already got. Using SELECT DISTINCT [FirstName] & " " & [LastName] FROM MyTable ORDER BY [FirstName], [LastName] or SELECT DISTINCT [LastName] & ", " & [FirstName] FROM MyTable ORDER BY [LastName], [FirstName] should work fine. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? |
#6
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
Mike Thanks for the info - hopefully I will get the time to try this out
tomorrow. I'll let you know how I get on... "MikeB" wrote: On Oct 24, 7:28 pm, Graham A wrote: I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? I'm not sure I fully understand your question. If you want to populate (for instance) a Combo Box with correct names, then I have a Query that does that in the Query field of the Drop-down list. It goes as follows SELECT PlayerID, [FirstName] & " " & [LastName] as [Name] From [Employees] You can then handle the "NotInList" event and parse the input the user typed, then do an insert into your table and requery the combobox. I'm not sure how you will handle the parsing if there are multiple parts to a last name or first name (eg. Jan De Boer). Perhaps you can create a pop-up form to ask specifically for the first name and last name in two fields and then perform the insert. Perhaps this will get you on the way until a more competent authority responds. M |
#7
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
Haven't had the chance to try this out until today and I can't get it to work
- maybe there is something I have misundertsood (I'm new at this!) I am assuming that I am supposed to be enetering the expression in the 'Criteria' filed of a Select Query: First I entered: SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName] I got the error message "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parenthesis" I then amended it to: (SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName]) This gave the error message "order by CLAUSE ([FirstName]) conflicts with DISTINCT" So then I tried: (SELECT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName]) Just to see if DISTINCT was the cause of the problem (as there aren't any duplicates in the table yet) and the expression would work without it. I then got the error message "At most one record can be returned by this subquery" which didn't seem to make any sense at all... There is obviously something fundamental that I am doing wrong - can you explain what it is please? Many thanks Graham A "Douglas J. Steele" wrote: I'd be curious to see the SQL associated with the query you've already got. Using SELECT DISTINCT [FirstName] & " " & [LastName] FROM MyTable ORDER BY [FirstName], [LastName] or SELECT DISTINCT [LastName] & ", " & [FirstName] FROM MyTable ORDER BY [LastName], [FirstName] should work fine. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? |
#8
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
What you're showing is a SQL string. You can't use it as the criteria for
another Select query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... Haven't had the chance to try this out until today and I can't get it to work - maybe there is something I have misundertsood (I'm new at this!) I am assuming that I am supposed to be enetering the expression in the 'Criteria' filed of a Select Query: First I entered: SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName] I got the error message "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parenthesis" I then amended it to: (SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName]) This gave the error message "order by CLAUSE ([FirstName]) conflicts with DISTINCT" So then I tried: (SELECT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName]) Just to see if DISTINCT was the cause of the problem (as there aren't any duplicates in the table yet) and the expression would work without it. I then got the error message "At most one record can be returned by this subquery" which didn't seem to make any sense at all... There is obviously something fundamental that I am doing wrong - can you explain what it is please? Many thanks Graham A "Douglas J. Steele" wrote: I'd be curious to see the SQL associated with the query you've already got. Using SELECT DISTINCT [FirstName] & " " & [LastName] FROM MyTable ORDER BY [FirstName], [LastName] or SELECT DISTINCT [LastName] & ", " & [FirstName] FROM MyTable ORDER BY [LastName], [FirstName] should work fine. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? |
#9
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
Oh dear, we are obiviously misunderstanding one another here! The SQL string
is basically the one you recommended, with the table name changed to suit my database. I wrote in my alsy response that "I am assuming that I am supposed to be entering the expression in the 'Criteria' filed of a Select Query", which from your comment below is obviously wrong, but you didn't say where I was supposed to put it - can you please explain where it should go? Many thanks Graham A "Douglas J. Steele" wrote: What you're showing is a SQL string. You can't use it as the criteria for another Select query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... Haven't had the chance to try this out until today and I can't get it to work - maybe there is something I have misundertsood (I'm new at this!) I am assuming that I am supposed to be enetering the expression in the 'Criteria' filed of a Select Query: First I entered: SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName] I got the error message "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parenthesis" I then amended it to: (SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName]) This gave the error message "order by CLAUSE ([FirstName]) conflicts with DISTINCT" So then I tried: (SELECT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName]) Just to see if DISTINCT was the cause of the problem (as there aren't any duplicates in the table yet) and the expression would work without it. I then got the error message "At most one record can be returned by this subquery" which didn't seem to make any sense at all... There is obviously something fundamental that I am doing wrong - can you explain what it is please? Many thanks Graham A "Douglas J. Steele" wrote: I'd be curious to see the SQL associated with the query you've already got. Using SELECT DISTINCT [FirstName] & " " & [LastName] FROM MyTable ORDER BY [FirstName], [LastName] or SELECT DISTINCT [LastName] & ", " & [FirstName] FROM MyTable ORDER BY [LastName], [FirstName] should work fine. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? |
#10
|
|||
|
|||
Concatenation of 2 fields to produce a lookup table
SELECT DISTINCT [FirstName] & " " & [LastName]
FROM Auditors ORDER BY [FirstName], [LastName] should be the complete SQL statement you need (although you might want to Alias the field, so that it's SELECT DISTINCT [FirstName] & " " & [LastName] AS FullName FROM Auditors ORDER BY [FirstName], [LastName] The data from the Auditors table should not be stored in any other table (you mention you "want to combine them to appear in a lookup table"). Instead, that SQL should be the RowSource for the combo (or list) box. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Graham A" wrote in message ... Oh dear, we are obiviously misunderstanding one another here! The SQL string is basically the one you recommended, with the table name changed to suit my database. I wrote in my alsy response that "I am assuming that I am supposed to be entering the expression in the 'Criteria' filed of a Select Query", which from your comment below is obviously wrong, but you didn't say where I was supposed to put it - can you please explain where it should go? Many thanks Graham A "Douglas J. Steele" wrote: What you're showing is a SQL string. You can't use it as the criteria for another Select query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... Haven't had the chance to try this out until today and I can't get it to work - maybe there is something I have misundertsood (I'm new at this!) I am assuming that I am supposed to be enetering the expression in the 'Criteria' filed of a Select Query: First I entered: SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName] I got the error message "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parenthesis" I then amended it to: (SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName]) This gave the error message "order by CLAUSE ([FirstName]) conflicts with DISTINCT" So then I tried: (SELECT [FirstName] & " " & [LastName] FROM Auditors ORDER BY [FirstName], [LastName]) Just to see if DISTINCT was the cause of the problem (as there aren't any duplicates in the table yet) and the expression would work without it. I then got the error message "At most one record can be returned by this subquery" which didn't seem to make any sense at all... There is obviously something fundamental that I am doing wrong - can you explain what it is please? Many thanks Graham A "Douglas J. Steele" wrote: I'd be curious to see the SQL associated with the query you've already got. Using SELECT DISTINCT [FirstName] & " " & [LastName] FROM MyTable ORDER BY [FirstName], [LastName] or SELECT DISTINCT [LastName] & ", " & [FirstName] FROM MyTable ORDER BY [LastName], [FirstName] should work fine. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Graham A" wrote in message ... I have two separate fields in an employee records table for 'FirstName' and 'LastName' and I want to combine them to appear in a lookup table as 'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on another area on the database to enable the user to select who has been allocated a task. I can generate such a table with an expression in a query but only if the query is run manually - this method also produces various error messages concerning amending or deleting records (or the table) which would alarm and / or confuse the user. The table used for the lookup source needs to be updated atuomatically anytime a new employee is added - I know that I will need to use some kind of Event action to trigger the update but I cannot find out how to make this work without being 'stalled' by the error messages which the user may not respond to correctly. Can anyone explain how this can be done please? |
Thread Tools | |
Display Modes | |
|
|