?
Solved

Ignore the first match

Posted on 2011-10-24
16
Medium Priority
?
211 Views
Last Modified: 2012-05-12
I am trying to create a MS access SQL statement that compares values from the same table that ignores the first row returned.

For example for each order there may be a query call raised. The first query call is acceptable so I want to ignore that. How can I do this using SQL.

If I was using Crystal I would probably use the PREVIOUS statement to evaluate this. Can I do anything similar in SQL?

The example date I am getting is:

Incident_Number      Call_Date      Order No
CALL1748900      01-Mar-11      ORD7444
CALL2166899      08-Jul-11      ORD7444
CALL1931108      26-Apr-11      ORD7575
CALL1934264      27-Apr-11      ORD7575
CALL1971397      10-May-11      ORD7685
CALL2080797      15-Jun-11      ORD7685
CALL2080905      15-Jun-11      ORD7685
CALL2089690      17-Jun-11      ORD7685
CALL2089769      17-Jun-11      ORD7685
CALL1676199      07-Feb-11      ORD8344
CALL1678344      08-Feb-11      ORD8344
CALL1696802      14-Feb-11      ORD8344
CALL1698086      14-Feb-11      ORD8344
CALL1747866      01-Mar-11      ORD8344

So for the data shown I am only interested in CALL2166899 and as such only want this data returned by my SQL statement.
0
Comment
Question by:JayceW
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37018002
What is determining the sequence of the records?

So you are saying out of all the records you listed you only want to get the second one?
You will probbaly need a Top 1 query but the sequencing field is crucial.
 
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37018037
I agree with Peter, although I'm still trying to figure out what makes Call 2166899 for ORD7444 any different than Call 1934264 for ORD7575.

I can sort of get that where there are more than 2 records for a particular Order#, you don't want to include any of those, but don't get how you would determine to only return the 2166899 record?
0
 

Author Comment

by:JayceW
ID: 37018042
Hi Peter57,

The reason for the requirement is to perform counts. So from the example set of data I would want to count 1 query for order ORD7444, 1 for ORD7575, 4 for ORD7685 and 4 for ORD8344. Also I may want to cound the number of queries for a given date. for the 15-Jun-11 I would count 1 but for the 17-JUN-11 I would count 2
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 101

Expert Comment

by:mlmcc
ID: 37018073
Given your set of data do you want to see any after the first one or just the last one?

mlmcc
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37018090
Sorry to be so blunt but you have to decide what you are trying to do.
You are pushing out 'maybe's' and that gets nobody anywhere.

You will need different queries to count things in different ways.
0
 

Author Comment

by:JayceW
ID: 37018147
OK mlmcc sorry about that.

Peter57r,

Not pushing out maybe's, just trying to populate a table with relationships between two tables, excluding the first relationship found. In Crystal Reports there are "PREVIOUS" and "NEXT" commands that would enable this but I was hoping not to have to use another tool on top. Do you not think that this is possible?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37018519
Are you trying to add records to a new table or are you trying to create a report showing the records that have multiple calls?

mlmcc
0
 

Author Comment

by:JayceW
ID: 37022756
step one is that I want to create a table that only contains the duplicate call records that I am interested in. Hence my requirement to ignore the first relationship found.

Thanks
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 2000 total points
ID: 37022792
Starting with the sample data you posted originally, please indicate what you expect your result to be.
And describe what rule(s) you used to get your result.
0
 

Author Comment

by:JayceW
ID: 37023333
Hi Peter57r,

Thanks for your continued involvement.

For the sample data shown I would want to import all call per order number, apart from the one with the lowest call date into a table. So for that data I would have a table populated as below.


Incident_Number      Call_Date      Order No
CALL2166899      08-Jul-11      ORD7444
CALL1934264      27-Apr-11      ORD7575
CALL2080797      15-Jun-11      ORD7685
CALL2080905      15-Jun-11      ORD7685
CALL2089690      17-Jun-11      ORD7685
CALL2089769      17-Jun-11      ORD7685
CALL1678344      08-Feb-11      ORD8344
CALL1696802      14-Feb-11      ORD8344
CALL1698086      14-Feb-11      ORD8344
CALL1747866      01-Mar-11      ORD8344


0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 2000 total points
ID: 37023802
Is the first record for each OrderNo ALWAYS the only one for that date (for that order) or can there be more than one record with the same earliest date for the same order?
0
 

Author Comment

by:JayceW
ID: 37023961
Yes the first record is the only one for that date
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 2000 total points
ID: 37024092
Try this query...

SELECT T.[Incident_number], T.[Order No], T.[Call_Date]
FROM Orders AS T LEFT JOIN
(Select [Order No], min([Call_Date]) as mdate from orders
group by [Order No])  AS q
ON (T.[Call_Date] = q.mdate) AND (T.[Order No] = q.[Order No])
WHERE (((q.[Order No]) Is Null))
ORDER BY T.[Order No], T.[Call_Date];
0
 

Author Comment

by:JayceW
ID: 37024155
Thanks Peter57r

When this came through I had already started to execute a similar NOT IN SQL statement.

Would you think that your Left join would be more efficient?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 37024240
Couldn't say for sure but I expect so.
However, testing both  is the best bet if performance matters..
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question