Solved

Access 2000 - First record by Item

Posted on 2011-09-26
9
240 Views
Last Modified: 2012-05-12
Dear Experts,

Can you please check the attached small database, it contains Table1 and based on that Query1 orders its records.

Could you advise about a query which would bring the first record of Query1 for each Item?

In query "NotGood" I have tried to apply the Item to be Group By and the others as First, but in this way not correct because I would need the first whole record line by Item, not that individually what is the first in fields

thanks,
KeepOrder.zip
0
Comment
Question by:csehz
  • 5
  • 4
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
clarify this  <first record of Query1 for each Item? > what are the criteria ?

from your sample db, post the records you want to see returned as the result of the query..
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
copy and this SQL and paste in the SQL view of a new query

SELECT T.Item, T.Fixed, T.Date, T.Agreement
FROM Table1 AS T
Inner Join
(select min(T2.Date) As MinDate, T2.Item
 From Table1 as T2
 Group by T2.Item
) As T3
On T.Item=T3.Item and T.Date=T3.MinDate

is that the result yo want to see?
0
 
LVL 1

Author Comment

by:csehz
Comment Utility
Basically just keeping the ordering result of Query1

Item      Agreement      Fixed      Date
101      41332            2011-11-01
102      41332            2011-11-06
103      41339      X      2011-11-23
104            X      2011-11-01

thanks,
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
there are fields in your table that are Null, are you sure these are correct?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:csehz
Comment Utility
Thanks I have tried but that gives different result for me.

Maybe I could explain this "first record" expression through Excel, so for example if we would use formula Vlookup based on Item, that would give the first row always as result, for example =VLOOKUP(A2;Query1!A:D;4;0)
0
 
LVL 1

Author Comment

by:csehz
Comment Utility
Yes the Null values in table are correct, as the live download like this comes from SAP.

0
 
LVL 1

Author Comment

by:csehz
Comment Utility
Could you please have a short look on the attached excel file, so I would like the same result in Access which is on Sheet1 in Excel

thanks,
Query1.xlsx
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
create this make table query first, then run

SELECT Table1.Item, Table1.Agreement, Table1.Fixed, Table1.Date INTO Table2
FROM Table1
ORDER BY Table1.Item, Table1.Agreement DESC , Table1.Fixed DESC , Table1.Date DESC;


then, create this query

SELECT Table2.Item, First(Table2.Agreement) AS FirstOfAgreement, First(Table2.Fixed) AS FirstOfFixed, First(Table2.Date) AS FirstOfDate
FROM Table2
GROUP BY Table2.Item;
0
 
LVL 1

Author Closing Comment

by:csehz
Comment Utility
Thanks it works
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now