about duplicated items

[beanbeanbean]

hi guys, i kind of have a problem.


currently i have a query which goes like this:

SELECT test1.[Opty Id], test1.[Opty Name], SJ_CIS.NEWCINSFX, test1.[Contact CIN], test1.[Contact CIN SFX], test1.[Create By (Name)], test1.[Created By (Login)], test1.[Opty Created Date], SJ_CIS.JOIN_DATE, test1.[Opty Closed Date], test1.[Closed By (Emp #)], test1.[Referral - Employee #], test1.[Referral - Employee], test1.[Sales Rep], test1.Product

FROM test1 INNER JOIN SJ_CIS ON [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]

WHERE (((test1.[Sales Rep Position]) Like '*INTL*')
AND (CDate([test1].[Opty Created Date]) < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < CDate([test1].[Opty Closed Date])));


what this query does is that it combines 2 tables together thus enabling me to compare the creation date, join date and the close date using a common field which is the NEWSINSFX.


now what i want the query to pull out is all the CIN numbers which have the requirements

Creation Date < Join Date < Close Date.

This code was working well. But i've encountered a problem ! And would like to request help.


its seems like the data base has got duplicates ! this means the data in the table can look like this

NEWCINSFX | CREATION | JOIN | CLOSED

D2934338E | 2/10/2008 | 5/11/2008 | 3/12/2008 (correct)
D2934338E | 22/10/2008 | 24/10/2008 | 22/10/2008 (wrong)

as you can see there is the exact same NEWCINSFX but the entry for the dates are different.

since my criteria for the query is Creation < Join < Close it should pull this NEWCINSFX out as it meets the requirements but because of the wrong dates of the duplicate it could not pull the NEWCINSFX out. how do i bypass this problem ?

also there is another problem,

NEWCINSFX | CREATION | JOIN | CLOSED

D2934338E | 2/10/2008 | 5/11/2008 | 3/12/2008 (correct)
D2934338E | 15/10/2008 | 20/11/2008 | 1/12/2008 (correct)

i also have some duplicates which have correct dates in both of them. i would like to pull the earlier date. this means i want to pull the one with 2/10/2008 creation date out. how do i do that ?



thanks in advanced. god bless

 

[namliam]

NEWCINSFX | CREATION | JOIN | CLOSED

D2934338E | 2/10/2008 | 5/11/2008 | 3/12/2008 (correct)
D2934338E | 22/10/2008 | 24/10/2008 | 22/10/2008 (wrong)

as you can see there is the exact same NEWCINSFX but the entry for the dates are different.

since my criteria for the query is Creation < Join < Close it should pull this NEWCINSFX out as it meets the requirements but because of the wrong dates of the duplicate it could not pull the NEWCINSFX out. how do i bypass this problem ?

As far as I can tell the second (wrong) record should not be there because of the Creation < Join < Closed

NEWCINSFX | CREATION | JOIN | CLOSED

D2934338E | 2/10/2008 | 5/11/2008 | 3/12/2008 (correct)
D2934338E | 15/10/2008 | 20/11/2008 | 1/12/2008 (correct)

i also have some duplicates which have correct dates in both of them. i would like to pull the earlier date. this means i want to pull the one with 2/10/2008 creation date out. how do i do that ?

Make a group by query to get the first date.

Select NEWCINFS, max(CREATION) as maxCreation
from yourtable
Group by NEWCINFS

Now join this query into your excisting query to only pull the first date(s). This should also eliminate problem #1

 

[beanbeanbean]

Make a group by query to get the first date.

Select NEWCINFS, max(CREATION) as maxCreation
from yourtable
Group by NEWCINFS

Now join this query into your excisting query to only pull the first date(s). This should also eliminate problem #1

hi namliam. first and foremost thanks for replying. but i jus realized i dun not need to pull out the NEWCINFS. i just need to pull the creation date out and the NEWCINFS will come out.

Would u teach me how to implement the group by function into my current code ?

its for problem 2.


SELECT test1.[Opty Id], test1.[Opty Name], SJ_CIS.NEWCINSFX, test1.[Contact CIN], test1.[Contact CIN SFX], test1.[Create By (Name)], test1.[Created By (Login)], test1.[Opty Created Date], SJ_CIS.JOIN_DATE, test1.[Opty Closed Date], test1.[Closed By (Emp #)], test1.[Referral - Employee #], test1.[Referral - Employee], test1.[Sales Rep], test1.Product

FROM test1 INNER JOIN SJ_CIS ON [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]

WHERE (((test1.[Sales Rep Position]) Like '*INTL*')
AND (CDate([test1].[Opty Created Date]) < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < CDate([test1].[Opty Closed Date])));


i tried adding AND GROUP BY ([test1].[Opty Created Date])); but somehow it would let me.

 

[namliam]

What do you want to sum/average/count??

Group by <insert all fields that you do not do sum/avg/count on>

 

[beanbeanbean]

I'm sorry. i dun quite get u. i'm quite a noob. but i dont think i need to use any sum/average/count function i guess.


basically what i want to do is:

NEWCINSFX | CREATION | JOIN | CLOSED

D2934338E | 2/10/2008 | 5/11/2008 | 3/12/2008 (correct)
D2934338E | 15/10/2008 | 20/11/2008 | 1/12/2008 (correct)

i also have some duplicates which have correct dates in both of them. i would like to pull the earlier date. this means i want to pull the one with 2/10/2008 creation date out. how do i do that ?



i would like to only grab the entry with the date 2/10/2008 instead of making the query grab both of them thus making me have a duplicate in the table that i extracted.

SELECT test1.[Opty Id], test1.[Opty Name], SJ_CIS.NEWCINSFX, test1.[Contact CIN], test1.[Contact CIN SFX], test1.[Create By (Name)], test1.[Created By (Login)], test1.[Opty Created Date], SJ_CIS.JOIN_DATE, test1.[Opty Closed Date], test1.[Closed By (Emp #)], test1.[Referral - Employee #], test1.[Referral - Employee], test1.[Sales Rep], test1.Product

FROM test1 INNER JOIN SJ_CIS ON [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]

WHERE (((test1.[Sales Rep Position]) Like '*INTL*')
AND (CDate([test1].[Opty Created Date]) < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < CDate([test1].[Opty Closed Date])));


my current query is like this. i do not understand how to add in the group by function thing that u are talking about. so sorry..

 

[namliam]

Select NEWCINFS, max(CREATION) as maxCreation
from yourtable
Group by NEWCINFS

Ah that is what you are on about.

Make above query, save it in your database.

Now make/add an "Inner join" to your query

SELECT test1.[Opty Id], test1.[Opty Name], SJ_CIS.NEWCINSFX, test1.[Contact CIN], test1.[Contact CIN SFX], test1.[Create By (Name)], test1.[Created By (Login)], test1.[Opty Created Date], SJ_CIS.JOIN_DATE, test1.[Opty Closed Date], test1.[Closed By (Emp #)], test1.[Referral - Employee #], test1.[Referral - Employee], test1.[Sales Rep], test1.Product

FROM test1 INNER JOIN SJ_CIS ON [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]
[B]INNER JOIN [U][NEW_QUERY][/U] on NEWCINSFX = NEWCINSFX
                      and CreationDate = MaxCreationdate[/B]
WHERE (((test1.[Sales Rep Position]) Like '*INTL*')
AND (CDate([test1].[Opty Created Date]) < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < CDate([test1].[Opty Closed Date])));

Semi-psuedo code, I hope you understand what I mean.

 

[beanbeanbean]

Ah that is what you are on about.

Make above query, save it in your database.

Now make/add an "Inner join" to your query

SELECT test1.[Opty Id], test1.[Opty Name], SJ_CIS.NEWCINSFX, test1.[Contact CIN], test1.[Contact CIN SFX], test1.[Create By (Name)], test1.[Created By (Login)], test1.[Opty Created Date], SJ_CIS.JOIN_DATE, test1.[Opty Closed Date], test1.[Closed By (Emp #)], test1.[Referral - Employee #], test1.[Referral - Employee], test1.[Sales Rep], test1.Product
 
FROM test1 INNER JOIN SJ_CIS ON [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]
[B]INNER JOIN [U][NEW_QUERY][/U] on NEWCINSFX = NEWCINSFX[/B]
[B]                 and CreationDate = MaxCreationdate[/B]
WHERE (((test1.[Sales Rep Position]) Like '*INTL*')
AND (CDate([test1].[Opty Created Date]) < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < CDate([test1].[Opty Closed Date])));

Semi-psuedo code, I hope you understand what I mean.

hi namliam,

i tried using the code but it does not seem to work.

first i did this.

SELECT test1.[Contact CIN], test1.[Opty Created Date]
FROM test1
GROUP BY test1.[Contact CIN];

when i tried double clicking on the query it said, " You tried to execute a query that does not include the specified expression 'Opty Created Date' as part of an aggregate function " i then saved it as Query1

i didnt know how to solve the problem so i went on and

did this


SELECT test1.[Opty Id], test1.[Opty Name], SJ_CIS.NEWCINSFX, test1.[Contact CIN], test1.[Contact CIN SFX], test1.[Create By (Name)], test1.[Created By (Login)], test1.[Opty Created Date], SJ_CIS.JOIN_DATE, test1.[Opty Closed Date], test1.[Closed By (Emp #)], test1.[Referral - Employee #], test1.[Referral - Employee], test1.[Sales Rep], test1.Product

FROM test1 INNER JOIN SJ_CIS ON [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]
INNER JOIN [QUERY1] on test1.[Contact CIN] = test1.[Contact CIN]
and test1.[Opty Created Date] = test1.[Opty Created Date]
WHERE (((test1.[Sales Rep Position]) Like '*INTL*')
AND (CDate([test1].[Opty Created Date]) < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < CDate([test1].[Opty Closed Date])));


i tried saving the query too but it seems to say that i syntax error (missing operator) and sometimes it says syntax error in JOIN Operation. is the join error coming out because of the join command which is this FROM test1 INNER JOIN SJ_CIS ON [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX] ?


i cant seem to solve it. and how does the max function work ? it seems to change my field name which i do not want.



also i tried adding the group by function to my normal code and there seems to be syntax errors too.


i'm so sorry i cant post the database up here. its too big.



so sorry for the trouble. thanks. god bless.

 

[beanbeanbean]

hey namliam i solved this problem by making them into 2 seperate queries. so its kinda done unless someone wants to teach me how to merge them into one. =D thanks to the great ideas of yours =)

 

[namliam]

SELECT test1.[Contact CIN], Max(test1.[Opty Created Date])
FROM test1
GROUP BY test1.[Contact CIN];

Guess you solved it though... Doing it in 2 steps/queries is the easiest way of doing it... Dont worry about "cleanlyness" in doing everything in one query, makes for maintanenance hazards anyway.

 

[beanbeanbean]

thanks for your help =) i kinda have a stupid thinking of having everything inside one query.

 

[namliam]

Not stupid, overly ambitious maybe...

Trust me... I have had to make a query on time that printed was a 4 pager.... without enters in there.... Side to side and top to bottom... The customer wanted to have it in 1 query...
NIGHTMARE!

Baby steps, and I love (to) KISS.... will save your life every time!

 

[beanbeanbean]

wow that sounds tough.

was wondering can u help me with another duplicate problem ?


let me explain.

my current query draws out at least 25 fields of data.

so sometimes the person entering the data might be too tired and enter the same data twice but a single field different.

for example :

the first data looks like this:

Contact CIN | ID | Name | Created by (name) | Created by (login id)
S1234567D | 1-5E8A2K| 1-32640 | Ang Kim Mui | BCHKLOI

the second data looks like this:
Contact CIN | ID | Name | Created by (name) | Created by (login id)
S1234567D | 1-8FLRNS| 1-32640 | Ang Kim Mui | BCHKLOI


the third data looks like this:
Contact CIN | ID | Name | Created by (name) | Created by (login id)
S1234567D | 1-5E8A2K| 1-42456 | Ang Kim Mui | BCHKLOI




basically what i'm using to search the data is the Contact CIN. Thus when i search for the Contact CIN S1234567D. All this 3 data will come out. Is there a possible way just to make the query just display one of the 3 records instead of all 3 ?

It does not matter which record is displayed

 

[namliam]

You can do a group by ... and select the max() for all other fields

 

[beanbeanbean]

erm can u do an example with my code ? so sorry haha. i kinda need visual aid.


SELECT DISTINCT Query2.[Opty Id], Query2.[Opty Name], SJ_CIS.NEWCINSFX, Query2.[Contact CIN], Query2.[Contact CIN SFX], Query2.[Create By (Name)], Query2.[Created By (Login)], Query2.[Opty Created Date], SJ_CIS.JOIN_DATE, Query2.[Opty Closed Date], Query2.[Closed By (Emp #)], Query2.[Referral - Employee #], Query2.[Referral - Employee], Query2.[Sales Rep], Query2.Product

FROM Query2 INNER JOIN SJ_CIS ON [Query2].[Contact CIN] & [Query2].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]

WHERE ((CDate([Query2].[Opty Created Date]) < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < CDate([Query2].[Opty Closed Date])));

 

[namliam]

In query design, find the Sigma (group by sign) in the menu bar (just about center usually). Click it.

This adds a new line on your design grid. This line now everything says "Group by"
Change all, except your CIN to any of the values but "Expression" or "Group by"
In this case Max/Min/Last/First one of these four probably.

I hope it is visual enough?

 

[beanbeanbean]

oh thanks man ! i solved it already. thank you.