"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

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?

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

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?

*;^) }}

(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?

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

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?

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