query involving dates

C

C White

Hi

I am having problems with running a query that does the following

there are 5 fields in a table that the query is based on, the first four
are simple enough and all that happens is that the fields need to match,
for that I was able to do the following to get records out of the
database (does that make sense ;) )

In (SELECT [SERIAL_NUM] FROM
As Tmp GROUP BY
[SERIAL_NUM],[SR_SUB_AREA],[DETAIL],[SUB_DETAIL] HAVING Count(*)>1 And
[SR_SUB_AREA] =
.[SR_SUB_AREA] And [DETAIL] =
.[DETAIL]
And [SUB_DETAIL] =
.[SUB_DETAIL])

The fifth field is a date field that is formatted as follows:
"11/08/2004 10:06:04 PM"

what I am having problem with is getting the query to pull the records
that are within 2 days of eachother.

Simply put the query need to pull out the records where the first four
fields match and they were created within 2 days of eachother.

I'm probably using the wrong terms when I try to search for an
answer/tutorial on how to this as I have found nothing to date.

Any help, would be appreciated.

Thanks
 
A

Allen Browne

It may be simpler to join the table to itself on the 4 fields, and then use
criteria to limit the output:

1. Create a query into this table.

2. Add a 2nd copy of the table. Access will alias it with a "_1" suffix,
e.g. Table_1.

3. Drag each of the 4 fields from the original table in turn onto the
matching field in the duplicate table. You will end up with 4 join lines
between the 2 tables, matching field to field.

4. In the Field row of the output grid, enter:
Abs(
.[EntryDate] - [Table_1].[EntryDate])
and in the Criteria row beneath this field:
<= 2
That limits the query to records created within 2 days of each other.

5. Because a record is not a duplicate of itself, drag the primary key field
from Table_1 into the grid, and in the Criteria row enter:
<>
.[ID]
 
C

C White

Thanks for your suggestion, unfortunately it did not work as it failed
to filter out any of the records, I must add that I do not use access, I
am only doing this as there was no-one else.

what I have right now is this:

1. some, info, here, 10/11/2004
2. some, info, here, 10/12/2004
3. more, info, here, 08/12/2004
4. more, info, here, 09/02/2004

what I need the query to do is only display records 1 and 2 as they fall
within the date range
 
A

Allen Browne

Okay, so you have a query that is not giving the desired results.
Open it in SQL View (View menu, from query design).
Copy what you see, and paste the SQL statement here so we can see what's
happening.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C White said:
Thanks for your suggestion, unfortunately it did not work as it failed to
filter out any of the records, I must add that I do not use access, I am
only doing this as there was no-one else.

what I have right now is this:

1. some, info, here, 10/11/2004
2. some, info, here, 10/12/2004
3. more, info, here, 08/12/2004
4. more, info, here, 09/02/2004

what I need the query to do is only display records 1 and 2 as they fall
within the date range

Allen said:
It may be simpler to join the table to itself on the 4 fields, and then
use criteria to limit the output:

1. Create a query into this table.

2. Add a 2nd copy of the table. Access will alias it with a "_1" suffix,
e.g. Table_1.

3. Drag each of the 4 fields from the original table in turn onto the
matching field in the duplicate table. You will end up with 4 join lines
between the 2 tables, matching field to field.

4. In the Field row of the output grid, enter:
Abs(
.[EntryDate] - [Table_1].[EntryDate])
and in the Criteria row beneath this field:
<= 2
That limits the query to records created within 2 days of each other.

5. Because a record is not a duplicate of itself, drag the primary key
field from Table_1 into the grid, and in the Criteria row enter:
<>
.[ID]
Click to expand...
Click to expand...
 
C

C White

Allen said:
Okay, so you have a query that is not giving the desired results.
Open it in SQL View (View menu, from query design).
Copy what you see, and paste the SQL statement here so we can see what's
happening.
Click to expand...
Thanks for the quick reply, do you mean my original query, or the one I
created with your assistance
 
C

C White

Allen said:
The one you just created as a result of this thread.
Click to expand...
here you go:

this is the query based on your suggestion, there are nearly 500,000
records in the table and the query returns an equal number of results

SELECT
Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]) AS
Expr1
FROM [SR_0804-1104_12:00] INNER JOIN [SR_0804-1104_12:00_Copy] ON
([SR_0804-1104_12:00].[ROW_ID]=[SR_0804-1104_12:00_Copy].[ROW_ID]) AND
([SR_0804-1104_12:00].[SERIAL_NUM]=[SR_0804-1104_12:00_Copy].[SERIAL_NUM])
AND
([SR_0804-1104_12:00].[SR_SUB_AREA]=[SR_0804-1104_12:00_Copy].[SR_SUB_AREA])
AND ([SR_0804-1104_12:00].[DETAIL]=[SR_0804-1104_12:00_Copy].[DETAIL])
AND
([SR_0804-1104_12:00].[SUB_DETAIL]=[SR_0804-1104_12:00_Copy].[SUB_DETAIL])
WHERE
(((Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]))<=2));


here is what I was using before and it returned only 50,000 results:

SELECT [SR_0804-1104_12:00].[SERIAL_NUM],
[SR_0804-1104_12:00].[SR_SUB_AREA], [SR_0804-1104_12:00].[DETAIL],
[SR_0804-1104_12:00].[SUB_DETAIL], [SR_0804-1104_12:00].[OPENED],
[SR_0804-1104_12:00].[CLOSED_DATE],
DateDiff("d",[OPENED],[SR_0804-1104_12:00].[OPENED]) AS Expr1
FROM [SR_0804-1104_12:00]
WHERE ((([SR_0804-1104_12:00].SERIAL_NUM) In (SELECT [SERIAL_NUM] FROM
[SR_0804-1104_12:00] As Tmp GROUP BY
[SERIAL_NUM],[SR_SUB_AREA],[DETAIL],[SUB_DETAIL] HAVING Count(*)=2 And
[SR_SUB_AREA] = [SR_0804-1104_12:00].[SR_SUB_AREA] And [DETAIL] =
[SR_0804-1104_12:00].[DETAIL] And [SUB_DETAIL] =
[SR_0804-1104_12:00].[SUB_DETAIL])) AND
((DateDiff("d",[OPENED],[SR_0804-1104_12:00].[OPENED]))<=2))
ORDER BY [SR_0804-1104_12:00].[SERIAL_NUM],
[SR_0804-1104_12:00].[SR_SUB_AREA], [SR_0804-1104_12:00].[DETAIL],
[SR_0804-1104_12:00].[SUB_DETAIL], [SR_0804-1104_12:00].[OPENED];

i've been working at this for nearly a week now and I can't seem to find
the right answer to my problem, I'm going to have to start sorting
through the records manually pretty soon if I can't get this thing to work

thanks
 
A

Allen Browne

That query does not seem to work at all here, as the table alias is missing.

You also omitted the criteria that stops each record from considering itself
to be a duplicate. To do that you need something unique, such as a primary
key field named ID.

Try something like this:

SELECT [SR_0804-1104_12:00].ID,
Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]) AS
Expr1
FROM [SR_0804-1104_12:00]
INNER JOIN [SR_0804-1104_12:00] AS [SR_0804-1104_12:00_Copy]
ON ([SR_0804-1104_12:00].ROW_ID = [SR_0804-1104_12:00_Copy].ROW_ID)
AND ([SR_0804-1104_12:00].SERIAL_NUM =
[SR_0804-1104_12:00_Copy].SERIAL_NUM)
AND ([SR_0804-1104_12:00].SR_SUB_AREA =
[SR_0804-1104_12:00_Copy].SR_SUB_AREA)
AND ([SR_0804-1104_12:00].DETAIL = [SR_0804-1104_12:00_Copy].DETAIL)
AND ([SR_0804-1104_12:00].SUB_DETAIL =
[SR_0804-1104_12:00_Copy].SUB_DETAIL)
WHERE ((([SR_0804-1104_12:00].ID)<>[SR_0804-1104_12:00_Copy].[ID])
AND
((Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]))<2));


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
C White said:
Allen said:
The one you just created as a result of this thread.
Click to expand...
here you go:

this is the query based on your suggestion, there are nearly 500,000
records in the table and the query returns an equal number of results

SELECT
Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]) AS
Expr1
FROM [SR_0804-1104_12:00] INNER JOIN [SR_0804-1104_12:00_Copy] ON
([SR_0804-1104_12:00].[ROW_ID]=[SR_0804-1104_12:00_Copy].[ROW_ID]) AND
([SR_0804-1104_12:00].[SERIAL_NUM]=[SR_0804-1104_12:00_Copy].[SERIAL_NUM])
AND
([SR_0804-1104_12:00].[SR_SUB_AREA]=[SR_0804-1104_12:00_Copy].[SR_SUB_AREA])
AND ([SR_0804-1104_12:00].[DETAIL]=[SR_0804-1104_12:00_Copy].[DETAIL]) AND
([SR_0804-1104_12:00].[SUB_DETAIL]=[SR_0804-1104_12:00_Copy].[SUB_DETAIL])
WHERE
(((Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]))<=2));


here is what I was using before and it returned only 50,000 results:

SELECT [SR_0804-1104_12:00].[SERIAL_NUM],
[SR_0804-1104_12:00].[SR_SUB_AREA], [SR_0804-1104_12:00].[DETAIL],
[SR_0804-1104_12:00].[SUB_DETAIL], [SR_0804-1104_12:00].[OPENED],
[SR_0804-1104_12:00].[CLOSED_DATE],
DateDiff("d",[OPENED],[SR_0804-1104_12:00].[OPENED]) AS Expr1
FROM [SR_0804-1104_12:00]
WHERE ((([SR_0804-1104_12:00].SERIAL_NUM) In (SELECT [SERIAL_NUM] FROM
[SR_0804-1104_12:00] As Tmp GROUP BY
[SERIAL_NUM],[SR_SUB_AREA],[DETAIL],[SUB_DETAIL] HAVING Count(*)=2 And
[SR_SUB_AREA] = [SR_0804-1104_12:00].[SR_SUB_AREA] And [DETAIL] =
[SR_0804-1104_12:00].[DETAIL] And [SUB_DETAIL] =
[SR_0804-1104_12:00].[SUB_DETAIL])) AND
((DateDiff("d",[OPENED],[SR_0804-1104_12:00].[OPENED]))<=2))
ORDER BY [SR_0804-1104_12:00].[SERIAL_NUM],
[SR_0804-1104_12:00].[SR_SUB_AREA], [SR_0804-1104_12:00].[DETAIL],
[SR_0804-1104_12:00].[SUB_DETAIL], [SR_0804-1104_12:00].[OPENED];

i've been working at this for nearly a week now and I can't seem to find
the right answer to my problem, I'm going to have to start sorting through
the records manually pretty soon if I can't get this thing to work

thanks
Click to expand...
 
C

C White

Allen said:
That query does not seem to work at all here, as the table alias is missing.

You also omitted the criteria that stops each record from considering itself
to be a duplicate. To do that you need something unique, such as a primary
key field named ID.

Try something like this:

SELECT [SR_0804-1104_12:00].ID,
Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]) AS
Expr1
FROM [SR_0804-1104_12:00]
INNER JOIN [SR_0804-1104_12:00] AS [SR_0804-1104_12:00_Copy]
ON ([SR_0804-1104_12:00].ROW_ID = [SR_0804-1104_12:00_Copy].ROW_ID)
AND ([SR_0804-1104_12:00].SERIAL_NUM =
[SR_0804-1104_12:00_Copy].SERIAL_NUM)
AND ([SR_0804-1104_12:00].SR_SUB_AREA =
[SR_0804-1104_12:00_Copy].SR_SUB_AREA)
AND ([SR_0804-1104_12:00].DETAIL = [SR_0804-1104_12:00_Copy].DETAIL)
AND ([SR_0804-1104_12:00].SUB_DETAIL =
[SR_0804-1104_12:00_Copy].SUB_DETAIL)
WHERE ((([SR_0804-1104_12:00].ID)<>[SR_0804-1104_12:00_Copy].[ID])
AND
((Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]))<2));
Click to expand...
Thanks again, but this time it returns no values as I don't know what to
enter when it asks me for a paramater value for each table's id field

Enter Parameter Value
SR_0804-1104_12:00.ID

and then it asks

Enter Parameter Value
SR_0804-1104_12:00_Copy.ID

what am I missing here?
 
A

Allen Browne

Yes, you appear to be missing the primary key field - named ID in the
example.

You need something that uniquely identifies a record, so that each record is
not returned as a duplicate of itself.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C White said:
Allen said:
That query does not seem to work at all here, as the table alias is
missing.

You also omitted the criteria that stops each record from considering
itself to be a duplicate. To do that you need something unique, such as a
primary key field named ID.

Try something like this:

SELECT [SR_0804-1104_12:00].ID,
Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED])
AS Expr1
FROM [SR_0804-1104_12:00]
INNER JOIN [SR_0804-1104_12:00] AS [SR_0804-1104_12:00_Copy]
ON ([SR_0804-1104_12:00].ROW_ID = [SR_0804-1104_12:00_Copy].ROW_ID)
AND ([SR_0804-1104_12:00].SERIAL_NUM =
[SR_0804-1104_12:00_Copy].SERIAL_NUM)
AND ([SR_0804-1104_12:00].SR_SUB_AREA =
[SR_0804-1104_12:00_Copy].SR_SUB_AREA)
AND ([SR_0804-1104_12:00].DETAIL = [SR_0804-1104_12:00_Copy].DETAIL)
AND ([SR_0804-1104_12:00].SUB_DETAIL =
[SR_0804-1104_12:00_Copy].SUB_DETAIL)
WHERE ((([SR_0804-1104_12:00].ID)<>[SR_0804-1104_12:00_Copy].[ID])
AND
((Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]))<2));
Click to expand...
Thanks again, but this time it returns no values as I don't know what to
enter when it asks me for a paramater value for each table's id field

Enter Parameter Value
SR_0804-1104_12:00.ID

and then it asks

Enter Parameter Value
SR_0804-1104_12:00_Copy.ID

what am I missing here?
Click to expand...
 
C

C White

Allen said:
Yes, you appear to be missing the primary key field - named ID in the
example.

You need something that uniquely identifies a record, so that each record is
not returned as a duplicate of itself.
Click to expand...
OK then

the field ROW_ID is the unique field that identifies the recors, so do I
change the first line "SELECT [SR_0804-1104_12:00].ID," to read "SELECT
[SR_0804-1104_12:00].[ROW_ID]," and does the last line become
[SR_0804-1104_12:00].[ROW_ID])<>[SR_0804-1104_12:00_Copy].[ROW_ID]

or am I totally off base here, as mentioned before I don't really know
what I'm doing here, yet they managed to stick me with it despite my
warnings
 
A

Allen Browne

Okay, if Row_ID is the primary key, then you do not want to include it in
the JOIN. Instead, it is part of the WHERE clause:

SELECT [SR_0804-1104_12:00_Copy].ID,
Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]) AS
Expr1
FROM [SR_0804-1104_12:00] INNER JOIN [SR_0804-1104_12:00] AS
[SR_0804-1104_12:00_Copy]
ON ([SR_0804-1104_12:00].SUB_DETAIL = [SR_0804-1104_12:00_Copy].SUB_DETAIL)
AND ([SR_0804-1104_12:00].DETAIL = [SR_0804-1104_12:00_Copy].DETAIL)
AND ([SR_0804-1104_12:00].SR_SUB_AREA =
[SR_0804-1104_12:00_Copy].SR_SUB_AREA)
AND ([SR_0804-1104_12:00].SERIAL_NUM =
[SR_0804-1104_12:00_Copy].SERIAL_NUM)
WHERE ((([SR_0804-1104_12:00_Copy].ID)<>[SR_0804-1104_12:00].[ROW_ID])
AND
((Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]))<=2));

By way of explanation, go back to the original reply to your question:
Step 3 said to join on the 4 fields that could be duplicates.
Step 5 said to set criteria on the primary key field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C White said:
Allen said:
Yes, you appear to be missing the primary key field - named ID in the
example.

You need something that uniquely identifies a record, so that each record
is not returned as a duplicate of itself.
Click to expand...
OK then

the field ROW_ID is the unique field that identifies the recors, so do I
change the first line "SELECT [SR_0804-1104_12:00].ID," to read "SELECT
[SR_0804-1104_12:00].[ROW_ID]," and does the last line become
[SR_0804-1104_12:00].[ROW_ID])<>[SR_0804-1104_12:00_Copy].[ROW_ID]

or am I totally off base here, as mentioned before I don't really know
what I'm doing here, yet they managed to stick me with it despite my
warnings
Click to expand...
 
C

C White

Allen said:
Okay, if Row_ID is the primary key, then you do not want to include it in
the JOIN. Instead, it is part of the WHERE clause:

SELECT [SR_0804-1104_12:00_Copy].ID,
Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]) AS
Expr1
FROM [SR_0804-1104_12:00] INNER JOIN [SR_0804-1104_12:00] AS
[SR_0804-1104_12:00_Copy]
ON ([SR_0804-1104_12:00].SUB_DETAIL = [SR_0804-1104_12:00_Copy].SUB_DETAIL)
AND ([SR_0804-1104_12:00].DETAIL = [SR_0804-1104_12:00_Copy].DETAIL)
AND ([SR_0804-1104_12:00].SR_SUB_AREA =
[SR_0804-1104_12:00_Copy].SR_SUB_AREA)
AND ([SR_0804-1104_12:00].SERIAL_NUM =
[SR_0804-1104_12:00_Copy].SERIAL_NUM)
WHERE ((([SR_0804-1104_12:00_Copy].ID)<>[SR_0804-1104_12:00].[ROW_ID])
AND
((Abs([SR_0804-1104_12:00].[OPENED]-[SR_0804-1104_12:00_Copy].[OPENED]))<=2));

By way of explanation, go back to the original reply to your question:
Step 3 said to join on the 4 fields that could be duplicates.
Step 5 said to set criteria on the primary key field.
Click to expand...
thanks for your patience, it finally worked after I changed:

WHERE ((([SR_0804-1104_12:00_Copy].ID)<>[SR_0804-1104_12:00].ID)

to read:

WHERE ((([SR_0804-1104_12:00_Copy].[ROW_ID])<>[SR_0804-1104_12:00].[ROW_ID])

I was able to get the desired result, it stopped asking me for input
values and it whittled the 500,000 records down to 30,000 and a quick
glance at the records show that they meet all of the criteria

Salad mentioned in another group that I posted to:
"Allen Browne has been answering your question in
microsoft.public.access.queries. Allen is one sharp dude, so you must
be missing something in his responses."

and he was correct in his assertion, you are a sharp a sharp dude and I
was indeed missing something

I would like to thank you very much for your assistance in this matter,
and I would like to send you a small gift to show my appreciation, I am
sure you are aware that you have saved me many hours of manual labour

could you please email me at "cwhite at theatomicmoose dot ca" so that I
may find a way to properly thank you for your assistance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

QBF & Query Malfunction 0
query to show where no data in a stated column 0
Update Query 10
Query to select values after a certain date 0
query on date 1
can't find the right query 0
Query the same table three times 0
Querying Dates 5

Top