Creating a field based on other fields

J

J. L.

I'm trying to create a report from either tables or queries.

I want to be able to have two fields of data in each record evaluated so
that I can produce data that's not in the field.

Example.

Field1 Field2 Field3
A1 B1 C1
A2 B1 C2
A3 B2 C3
A4 B2 C4

Field3 is generated from the results of Field's 1 and 2 and new data (not in
any table) is placed there.

If anyone knows how to do that I would appreciate it, as I'm under a
deadline.
 
T

Tom Ellison

Dear J. L.:

I can only begin to guess at how you want to generate the data in
Field3 based on Field1 and Field2. Does it always consist of a 'C'
followed by the larger of the digit values from the second positions
of Field1 and Field2? For the sample data you give this would work,
but what you want done may be totally different. Could you explain
this in precise, unambiguous terms?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
J

J. L.

Sorry Tom,

Issue 1)
I wanted Field3 to be automatically generated based upon what was in
Fields 1 and 2 in the query so that the report would have a column showing
the data from Field 3 as oppossed to Fields 1 and 2.

Issue 2 (seperate of Issue 1)
I have to create a report that shows total hours worked in the current
month, vs, total hours since the project began. I have to make a seperate
report that does the same for dollars spent this month vs running total, and
amount of records generated this month vs total record count.
 
T

Tom Ellison

Dear J. L.:

Please understand I am ignorant how you want to do this, and you
haven't explained it at all:
I wanted Field3 to be automatically generated based upon what was in
Fields 1 and 2 in the query so that the report would have a column showing
the data from Field 3 as oppossed to Fields 1 and 2.
Click to expand...

Do you already have a function that does this? Do you want my help in
doing it? Your example mysteriously transforms Field1 and Field2 into
some new value in a way that is likely obvious to you, but not to me.
Can you describe how you want this done? Or is this something with
which you want assistance.

Now there is a new Issue 2 I didn't know about before. I'd prefer to
stick to one disaster at a time, please. We can come back to that
later, but I'll need to know the table structure and the method there
too if I'm to be of any help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
J

J. L.

Tom,
You're getting it.

If Field1 is one thing, and Field2 is another, then I want Field3 to be
something not in either fields, because of what was in Fields 1 and 2.

Say Field1 holds type of payment (Check, Money Order, Cash) and Field2 holds
type of customer (Residential, Commercial).

Now for example, if the data in Field1 of a record were "Check" and the data
of Field2 of the same record were "Residential", can I cause the word
"ResidAcctPay" to show up in Field3 of the same record?
 
T

Tom Ellison

Dear J. L.:

I think you're wrong about one thing. I'm not getting it.

For all I can see, there is some giant leap in logic that says when
Field1 is "Check" and Field2 is "Residential" then generate from this
"ResidAcctPay". Just where did that come from? Why isn't it
"GodzillaAteTokyo" instead? Of all the things you could pick given
that Field1 is "Check" and Field2 is "Residential" how did you come up
with "ResidAcctPay"? Do you have a table showing all possible
combinations of Field1 values and Field2 values that tells us what to
put into Field3? Is there some algorithm you can describe that would
generate "ResidAcctPay" from this fact?

Sorry, but I'm not getting it. I don't see why "ResidAcctPay"
suddenly jumps out as the obvious result.

So, really, how do you determine what goes here? And, assuming it can
be readily derived, why do you want to store it in the table? It is
an excellent and important rule not to store readily derivable
information. Instead, just generate it when you need it.

I'd love to be able to help you, but you haven't given me any way to
figure this out. Perhaps you should try a simple cross-product
conversion table:

Field1 Field2 Result
Check Residential ResidAcctPay
Money Order Residential MothraIsNaughty
Cash Residential GhengisKhanEatsOats
Check Commercial AlexanderIsGreat
Money Order Commercial JuliusIsCaesarDressing
Cash Commercial NeroFiddlesNobodyDances

A simple JOIN to such a table will give you the result you seek. But
I strongly recommend you not store it. Just derive it whenever it is
needed. Otherwise you must update it any time Field1 or Field2
changes, and for any new row. This is much more work for you and for
the computer in the long run.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
J

Jacques Latoison

Thank you,
My apologies if my description is confusing. Some of your e-mail seems
to answer the question though.
I'm really not certain how else to explain it, but the part where you
wrote:

| Field1 Field2 Result
| Check Residential ResidAcctPay
| Money Order Residential MothraIsNaughty
| Cash Residential GhengisKhanEatsOats
| Check Commercial AlexanderIsGreat
| Money Order Commercial JuliusIsCaesarDressing
| Cash Commercial NeroFiddlesNobodyDances

....this is what I'm trying to do, I'm just uncertain as to how to make that
happen. What I did instead was added a field for the data entry people to
manually state what data should be there.
 
T

Tom Ellison

Dear Jacques:

I'm pleased there has been some communication. And please accept my
weak attempts at humor as just that, and not sarcasm.

I was recommending you create a table to map all possible combinations
of the given data (Field1 and Field2) into whatever result you feel
you want. Unless there is some way for the computer to "calculate"
the value, this would be your best option.

So, I was suggesting you create a new table and fill in the necessary
values. You can JOIN to this table in your query on the Field1 and
Field2 values, and it will then provide the Result value and that will
be available in your query.

This is pretty straight forward in terms of creating both the table
and the query. If you are having trouble with it, please let me know.

Notice that, if any new "possible" values for Field1 or Field2 are
added, the table of all combinations will grow. If users are allowed
to do this, bad things will happen. Where there is no available
Result in the table for a combination of Field1 and Field2, the entire
row of data will drop out. A LEFT JOIN to this table would be
preferable - or in the design grid, show all the rows in your main
query, but only those rows from this new table where . . .

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Top