Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi guys!
 
Good Day...
 
I have a problem in my access 2003. I have a duplicate data in my table. The 2nd column needs to be zero out when it has duplicate in 1st column. Tody i have this..
 
TicketNumber ShippingContainer  Weight
   SRN1         2-0002-01        56
   SRN2         2-0002-01        56
 
                         TOTAL WT:  112
 

 
But it should be like this...
 
TicketNumber ShippingContainer  Weight
   SRN1         2-0002-01        56
   SRN2         2-0002-01             
 
                         TOTAL WT:  56
 

The ticket number is unique and the shipping_container_number is the same. I need criteria or something so the wieght will not be repeatative as you can see in the illustration above. The shipment of a container will be shipped to different places thats why it has different ticket_number but still i need them to display all like that.
 
Is this possible in access. please help. I really need this to work for my report. Thanks in advance... More Powe and God Bless...
 
Smile | :)
Posted 2-Jan-13 21:58pm
Edited 3-Jan-13 18:21pm
v4

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I am not sure how possible this is, in the format you're asking for. You can get totals by selecting distinct values from shipping container. It's the total weight you're worried about, so I suspect you want to just calculate it separately, doing a distinct select that does not include the ticket number. I could tell you how to write SQL to do that but I have no clue how to do it by not programming in Access.
  Permalink  
v2
Comments
Ariel Riyo at 3-Jan-13 22:44pm
   
first of all this makes sense.. second my point here is selecting the same data but then if the data on col1 is selected then the second data dont need to display again the column two. please comment first before posting in the solution area if you cant hlp at all.
Christian Graus at 3-Jan-13 22:47pm
   
Here's a tip. If it doesn't make sense to me, the person you want to do your job, then it doesn't matter if it makes sense to you. Why do you want to select SRN1 twice, but with 56 not present the second time ? If you just wanted to select no duplicates, DISTINCT would do it. As it stands, it still makes no sense.
Ariel Riyo at 3-Jan-13 23:11pm
   
i just made it simpler. actually i have another identifier i have 2 container but different ticket id. so itll be 2 different ticket and same container because they have different shipment and so they have same weight so the col2 here is the weight i dont want to make it appear twice because i need the sum in the report. i hope you can get me.
Christian Graus at 3-Jan-13 23:50pm
   
No, you've still not told us enough so we can help you. We need to know the SQL you're running, the table structure, and exactly what you're looking for, and why. One possibility I can see, is a count statenemtn on col1 and a group by, so you only get one row, but a 2 if SRN1 appeared twice.
Ariel Riyo at 4-Jan-13 0:11am
   
maybe ill revise the question in a bit. im just dragging the items in the access so there is n paticular codes/criteria at this moment.
Christian Graus at 4-Jan-13 0:25am
   
The problem with not knowing how to write code is, you get stuck pretty quick trying to drag and drop stuff.
Ariel Riyo at 4-Jan-13 1:17am
   
i know something about coding but there nothing to code if just need the data like that. now i need to compose something to work with this problem. i tried count, if. and i made new query with the sql statment to group by each ticket number so it wont be repeattive. but i tried connecting it to the main query and tried left/right join but still ambiguous so im stucked. i also revise some of my macro and still got stock with this problem. either count and compre in the criteria doesnt work...
Christian Graus at 4-Jan-13 1:23am
   
I suggest talking to your teacher at this point.
Ariel Riyo at 4-Jan-13 1:36am
   
ahahahaahahahhahahahahahhaha. dont request and say this dont makes sense if you are like that. Thanks for the conversation btw.
Christian Graus at 4-Jan-13 1:45am
   
Surely you're in a class, you're not being paid to do stuff you have no clue about ?
Ariel Riyo at 4-Jan-13 1:49am
   
im not a student anymore. sorry if im like this for a while.. im solving this case for about 2 weeks. tried many things but still.
Christian Graus at 4-Jan-13 13:56pm
   
You will always be a student if you want to be a programmer, but clearly you should still be in a class. Either way, you should buy a book and work through it, not spend two weeks trawling the web for someone else to solve your issue, or for it to solve itself at random.
Ariel Riyo at 7-Jan-13 19:14pm
   
but if you just want to insult other person thats not really a great hobby. :)
Christian Graus at 7-Jan-13 19:20pm
   
I'm trying to help, not insult. We can spoon feed you forever, but it won't help you in the long run.
Ariel Riyo at 7-Jan-13 20:07pm
   
dude your 1st ever post is classified as a insult why dont you ask first my point. btw this convo will get me to nowhere.. /end
Christian Graus at 7-Jan-13 20:10pm
   
*sigh* you're the one who has a job they can't do. So, your ability to earn a wage is apparently dependant on my good will. I don't think you have room to complain. Your question initially made no sense. When you fixed it, I stuck with you because I'd posted an answer. I'm not sure why you're upset.
Ariel Riyo at 7-Jan-13 20:13pm
   
coz im still working on this. tried in vba. access criteria. hidden fields. and still.
Christian Graus at 7-Jan-13 20:16pm
   
Well, that clearly does not bode well for you. Do you have a boss giving you this task ? If so, you should talk to him, he'll be furious if he finds out you got nothing done for days without asking.
Ariel Riyo at 7-Jan-13 20:38pm
   
She doesnt know also what to do. not pressuring me and giving me time.
Christian Graus at 7-Jan-13 20:52pm
   
OK - is there a client at the end of all this ? So you have a job, you don't know how to do it, and your boss has no clue, either ?
Ariel Riyo at 8-Jan-13 3:05am
   
yep. we can do this manually. for now we are doing it manually. if this will be automated itll be just perfect!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 397
1 OriginalGriff 350
2 thatraja 205
3 Emre Ataseven 165
4 Shai Vashdi 150
0 Sergey Alexandrovich Kryukov 8,894
1 OriginalGriff 5,200
2 Peter Leow 3,955
3 Maciej Los 3,535
4 Abhinav S 3,208


Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 4 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid