Repost: Calculation problem. Someone help!

Good morning, Victor,
I see another response - perhaps you can use that
approach if it works better, but read on.

I think this will work for you. You'll need to make
two queries; one to select date-matched records from the
source table, create a new value ("Results") for the ID
field, and calculate the difference between the Gross in
the Details Page record and the Gross in the Landings Page
record of the same date; and another query to append the
information created in the first query back into the table.

1. Create a Select query that joins two instances of the
source table through the date field. When you add the
second instance of the table, Access will append _1 to the
table name.
- Add the Date, ID and Gross fields from the first
instance of the table to the grid.
- Add the ID and Gross fields from the second instance to
the grid.
- Add a calculated variable "NewID: Results" (no quotes)
to the grid.
- Add another calculated variable "Diff: [table]![gross]-
[table_1]![gross]" (no quotes) to the grid. Use the real
names in place of my references to [table] and [gross]
- Set the criterion for the ID field from the first table
to "Details Page". Set the criterion for the ID field
from the second table to "Landings Page"

2. If you run this query by itself, you'll see a record
for each date-matched pair with the "Result" ID and the
calculated difference. If there are any misspelled ID
fields, the query will not find a match, so be sure the ID
field is correct in all fields of the original table.
Likewise, if there are any incorrect dates, there will be
no match or the wrong records will be matched. This query
REQUIRES that there be only one record set per date, as
you indicated before.

3. Create a second query that uses the first query as a
source. Make it an Append query with the original table
as the target.
- Add the Date, NewID and Diff fields to the grid.
- Append the ID field to the ID field, NewID to the ID
field and Diff to the Gross field.

4. If you run this query, Access will append the
appropriate fields from the first query into the table n a
new, dated Results record. Check the results. If you run
this query more than once it will create duplicate results
records, so watch out. If you have to re-run the query,
delete the previously created and appended Results records
OR add a limiting criterion to the date field so that only
records with dates other than the ones already appended
will be passed through.

Let me know how this works for you. If you need more
information or explanation, post back.

Good luck. HJ

-----Original Message-----
Hi there ... thanks for your diligence on this. please
see my responses
below. thanks

"others jump in please" wrote:

Hi Victor,
I see a possible solution if the record sets are
always
tied together by the date field - one record set per
unique date. True? TRUE


Not true? Also, I don't see
any "opt1" records in your sample - are they added
later
and tagged with the same date as the related
records? --- NO, I just left it out of the table to
make it smaller for pasting in this message. but the
structure is the same as shown with a few more values in
the id description field


Also, I presume that you want the new "results" record
to
be tagged with the related date as well. True? YES

My day is almost over (CDT), and I want to test the
idea
before I pass it along - don't get your hopes too high
at
this point! My coding skills are not good, so if the
idea
doesn't work with a pretty straightforward Make Table
query, then I'll have to yield to others. I'm also
thinking that an Excel solution might work, too (export
the data out, sort it, create the results record, and
import back in to Access). ------ OK THANKS

Talk to you tomorrow.
hj

-----Original Message-----
Here is a sample of the actaul table..

Date ID Description field 1 field 2 Gross
Revenue

2/19/2004 Details Page 216610 165104 1007.98
2/19/2004 Landing Page 4968 4878 527.94
2/19/2004 No Results Page 137264 74944
3093.14
2/20/2004 Details Page 346056 259348 1831.66
2/20/2004 Landing Page 10308 10272 735.82
2/20/2004 No Results Page 223462 121882
4845.06
2/21/2004 Details Page 295816 222550 1672.56
2/21/2004 Landing Page 9054 9002 688.34
2/21/2004 No Results Page 167172 94252
4180.94
2/22/2004 Details Page 275650 211330 1981.48
2/22/2004 Landing Page 8102 8040 726.28
2/22/2004 No Results Page 148674 84052
3791.76


Thanks for your help!


"others jump in please" wrote:

A bit more info please, but your problem may be
difficult
to solve.
Do the record samples you gave occur many times in
the
table; e.g., are there many instances of records
with
ClientID= "details"? YES

Do the records always occur in four-
record sets of
ClientID="details", "landing", "noresults",
and "opt1"? YES

Do the records have any other fields that
might connect or relate one record to another? NO

The reason
for that question is that there needs to be a way to
uniquely relate one instance of record "details"
& "amt."
with its corresponding record of "landing" & "amt."

I'm thinking that if there is no way to positively
and
uniquely connect associated records then doing a
unique
calculation will not be possible.

If, on the other hand, the table only contains four
records, i.e. one record for "details", one related
record
for "landing", etc., then there might be a solution.

Hang in!

-----Original Message-----
Hi everyone...

Sorry for the confusion. Client iD, Id Description
and
Amt. are separate
fields in my table. Clinet id has details,
landing ,
no
results and opt1 as
values. I need to generate a new value in the
client
id
field called
"Results" where results takes the Amt. value of
landing
and subtracts it.
from the amt value. of details ...... Thanks

" wrote:

Don't know about rest of the group, but I was
really
puzzled by your question the first time around,
so I
really couldn't think it through too clearly.

Perhaps you need to give more information about
your
table
design. Is "ClientID" a field in the table,
with "details", "landing" etc. being values in
that
field
in separate records? Are "ID description"
and "amt."
separate fields?

If you can help out a bit with more explanation,
maybe
some more people will jump in and help you find a
solution.

Regards.

-----Original Message-----
I am reporting due to lack of repsone. However,
I
really
owuld like some help
on this.

I have a table which looks like this:

ClientID ID Description amt.
details Details Page $10
landing Landing Page $10
noresults No Results Page $10
opt1 Picker Page $10

I need to create a new calculated item under
ClientID
called "Results" which will be results =
details -
landing. so that in query output, i will see all
the
line
items under client id and the new calculated
item
in
client id called results ....

Thanks

.


.


.


.

Ouch - I misstated something:
Where I said "Append the ID field to the ID field" down
in step 3, I should have said "append the Date field to
the Date field" - actually Access should recognize this
automatically.
Sorry

-----Original Message-----
Good morning, Victor,
I see another response - perhaps you can use that
approach if it works better, but read on.

I think this will work for you. You'll need to make
two queries; one to select date-matched records from the
source table, create a new value ("Results") for the ID
field, and calculate the difference between the Gross in
the Details Page record and the Gross in the Landings
Page
record of the same date; and another query to append the
information created in the first query back into the
table.

1. Create a Select query that joins two instances of the
source table through the date field. When you add the
second instance of the table, Access will append _1 to
the
table name.
- Add the Date, ID and Gross fields from the first
instance of the table to the grid.
- Add the ID and Gross fields from the second instance to
the grid.
- Add a calculated variable "NewID: Results" (no quotes)
to the grid.
- Add another calculated variable "Diff: [table]![gross]-
[table_1]![gross]" (no quotes) to the grid. Use the real
names in place of my references to [table] and [gross]
- Set the criterion for the ID field from the first table
to "Details Page". Set the criterion for the ID field
from the second table to "Landings Page"

2. If you run this query by itself, you'll see a record
for each date-matched pair with the "Result" ID and the
calculated difference. If there are any misspelled ID
fields, the query will not find a match, so be sure the
ID
field is correct in all fields of the original table.
Likewise, if there are any incorrect dates, there will be
no match or the wrong records will be matched. This
query
REQUIRES that there be only one record set per date, as
you indicated before.

3. Create a second query that uses the first query as a
source. Make it an Append query with the original table
as the target.
- Add the Date, NewID and Diff fields to the grid.
- Append the ID field to the ID field, NewID to the ID
field and Diff to the Gross field.

4. If you run this query, Access will append the
appropriate fields from the first query into the table n
a
new, dated Results record. Check the results. If you
run
this query more than once it will create duplicate
results
records, so watch out. If you have to re-run the query,
delete the previously created and appended Results
records
OR add a limiting criterion to the date field so that
only
records with dates other than the ones already appended
will be passed through.

Let me know how this works for you. If you need more
information or explanation, post back.

Good luck. HJ

-----Original Message-----
Hi there ... thanks for your diligence on this. please
see my responses
below. thanks

"others jump in please" wrote:

Hi Victor,
I see a possible solution if the record sets are
always
tied together by the date field - one record set per
unique date. True? TRUE


Not true? Also, I don't see
any "opt1" records in your sample - are they added
later
and tagged with the same date as the related
records? --- NO, I just left it out of the table to
make it smaller for pasting in this message. but the
structure is the same as shown with a few more values in
the id description field


Also, I presume that you want the new "results" record
to
be tagged with the related date as well. True? YES

My day is almost over (CDT), and I want to test the
idea
before I pass it along - don't get your hopes too high
at
this point! My coding skills are not good, so if the
idea
doesn't work with a pretty straightforward Make Table
query, then I'll have to yield to others. I'm also
thinking that an Excel solution might work, too
(export
the data out, sort it, create the results record, and
import back in to Access). ------ OK THANKS

Talk to you tomorrow.
hj

-----Original Message-----
Here is a sample of the actaul table..

Date ID Description field 1 field 2 Gross
Revenue

2/19/2004 Details Page 216610 165104 1007.98
2/19/2004 Landing Page 4968 4878 527.94
2/19/2004 No Results Page 137264 74944
3093.14
2/20/2004 Details Page 346056 259348 1831.66
2/20/2004 Landing Page 10308 10272 735.82
2/20/2004 No Results Page 223462 121882
4845.06
2/21/2004 Details Page 295816 222550 1672.56
2/21/2004 Landing Page 9054 9002 688.34
2/21/2004 No Results Page 167172 94252
4180.94
2/22/2004 Details Page 275650 211330 1981.48
2/22/2004 Landing Page 8102 8040 726.28
2/22/2004 No Results Page 148674 84052
3791.76


Thanks for your help!


"others jump in please" wrote:

A bit more info please, but your problem may be
difficult
to solve.
Do the record samples you gave occur many times in
the
table; e.g., are there many instances of records
with
ClientID= "details"? YES

Do the records always occur in four-
record sets of
ClientID="details", "landing", "noresults",
and "opt1"? YES

Do the records have any other fields that
might connect or relate one record to another? NO

The reason
for that question is that there needs to be a way
to
uniquely relate one instance of record "details"
& "amt."
with its corresponding record of "landing" & "amt."

I'm thinking that if there is no way to positively
and
uniquely connect associated records then doing a
unique
calculation will not be possible.

If, on the other hand, the table only contains four
records, i.e. one record for "details", one related
record
for "landing", etc., then there might be a solution.

Hang in!

-----Original Message-----
Hi everyone...

Sorry for the confusion. Client iD, Id Description
and
Amt. are separate
fields in my table. Clinet id has details,
landing ,
no
results and opt1 as
values. I need to generate a new value in the
client
id
field called
"Results" where results takes the Amt. value of
landing
and subtracts it.
from the amt value. of details ...... Thanks

" wrote:

Don't know about rest of the group, but I was
really
puzzled by your question the first time around,
so I
really couldn't think it through too clearly.

Perhaps you need to give more information about
your
table
design. Is "ClientID" a field in the table,
with "details", "landing" etc. being values in
that
field
in separate records? Are "ID description"
and "amt."
separate fields?

If you can help out a bit with more explanation,
maybe
some more people will jump in and help you find
a
solution.

Regards.

-----Original Message-----
I am reporting due to lack of repsone. However,
I
really
owuld like some help
on this.

I have a table which looks like this:

ClientID ID Description amt.
details Details Page $10
landing Landing Page $10
noresults No Results Page $10
opt1 Picker Page $10

I need to create a new calculated item under
ClientID
called "Results" which will be results =
details -
landing. so that in query output, i will see
all
the
line
items under client id and the new calculated
item
in
client id called results ....

Thanks

.


.


.


.

.

Hi there ... Thanks for your response. I am not doing a report, however,
your solution might get me to do one anyway. I am now focusing on the
solution proposed by HJ. Will let you all know if it works. Thanks so much!

Victor


"Gary Walter" wrote:

oops...the subform query needs a FROM clause

SELECT [Date],
Sum(IIF([ID]='Details',[Gross Revenue],
IIF([ID]='Landing',-[Gross Revenue],0))) As Result
FROM yourtable
GROUP BY [Date];


"Gary Walter" wrote:

Here might be one perspective if I
understand correctly.

How do you want to present the results
of this query?

If in a report, then you can just group on
date, then in your group footer calculate
the "Result."

For example, base your report on a query
similar to (watch word wrap):

SELECT *,
IIF([ID]='Details',[Gross Revenue],
IIF([ID]='Landing',-[Gross Revenue],0)) As PreResult
FROM yourtable;

then Control Source for textbox in group footer
(with label "Result") would be

= Sum([PreResult])

In a form, you could have a subform based
on a query like:

SELECT [Date],
Sum(IIF([ID]='Details',[Gross Revenue],
IIF([ID]='Landing',-[Gross Revenue],0))) As Result
GROUP BY [Date];

and tie it (master/child) to the main form on [Date]

(I assume your field name is not really the reserved
word "Date", but just used here for brevity)

Of course, I may have completely misunderstood.

Good luck,

Gary Walter

"Victor" wrote:
Here is a sample of the actaul table..

Date ID Description field 1 field 2 Gross Revenue

2/19/2004 Details Page 216610 165104 1007.98
2/19/2004 Landing Page 4968 4878 527.94
2/19/2004 No Results Page 137264 74944 3093.14
2/20/2004 Details Page 346056 259348 1831.66
2/20/2004 Landing Page 10308 10272 735.82
2/20/2004 No Results Page 223462 121882 4845.06
2/21/2004 Details Page 295816 222550 1672.56
2/21/2004 Landing Page 9054 9002 688.34
2/21/2004 No Results Page 167172 94252 4180.94
2/22/2004 Details Page 275650 211330 1981.48
2/22/2004 Landing Page 8102 8040 726.28
2/22/2004 No Results Page 148674 84052 3791.76


Thanks for your help!


"others jump in please" wrote:

A bit more info please, but your problem may be difficult
to solve.
Do the record samples you gave occur many times in the
table; e.g., are there many instances of records with
ClientID= "details"? YES

Do the records always occur in four-
record sets of ClientID="details", "landing", "noresults",
and "opt1"? YES

Do the records have any other fields that
might connect or relate one record to another? NO

The reason
for that question is that there needs to be a way to
uniquely relate one instance of record "details" & "amt."
with its corresponding record of "landing" & "amt."

I'm thinking that if there is no way to positively and
uniquely connect associated records then doing a unique
calculation will not be possible.

If, on the other hand, the table only contains four
records, i.e. one record for "details", one related record
for "landing", etc., then there might be a solution.

Hang in!

-----Original Message-----
Hi everyone...

Sorry for the confusion. Client iD, Id Description and
Amt. are separate
fields in my table. Clinet id has details, landing , no
results and opt1 as
values. I need to generate a new value in the client id
field called
"Results" where results takes the Amt. value of landing
and subtracts it.
from the amt value. of details ...... Thanks

" wrote:

Don't know about rest of the group, but I was really
puzzled by your question the first time around, so I
really couldn't think it through too clearly.

Perhaps you need to give more information about your
table
design. Is "ClientID" a field in the table,
with "details", "landing" etc. being values in that
field
in separate records? Are "ID description" and "amt."
separate fields?

If you can help out a bit with more explanation, maybe
some more people will jump in and help you find a
solution.

Regards.

-----Original Message-----
I am reporting due to lack of repsone. However, I
really
owuld like some help
on this.

I have a table which looks like this:

ClientID ID Description amt.
details Details Page $10
landing Landing Page $10
noresults No Results Page $10
opt1 Picker Page $10

I need to create a new calculated item under ClientID
called "Results" which will be results = details -
landing. so that in query output, i will see all the
line
items under client id and the new calculated item in
client id called results ....

Thanks

.


.

HJ - thanks for looking at this. I will take a look at your solution asap and
let you know, but going through it, I think it might work. thanks!

"HJ" wrote:

Ouch - I misstated something:
Where I said "Append the ID field to the ID field" down
in step 3, I should have said "append the Date field to
the Date field" - actually Access should recognize this
automatically.
Sorry

-----Original Message-----
Good morning, Victor,
I see another response - perhaps you can use that
approach if it works better, but read on.

I think this will work for you. You'll need to make
two queries; one to select date-matched records from the
source table, create a new value ("Results") for the ID
field, and calculate the difference between the Gross in
the Details Page record and the Gross in the Landings
Page
record of the same date; and another query to append the
information created in the first query back into the
table.

1. Create a Select query that joins two instances of the
source table through the date field. When you add the
second instance of the table, Access will append _1 to
the
table name.
- Add the Date, ID and Gross fields from the first
instance of the table to the grid.
- Add the ID and Gross fields from the second instance to
the grid.
- Add a calculated variable "NewID: Results" (no quotes)
to the grid.
- Add another calculated variable "Diff: [table]![gross]-
[table_1]![gross]" (no quotes) to the grid. Use the real
names in place of my references to [table] and [gross]
- Set the criterion for the ID field from the first table
to "Details Page". Set the criterion for the ID field
from the second table to "Landings Page"

2. If you run this query by itself, you'll see a record
for each date-matched pair with the "Result" ID and the
calculated difference. If there are any misspelled ID
fields, the query will not find a match, so be sure the
ID
field is correct in all fields of the original table.
Likewise, if there are any incorrect dates, there will be
no match or the wrong records will be matched. This
query
REQUIRES that there be only one record set per date, as
you indicated before.

3. Create a second query that uses the first query as a
source. Make it an Append query with the original table
as the target.
- Add the Date, NewID and Diff fields to the grid.
- Append the ID field to the ID field, NewID to the ID
field and Diff to the Gross field.

4. If you run this query, Access will append the
appropriate fields from the first query into the table n
a
new, dated Results record. Check the results. If you
run
this query more than once it will create duplicate
results
records, so watch out. If you have to re-run the query,
delete the previously created and appended Results
records
OR add a limiting criterion to the date field so that
only
records with dates other than the ones already appended
will be passed through.

Let me know how this works for you. If you need more
information or explanation, post back.

Good luck. HJ

-----Original Message-----
Hi there ... thanks for your diligence on this. please
see my responses
below. thanks

"others jump in please" wrote:

Hi Victor,
I see a possible solution if the record sets are
always
tied together by the date field - one record set per
unique date. True? TRUE


Not true? Also, I don't see
any "opt1" records in your sample - are they added
later
and tagged with the same date as the related
records? --- NO, I just left it out of the table to
make it smaller for pasting in this message. but the
structure is the same as shown with a few more values in
the id description field


Also, I presume that you want the new "results" record
to
be tagged with the related date as well. True? YES

My day is almost over (CDT), and I want to test the
idea
before I pass it along - don't get your hopes too high
at
this point! My coding skills are not good, so if the
idea
doesn't work with a pretty straightforward Make Table
query, then I'll have to yield to others. I'm also
thinking that an Excel solution might work, too
(export
the data out, sort it, create the results record, and
import back in to Access). ------ OK THANKS

Talk to you tomorrow.
hj

-----Original Message-----
Here is a sample of the actaul table..

Date ID Description field 1 field 2 Gross
Revenue

2/19/2004 Details Page 216610 165104 1007.98
2/19/2004 Landing Page 4968 4878 527.94
2/19/2004 No Results Page 137264 74944
3093.14
2/20/2004 Details Page 346056 259348 1831.66
2/20/2004 Landing Page 10308 10272 735.82
2/20/2004 No Results Page 223462 121882
4845.06
2/21/2004 Details Page 295816 222550 1672.56
2/21/2004 Landing Page 9054 9002 688.34
2/21/2004 No Results Page 167172 94252
4180.94
2/22/2004 Details Page 275650 211330 1981.48
2/22/2004 Landing Page 8102 8040 726.28
2/22/2004 No Results Page 148674 84052
3791.76


Thanks for your help!


"others jump in please" wrote:

A bit more info please, but your problem may be
difficult
to solve.
Do the record samples you gave occur many times in
the
table; e.g., are there many instances of records
with
ClientID= "details"? YES

Do the records always occur in four-
record sets of
ClientID="details", "landing", "noresults",
and "opt1"? YES

Do the records have any other fields that
might connect or relate one record to another? NO

The reason
for that question is that there needs to be a way
to
uniquely relate one instance of record "details"
& "amt."
with its corresponding record of "landing" & "amt."

I'm thinking that if there is no way to positively
and
uniquely connect associated records then doing a
unique
calculation will not be possible.

If, on the other hand, the table only contains four
records, i.e. one record for "details", one related
record
for "landing", etc., then there might be a solution.

Hang in!

-----Original Message-----
Hi everyone...

Sorry for the confusion. Client iD, Id Description
and
Amt. are separate
fields in my table. Clinet id has details,
landing ,
no
results and opt1 as
values. I need to generate a new value in the
client
id
field called
"Results" where results takes the Amt. value of
landing
and subtracts it.
from the amt value. of details ...... Thanks

" wrote:

Don't know about rest of the group, but I was
really
puzzled by your question the first time around,
so I
really couldn't think it through too clearly.

Perhaps you need to give more information about
your
table
design. Is "ClientID" a field in the table,
with "details", "landing" etc. being values in
that
field
in separate records? Are "ID description"
and "amt."
separate fields?

If you can help out a bit with more explanation,
maybe
some more people will jump in and help you find
a
solution.

Regards.

-----Original Message-----
I am reporting due to lack of repsone. However,
I
really
owuld like some help
on this.

I have a table which looks like this:

ClientID ID Description amt.
details Details Page $10
landing Landing Page $10
noresults No Results Page $10
opt1 Picker Page $10

I need to create a new calculated item under
ClientID
called "Results" which will be results =
details -
landing. so that in query output, i will see
all
the
line
items under client id and the new calculated
item
in
client id called results ....

Thanks

.


.


.


.

.