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
|
|||
|
|||
"Combine" Fields?
Hello,
Employees have two available methods of entering time on a project (depends on the job function). One method uses the employee's ID, while the other just relies on a text field. Thus the unique identifying value winds up being stored in two different fields. See example below. Date EmployeeID Text $Amount ------------------------------------------------------ 8/11 1001 $50.00 8/11 1002 $75.00 8/11 Joe Blow's Time $62.50 Note that if the Employee ID field is used, then the Text field is blank, and vice versa. These time entries are intermingled with other project data -- expense reports, etc. I have a query that grabs both types of time entries (what you see above, more or less), but what I would like to do is have a new query that "combines" the EmployeeID and Text fields into a single field -- see below. NewField $Amount -------------------------------------- 1001 $50.00 Joe Blow's Time $62.50 1002 $75.00 Any help is appreciated. D |
#2
|
|||
|
|||
"Combine" Fields?
Do you realize how bad a design this is? You're mixing text and numeric fields. That'll screw up creating joins. One field should hold ONE kind of data. If I were you, I'd rethink my design. Or, what do you expect to save by doing this? |
#3
|
|||
|
|||
"Combine" Fields?
D, it will be *really* important to redesign this so that the table does not
contain redundant fieds. The Text field must go, so it relies on the EmployeeID only. If you have a really weird scenario where that is not possible for some reason (e.g. the text records come from an import where tehe EmployeeID is not available), I suggest that you put those values into a temporary table, and periodically commit them to the real table (correctly designed). That will include designing an interface that allows the user to resolve text entries that don't match (e.g. misspelling, new employee, left blank), duplicates, invalid entries (excessive or negative amounts, no date field, etc.) With that approach, your database maintains its integrity. Anything less is not worth pursuing. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Debris" wrote in message ink.net... Hello, Employees have two available methods of entering time on a project (depends on the job function). One method uses the employee's ID, while the other just relies on a text field. Thus the unique identifying value winds up being stored in two different fields. See example below. Date EmployeeID Text $Amount ------------------------------------------------------ 8/11 1001 $50.00 8/11 1002 $75.00 8/11 Joe Blow's Time $62.50 Note that if the Employee ID field is used, then the Text field is blank, and vice versa. These time entries are intermingled with other project data -- expense reports, etc. I have a query that grabs both types of time entries (what you see above, more or less), but what I would like to do is have a new query that "combines" the EmployeeID and Text fields into a single field -- see below. NewField $Amount -------------------------------------- 1001 $50.00 Joe Blow's Time $62.50 1002 $75.00 Any help is appreciated. D |
#4
|
|||
|
|||
"Combine" Fields?
Dear Santa,
for Christmas, may I have Allen's patience and ability to explain things... patiently. Or do I have to learn that the hard way? |
#5
|
|||
|
|||
"Combine" Fields?
*;^) }}
(The Santa grin.) -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. wrote in message oups.com... Dear Santa, for Christmas, may I have Allen's patience and ability to explain things... patiently. Or do I have to learn that the hard way? |
#6
|
|||
|
|||
"Combine" Fields?
LOL
First, let me make clear that I did NOT design the database in question... : ) I'm extracting from our ERP time entry data for a given project and importing it into Access or Excel. I don't think can't get rid of the Doc Text field -- while some employees enter time via a formal "electronic timesheet" (employee ID, hours, reason codes, etc.) on a daily/weekly basis, others simply create at the end of the month what amounts to an unverifiable, virtually untraceable journal entry. Problem is, if Joe Blow doesn't enter "Joe Blow's Time" in the text field, then it becomes really difficult to track down the source of the entry. (Fortunately most folks charge time via electronic timesheet; the journal entries are more of the exception, not the rule.) I'm going to blame this goofiness on "business processes." : ) Right now, after I "scrub" the data in Access, I basically export the data (i.e. the results of a select query) to Excel and create two pivot tables, one looking at the employee ID, one looking at the Doc Text field. After sleeping on it, this is probably way more trouble than it's worth... anyway, thanks for the help, D "Allen Browne" wrote in message ... D, it will be *really* important to redesign this so that the table does not contain redundant fieds. The Text field must go, so it relies on the EmployeeID only. If you have a really weird scenario where that is not possible for some reason (e.g. the text records come from an import where tehe EmployeeID is not available), I suggest that you put those values into a temporary table, and periodically commit them to the real table (correctly designed). That will include designing an interface that allows the user to resolve text entries that don't match (e.g. misspelling, new employee, left blank), duplicates, invalid entries (excessive or negative amounts, no date field, etc.) With that approach, your database maintains its integrity. Anything less is not worth pursuing. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Debris" wrote in message ink.net... Hello, Employees have two available methods of entering time on a project (depends on the job function). One method uses the employee's ID, while the other just relies on a text field. Thus the unique identifying value winds up being stored in two different fields. See example below. Date EmployeeID Text $Amount ------------------------------------------------------ 8/11 1001 $50.00 8/11 1002 $75.00 8/11 Joe Blow's Time $62.50 Note that if the Employee ID field is used, then the Text field is blank, and vice versa. These time entries are intermingled with other project data -- expense reports, etc. I have a query that grabs both types of time entries (what you see above, more or less), but what I would like to do is have a new query that "combines" the EmployeeID and Text fields into a single field -- see below. NewField $Amount -------------------------------------- 1001 $50.00 Joe Blow's Time $62.50 1002 $75.00 Any help is appreciated. D |
#7
|
|||
|
|||
"Combine" Fields?
LOL
"Allen Browne" wrote in message ... *;^) }} (The Santa grin.) -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. wrote in message oups.com... Dear Santa, for Christmas, may I have Allen's patience and ability to explain things... patiently. Or do I have to learn that the hard way? |
#8
|
|||
|
|||
"Combine" Fields?
something like:-
IIf(IsNull([field2]),[field1],[field2]) AS xrated but like they said this is bad, bad, bad. "Debris" wrote in message ink.net... LOL First, let me make clear that I did NOT design the database in question... : ) I'm extracting from our ERP time entry data for a given project and importing it into Access or Excel. I don't think can't get rid of the Doc Text field -- while some employees enter time via a formal "electronic timesheet" (employee ID, hours, reason codes, etc.) on a daily/weekly basis, others simply create at the end of the month what amounts to an unverifiable, virtually untraceable journal entry. Problem is, if Joe Blow doesn't enter "Joe Blow's Time" in the text field, then it becomes really difficult to track down the source of the entry. (Fortunately most folks charge time via electronic timesheet; the journal entries are more of the exception, not the rule.) I'm going to blame this goofiness on "business processes." : ) Right now, after I "scrub" the data in Access, I basically export the data (i.e. the results of a select query) to Excel and create two pivot tables, one looking at the employee ID, one looking at the Doc Text field. After sleeping on it, this is probably way more trouble than it's worth... anyway, thanks for the help, D "Allen Browne" wrote in message ... D, it will be *really* important to redesign this so that the table does not contain redundant fieds. The Text field must go, so it relies on the EmployeeID only. If you have a really weird scenario where that is not possible for some reason (e.g. the text records come from an import where tehe EmployeeID is not available), I suggest that you put those values into a temporary table, and periodically commit them to the real table (correctly designed). That will include designing an interface that allows the user to resolve text entries that don't match (e.g. misspelling, new employee, left blank), duplicates, invalid entries (excessive or negative amounts, no date field, etc.) With that approach, your database maintains its integrity. Anything less is not worth pursuing. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Debris" wrote in message ink.net... Hello, Employees have two available methods of entering time on a project (depends on the job function). One method uses the employee's ID, while the other just relies on a text field. Thus the unique identifying value winds up being stored in two different fields. See example below. Date EmployeeID Text $Amount ------------------------------------------------------ 8/11 1001 $50.00 8/11 1002 $75.00 8/11 Joe Blow's Time $62.50 Note that if the Employee ID field is used, then the Text field is blank, and vice versa. These time entries are intermingled with other project data -- expense reports, etc. I have a query that grabs both types of time entries (what you see above, more or less), but what I would like to do is have a new query that "combines" the EmployeeID and Text fields into a single field -- see below. NewField $Amount -------------------------------------- 1001 $50.00 Joe Blow's Time $62.50 1002 $75.00 Any help is appreciated. D |
Thread Tools | |
Display Modes | |
|
|