asked on Access query not returning expected data
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?
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?
Microsoft AccessMicrosoft SQL Server
Last Comment
HKFuey
select SalesOrder,AmmendDate, EnteredBy
from tableX
Where AmmendDate=(select min([AmmendDate]) from tableX As t where t.SalesOrder=tableX.SalesO
from tableX
Where AmmendDate=(select min([AmmendDate]) from tableX As t where t.SalesOrder=tableX.SalesO
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.
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.
@cluskitt
are you referring to the query i posted?
i suggest that you try it first before making any negative comments.
are you referring to the query i posted?
i suggest that you try it first before making any negative comments.
I apologize. I misread your solution. It will return the desired records as well. Sorry.
Or:
SELECT SalesOrder, EnteredBy, Min(AmendDate)
FROM MyTable
GROUP BY SalesOrder, EnteredBy;
SELECT SalesOrder, EnteredBy, Min(AmendDate)
FROM MyTable
GROUP BY SalesOrder, EnteredBy;
For completeness:
SELECT SalesOrder, EnteredBy, Min(AmendDate) as FirstEntry
FROM MyTable
GROUP BY SalesOrder, EnteredBy;
SELECT SalesOrder, EnteredBy, Min(AmendDate) as FirstEntry
FROM MyTable
GROUP BY SalesOrder, EnteredBy;
that will work as long as the Group By will return single record per SalesOrder when you add more fields to the query
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.
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.SalesO
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.SalesO
Slow tho
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.
ASKER
Thanks for all the input.
I tried suggestion 37738734 by Capricorn1: -
SELECT MyTable.SalesOrder, MyTable.Operator, MyTable.AmmendDate
FROM MyTable
WHERE (((MyTable.SalesOrder)="99
Not sure my syntax is right as this returns nothing, now trying first response from Cluskit.
I tried suggestion 37738734 by Capricorn1: -
SELECT MyTable.SalesOrder, MyTable.Operator, MyTable.AmmendDate
FROM MyTable
WHERE (((MyTable.SalesOrder)="99
Not sure my syntax is right as this returns nothing, now trying first response from Cluskit.
ASKER
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.Firs
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.Firs
Don't use design view. Use the SQL view and paste it there.
ASKER
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
Select Order and Min of date, save as Query1
Add new Query2 and join Min of date and order to fields in my table
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.
test this one
SELECT MyTable.SalesOrder, MyTable.Operator, MyTable.AmmendDate
FROM MyTable
WHERE (((MyTable.SalesOrder)="99
SELECT MyTable.SalesOrder, MyTable.Operator, MyTable.AmmendDate
FROM MyTable
WHERE (((MyTable.SalesOrder)="99
ASKER
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
FROM dbo.MyTable AS t
Where (dbo.MyTable.SalesOrder = dbo.MyTable.SalesOrder)))
SELECT SalesOrder, Operator, AmmendDate
FROM dbo.MyTable
WHERE (SalesOrder = '999999') AND (AmmendDate =
(SELECT MIN(dbo.MyTable.AmmendDate
FROM dbo.MyTable AS t
Where (dbo.MyTable.SalesOrder = dbo.MyTable.SalesOrder)))
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
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?
ASKER
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.
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.
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.Firs