duplicate data issue in access 2003

Question

1.00/5 (2 votes)

See more:

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

XML

TicketNumber ShippingContainer  Weight
   SRN1         2-0002-01        56
   SRN2         2-0002-01        56

                         TOTAL WT:  112

But it should be like this...

XML

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

:)

Posted 2-Jan-13 21:58pm

Ariel Riyo

Updated 3-Jan-13 18:21pm

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Answer 1 · 2013-01-03T15:46:00

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.

Posted 3-Jan-13 15:46pm

Christian Graus

Updated 3-Jan-13 18:28pm

v2

Comments

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!