If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Repost: Calculation problem. Someone help!
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 |
#2
|
|||
|
|||
Repost: Calculation problem. Someone help!
-----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/AwUBQRKESoechKqOuFEgEQLfbwCgiMtKdkljRfCbFFxMZQh9rW H9bvoAn3jc 3KGwp7etfdK/BsCUCEk2EiZG =NCEF -----END PGP SIGNATURE----- Victor wrote: 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 .... |
#3
|
|||
|
|||
Repost: Calculation problem. Someone help!
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 . |
#4
|
|||
|
|||
Repost: Calculation problem. Someone help!
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 . |
#5
|
|||
|
|||
Repost: Calculation problem. Someone help!
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! -----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 . . |
#6
|
|||
|
|||
Repost: Calculation problem. Someone help!
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 . . |
#7
|
|||
|
|||
Repost: Calculation problem. Someone help!
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 -----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 . . . |
#8
|
|||
|
|||
Repost: Calculation problem. Someone help!
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 . . . |
#9
|
|||
|
|||
Repost: Calculation problem. Someone help!
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 "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 . . |
#10
|
|||
|
|||
Repost: Calculation problem. Someone help!
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 . . |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange Problem Occured Using C#&Access | Xian Xu | General Discussion | 5 | June 30th, 2004 10:12 PM |
PGP problem | Bob Henson | General Discussion | 0 | June 27th, 2004 11:28 AM |
problem with an error | evil_one | General Discussion | 7 | June 17th, 2004 09:27 PM |
If logic problem | Jeff Smith | Worksheet Functions | 4 | May 13th, 2004 12:48 AM |
Is it possible to plot 3 surfaces in a Pivot Chart? (Repost) | Alison | Charts and Charting | 5 | October 21st, 2003 11:00 PM |