Click here to Skip to main content
15,073,507 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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...

Updated 3-Jan-13 18:21pm

1 solution

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.
Ariel Riyo 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 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 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 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 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 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 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 4-Jan-13 1:23am
I suggest talking to your teacher at this point.
Ariel Riyo 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 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 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 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 7-Jan-13 19:14pm
but if you just want to insult other person thats not really a great hobby. :)
Christian Graus 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 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 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 7-Jan-13 20:13pm
coz im still working on this. tried in vba. access criteria. hidden fields. and still.
Christian Graus 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 7-Jan-13 20:38pm
She doesnt know also what to do. not pressuring me and giving me time.
Christian Graus 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 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)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900