Select records only on the same dates (1 Viewer)

AlexTeslin

Registered User.
Local time
Today, 07:41
Joined
Jul 10, 2008
Messages
55
Hi,

I am trying to pull records from tables which have the same dates within a field in a table. For example if a table has following records:

Field_1 Field_2
ref1 date1
ref2 date2
ref3 date3
ref1 date2

The query should output only 2 records (ref2, date2) and (ref1, date2), because both dates are the same even though the ref are different.

My question is how can I pull only those 2 records so that somehow it can loop within the date field?

Thank you in advance
 

khawar

AWF VIP
Local time
Today, 18:41
Joined
Oct 28, 2006
Messages
870
What if its like this

Field_1 Field_2
ref1 date1
ref2 date2
ref3 date3
ref1 date2
ref3 date3

i-e there are more than one dates that are entered in table more than once
 

khawar

AWF VIP
Local time
Today, 18:41
Joined
Oct 28, 2006
Messages
870
For your current scenerio this is the Sql

Code:
SELECT YourTable.Field1, YourTable.Field2
FROM YourTable
WHERE (((YourTable.Field2)=(SELECT YourTable.Field2
FROM YourTable
GROUP BY YourTable.Field2
HAVING (((Count(YourTable.Field2))>1)))))

Replace with your table and field names
 

AlexTeslin

Registered User.
Local time
Today, 07:41
Joined
Jul 10, 2008
Messages
55
Sorry for a delay Khawar and thank you for your reply.
I have tried to run similar code given above, but produces some error messages. Only when I use Group By and Having on the same query layer, I am getting only one instance of it, which is expected.

SELECT MyTable.field1, MyTable.field2
FROM MyTable
GROUP MyTable.field2, MyTable.field1
HAVING (((Count(MyTable.field1))>6));

Is there a way to do it?

thanks,
 

AlexTeslin

Registered User.
Local time
Today, 07:41
Joined
Jul 10, 2008
Messages
55
In fact the error message I am getting is this:

Subquery returned more than 1 value. This is not possible when subquery followes with =, !=, <...

thanks
 

AlexTeslin

Registered User.
Local time
Today, 07:41
Joined
Jul 10, 2008
Messages
55
I have managed to get the results on date with more than 6 occurences for the particular date, for example. But because I have used Group By, Having and Count I can't show all the columns. But if I do, then I have to use them in Group by, which generates different results.

I have tried create another query which uses this query linked with another table. But when I try to link some fields I am getting wrong results (instead of 100 I would get 10000, for example). Can anyone help me with this please?

Thank you
 

khawar

AWF VIP
Local time
Today, 18:41
Joined
Oct 28, 2006
Messages
870
Use this statement

SELECT YourTable.Field1, YourTable.Field2
FROM [SELECT YourTable.Field2
FROM YourTable
GROUP BY YourTable.Field2
HAVING (((Count(YourTable.Field2))>1))
]. AS CountDate INNER JOIN YourTable ON CountDate.Field2 = YourTable.Field2;
 

AlexTeslin

Registered User.
Local time
Today, 07:41
Joined
Jul 10, 2008
Messages
55
Thank you for your code - it works fine. The only thing is I didn't make myself clear. When it generates the date more than 1 it should generate more than on field1 too. I think this is was your first question asked.

I have tried all the possibilities but no luck yet. If you could help me with that as well that will be great.

Thank you in advance
 

AlexTeslin

Registered User.
Local time
Today, 07:41
Joined
Jul 10, 2008
Messages
55
Hi Dallr,
this one although is part of my query, it is achieving to get different results. I thought will be easier to break down the problem and put them together by myself or leave them as separate queries.

Sorry for a confusion
 

Users who are viewing this thread

Top Bottom