Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access query not returning expected data

Posted on 2012-03-19
23
Medium Priority
?
359 Views
Last Modified: 2012-06-27
I have this data:
SalesOrder      AmmendDate                      EnteredBy
381894          19/03/2012 11:00:01      JS  
381894          19/03/2012 11:00:02      JS  
381894          19/03/2012 11:24:56      JS  
381894          19/03/2012 11:25:02      PW  
381894          19/03/2012 11:25:02      PW  
381894          19/03/2012 14:24:57      PW  

The AmendedDate column is Date/Time

I want to return the user (EnteredBy Field) that put the order on, So I group by Order and display firstof AmmendedDate and firstof Enteredby which returns: -

SalesOrder      FirstOfAmmendDate      EnteredBy
381894          19/03/2012 14:24:57      PW  

This is incorrect as I want to find the user who entered the order which is 'JS' on 19/03/2012 11:00:01

Can anyone help?
0
Comment
Question by:HKFuey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +2
23 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37738702
SELECT m.SalesOrder, m.FirstOfAmmendDate, m.EnteredBy
FROM MyTable m INNER JOIN
(SELECT SalesOrder, MIN(FirstOfAmmendDate) FirstOfAmmendDate FROM MyTable GROUP BY SalesOrder) t ON m.SalesOrder=t.SalesOrder AND m.FirstOfAmmendDate=t.FirstOfAmmendDate
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37738734
select SalesOrder,AmmendDate, EnteredBy
from tableX
Where AmmendDate=(select min([AmmendDate]) from tableX As t where t.SalesOrder=tableX.SalesOrder)
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37738754
I usually try to avoid tables in the where clause. I used to be a big fan of them, especially with EXISTS, or IN, but the problem is that the table is constructed for each record, as opposed to being constructed once then joined.
Also, your query will repeat records (he was grouping by SalesOrder). He only wants the first for each SalesOrder. A SELECT DISTINCT will solve it but is, imho, inefficient.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37738905
@cluskitt
are you referring to the query i posted?
i suggest that you try it first before making any negative comments.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37738956
I apologize. I misread your solution. It will return the desired records as well. Sorry.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37739471
Or:

SELECT SalesOrder, EnteredBy, Min(AmendDate)
FROM MyTable
GROUP BY SalesOrder, EnteredBy;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37739477
For completeness:

SELECT SalesOrder, EnteredBy, Min(AmendDate) as FirstEntry
FROM MyTable
GROUP BY SalesOrder, EnteredBy;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37739823
that will work as long as the Group By will return single record per SalesOrder when you add more fields to the query
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37740282
It will return a single record per sales order/entered by as long as there are no duplicates in the date/time field for that combination.  BTW, we should be answering what is asked, not conjecturing about adding more fields at a later date.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37740304
it is very common that the real query have more fields than what was given as an example.

the query i posted at http:#a37738734  can handle that, even if you include all the fields from the table. i.e.,

select * from
tablex
Where AmmendDate=(select min([AmmendDate]) from tableX As t where t.SalesOrder=tableX.SalesOrder)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37740345
Slow tho
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37741171
My query will address will address the original question and is, I think, efficient. It can work with however many fields you want to use.
0
 

Author Comment

by:HKFuey
ID: 37741372
Thanks for all the input.

I tried suggestion 37738734 by Capricorn1: -

SELECT MyTable.SalesOrder, MyTable.Operator, MyTable.AmmendDate
FROM MyTable
WHERE (((MyTable.SalesOrder)="99999") AND ((MyTable.AmmendDate)=(select min([AmmendDate]) from MyTable As t )));

Not sure my syntax is right as this returns nothing, now trying first response from Cluskit.
0
 

Author Comment

by:HKFuey
ID: 37741655
Suggestion from Cluskit gives "can't represent the join expression m.SalesOrder=t.SalesOrder in design view: -

SELECT m.SalesOrder, m.FirstOfAmmendDate, m.EnteredBy
FROM MyTable m INNER JOIN
(SELECT SalesOrder, MIN(FirstOfAmmendDate) FirstOfAmmendDate FROM MyTable GROUP BY SalesOrder) t ON m.SalesOrder=t.SalesOrder AND m.FirstOfAmmendDate=t.FirstOfAmmendDate
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37741662
Don't use design view. Use the SQL view and paste it there.
0
 

Author Comment

by:HKFuey
ID: 37741666
OK I have done this now and it works, not very elegant though: -

Select Order and Min of date, save as Query1

Add new Query2 and join Min of date and order to fields in my table
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37741683
It's pretty much the same. What you have to do is create a temporary table (or view), then link it with the actual table. Whether you do it on Access or SQL, with a JOIN or in the WHERE clause, a temporary table is always required.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37741687
test this one


SELECT MyTable.SalesOrder, MyTable.Operator, MyTable.AmmendDate
FROM MyTable
WHERE (((MyTable.SalesOrder)="99999") AND ((MyTable.AmmendDate)=(select min([AmmendDate]) from MyTable As t where t.SalesOrder=MyTable.SalesOrder)));
0
 

Author Comment

by:HKFuey
ID: 37741821
Access still giving errors, thinking of going to a SQL view and starting a new question will give points later.

SELECT     SalesOrder, Operator, AmmendDate
FROM         dbo.MyTable
WHERE     (SalesOrder = '999999') AND (AmmendDate =
                          (SELECT     MIN(dbo.MyTable.AmmendDate) AS Expr1
                            FROM          dbo.MyTable AS t
                           Where      (dbo.MyTable.SalesOrder = dbo.MyTable.SalesOrder)))
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 900 total points
ID: 37741838
As long as you create a view with:
SELECT SalesOrder So, MIN(FirstOfAmmendDate) Dt FROM MyTable GROUP BY SalesOrder
You can then create a second view that includes both the table and the first view. Join them both and you have your results. You can do this in design mode, if you want.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 600 total points
ID: 37742251
SELECT     SalesOrder, Operator, AmmendDate
FROM         dbo.MyTable
WHERE     (SalesOrder = '999999') AND (AmmendDate =
                          (SELECT     MIN(dbo.MyTable.AmmendDate) AS Expr1
                            FROM          dbo.MyTable AS t
                           Where      (t.SalesOrder = dbo.MyTable.SalesOrder)))
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37742540
Tags:access 2007 sql 2000
Topics: Microsoft Access Database, MS SQL Server

You posted this in the MS SQL Server topic area with a "sql 2000" tag, were you looking for T-SQL solution?
0
 

Author Closing Comment

by:HKFuey
ID: 37742698
Capricorn1, I tried your final suggestion and I get error "An aggregate may not appear in the Where clause...

Thanks for trying to help, I need to go do some work now!!

In the end I just created 2 views as I suggested earlier in the thread.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

609 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