"Combine" Fields?

[Debris]

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

 

[pietlinden]

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?

 

[Allen Browne]

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.

 

[pietlinden]

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?

 

[Allen Browne]

*<<;^) }}

(The Santa grin.)

 

[Debris]

< 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

 

[tina]

LOL

*<<;^) }}

(The Santa grin.)

 

[David Cox]

something like:-

IIf(IsNull([field2]),[field1],[field2]) AS xrated

but like they said this is bad, bad, bad.