|
| 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
February 7th, 2007, 10:26 PM
posted to microsoft.public.access.queries
|
|||
|
|||
zero a field in duplicate rows
Hi,
I have a table with duplicate rows, which is fine, that's what i need to display. i have the same field 'balance' in all those rows. My question is; how can i zero the 'balance' field after the first row, only the first row should show an $$ value in that field, all duplicates should be zeroed out. Regards, Manny 
|
|
#2
February 7th, 2007, 10:58 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
zero a field in duplicate rows
Manny wrote:
Hi, I have a table with duplicate rows, which is fine, that's what i need to display. i have the same field 'balance' in all those rows. My question is; how can i zero the 'balance' field after the first row, only the first row should show an $$ value in that field, all duplicates should be zeroed out. Regards, Manny Hi Manny, Hmm, Just a thought... I question why you want to do this? Are you adding up the balance, e.g. in a query someplace, and do not want to count the duplicate entries? Perhaps there is another way to solve the underlying problem rather than modifying the table data. -- Smartin
|
|
#3
February 7th, 2007, 11:45 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
zero a field in duplicate rows
Hi Smartin,
It is correct, i need to sum the balance. Here is what i have. i need to export the table to excel, where i have a pivot table extracting the data. Obviously the data is incorrect because it is adding all duplicates, that in fact they are not duplicates...they are the detail of invoices.... Regards, "Smartin" wrote: Manny wrote: Hi, I have a table with duplicate rows, which is fine, that's what i need to display. i have the same field 'balance' in all those rows. My question is; how can i zero the 'balance' field after the first row, only the first row should show an $$ value in that field, all duplicates should be zeroed out. Regards, Manny Hi Manny, Hmm, Just a thought... I question why you want to do this? Are you adding up the balance, e.g. in a query someplace, and do not want to count the duplicate entries? Perhaps there is another way to solve the underlying problem rather than modifying the table data. -- Smartin
|
|
#4
February 8th, 2007, 12:05 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
zero a field in duplicate rows
If you do not need to see all the duplicate detail rows then perhaps
create a query to SELECT DISTINCT rows, and base your export on that instead. If the Balance field has the same value across what you consider to be duplicate rows, this is a one step process. Manny wrote: Hi Smartin, It is correct, i need to sum the balance. Here is what i have. i need to export the table to excel, where i have a pivot table extracting the data. Obviously the data is incorrect because it is adding all duplicates, that in fact they are not duplicates...they are the detail of invoices.... Regards, "Smartin" wrote: Manny wrote: Hi, I have a table with duplicate rows, which is fine, that's what i need to display. i have the same field 'balance' in all those rows. My question is; how can i zero the 'balance' field after the first row, only the first row should show an $$ value in that field, all duplicates should be zeroed out. Regards, Manny Hi Manny, Hmm, Just a thought... I question why you want to do this? Are you adding up the balance, e.g. in a query someplace, and do not want to count the duplicate entries? Perhaps there is another way to solve the underlying problem rather than modifying the table data. -- Smartin -- Smartin
|
|
#5
February 8th, 2007, 12:51 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
zero a field in duplicate rows
Smartin,
That is the thing....i need to see all detail records when i drill down... Regards, "Smartin" wrote: If you do not need to see all the duplicate detail rows then perhaps create a query to SELECT DISTINCT rows, and base your export on that instead. If the Balance field has the same value across what you consider to be duplicate rows, this is a one step process. Manny wrote: Hi Smartin, It is correct, i need to sum the balance. Here is what i have. i need to export the table to excel, where i have a pivot table extracting the data. Obviously the data is incorrect because it is adding all duplicates, that in fact they are not duplicates...they are the detail of invoices.... Regards, "Smartin" wrote: Manny wrote: Hi, I have a table with duplicate rows, which is fine, that's what i need to display. i have the same field 'balance' in all those rows. My question is; how can i zero the 'balance' field after the first row, only the first row should show an $$ value in that field, all duplicates should be zeroed out. Regards, Manny Hi Manny, Hmm, Just a thought... I question why you want to do this? Are you adding up the balance, e.g. in a query someplace, and do not want to count the duplicate entries? Perhaps there is another way to solve the underlying problem rather than modifying the table data. -- Smartin -- Smartin
|
|
#6
February 8th, 2007, 10:58 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
zero a field in duplicate rows
Alrighty then... sounds like I am back to square one! Unless someone
else has an idea I guess we are looking at zeroing out all but one record in each set of duplicates, just like you said in the first place (^: There are quite a few posts on the topic of deleting duplicate rows and, optionally, preserving one row with duplicate sets. Here's a relatively recent one (courtesy of John Spencer): http://preview.tinyurl.com/296hkz You can leverage this technique. In your case obviously you want to UPDATE and not DELETE. The adjustment should be pretty easy to make. FWIW I still don't like the idea of modifying the table data, although your mileage may vary. One way to avoid this would be to create a SELECT query that uses the two-query technique in the linked article, and include a conditional field (ala IIf) to check whether the current record is in the "keep" list. If it is, use the original balance amount. If not, substitute zero. Again, base your export on the query. Hope this all helps! Sorry I dragged you all the way around the block only to get back to the starting point... Manny wrote: Smartin, That is the thing....i need to see all detail records when i drill down... Regards, "Smartin" wrote: If you do not need to see all the duplicate detail rows then perhaps create a query to SELECT DISTINCT rows, and base your export on that instead. If the Balance field has the same value across what you consider to be duplicate rows, this is a one step process. Manny wrote: Hi Smartin, It is correct, i need to sum the balance. Here is what i have. i need to export the table to excel, where i have a pivot table extracting the data. Obviously the data is incorrect because it is adding all duplicates, that in fact they are not duplicates...they are the detail of invoices.... Regards, "Smartin" wrote: Manny wrote: Hi, I have a table with duplicate rows, which is fine, that's what i need to display. i have the same field 'balance' in all those rows. My question is; how can i zero the 'balance' field after the first row, only the first row should show an $$ value in that field, all duplicates should be zeroed out. Regards, Manny Hi Manny, Hmm, Just a thought... I question why you want to do this? Are you adding up the balance, e.g. in a query someplace, and do not want to count the duplicate entries? Perhaps there is another way to solve the underlying problem rather than modifying the table data. -- Smartin -- Smartin -- Smartin
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
