Repost: Calculation problem. Someone help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Instead of re-posting the exact wording of your previous post, re
think/re-word the problem and post the re-worded problem. Either no one
thinks your problem is interesting or we just can't understand what
you're trying to do. E.g.: How does one subtracting the word "landing"
from the word "details"? Do you mean subtract the "landing" "amt" value
from the "details" "amt" value?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRKESoechKqOuFEgEQLfbwCgiMtKdkljRfCbFFxMZQh9rWH9bvoAn3jc
3KGwp7etfdK/BsCUCEk2EiZG
=NCEF
-----END PGP SIGNATURE-----
 
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.
 
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"? Do the records always occur in four-
record sets of ClientID="details", "landing", "noresults",
and "opt1"? Do the records have any other fields that
might connect or relate one record to another? 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!
 
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 said:
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
Click to expand...

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

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

The reason
 
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? 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?
Also, I presume that you want the new "results" record to
be tagged with the related date as well. True?

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).

Talk to you tomorrow.
hj
 
Hi there ... thanks for your diligence on this. please see my responses
below. thanks

others jump in please said:
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
Click to expand...


Not true? Also, I don't see
 
Hi Victor,

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
 
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 said:
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 said:
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!




Do the records always occur in four-

Do the records have any other fields that

The reason
Click to expand...
Click to expand...
 
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:
![gross]-
[table_1]![gross]" (no quotes) to the grid. Use the real
names in place of my references to
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




Not true? Also, I don't see
Click to expand...
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
 
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:
![gross]-
[table_1]![gross]" (no quotes) to the grid. Use the real
names in place of my references to
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




Not true? Also, I don't see
Click to expand...
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
.
Click to expand...
.
Click to expand...
 
Back
Top