Access query on date

[malay63]

Sir,
I want to run a query on a set of records like this
Suppose ABC Co shows following sale records in first statement:
ID NO Name Sale Amt Tax Amt log date
100 abc 1000 10 12/09/2011
101 def 5000 50 12/09/2011
102 pqr 7000 70 12/09/2011

In 2nd statement the sale is:
101 abc 1000 10 20/09/2011
102 def 7000 70 20/09/2011
104 xyz 8000 80 20/09/2011

In 3rd statement the sale is:
101 def 4000 40 30/09/2011
102 pqr 7000 70 30/09/2011
104 xyz 9000 90 30/09/2011

All data are in the same table. I want to extract the set of data relating to the last date i.e 30/09/2011 only. The table contains suppose n number of companies having many such sets. The query should generate data for n companies corresponding to the last for each company. Pl. help.

 

[MSAccessRookie]

Sir,
I want to run a query on a set of records like this
Suppose ABC Co shows following sale records in first statement:
ID NO Name Sale Amt Tax Amt log date
100 abc 1000 10 12/09/2011
101 def 5000 50 12/09/2011
102 pqr 7000 70 12/09/2011

In 2nd statement the sale is:
101 abc 1000 10 20/09/2011
102 def 7000 70 20/09/2011
104 xyz 8000 80 20/09/2011

In 3rd statement the sale is:
101 def 4000 40 30/09/2011
102 pqr 7000 70 30/09/2011
104 xyz 9000 90 30/09/2011

All data are in the same table. I want to extract the set of data relating to the last date i.e 30/09/2011 only. The table contains suppose n number of companies having many such sets. The query should generate data for n companies corresponding to the last for each company. Pl. help.

In order to select rows with the most recent Date, consider looking into the MAX() or TOP() functions. You can then get back to us if you have any questions.

MAX()

http://office.microsoft.com/en-us/access-help/min-max-functions-HP001032255.aspx

TOP()

http://support.microsoft.com/kb/147724

 

[student]

I think looking at the VBA example of the TOP function on that link could be a little over-complicated tbh. I just write it straight in SQL view of query builder for simple queries like this. Example below of one I used recently. Bear in mind that the data must be sorted by a field to get the correct result

SELECT TOP 5 accidents_2011.AccPlace, accidents_2011.Witness1Name, Sum(accidents_2011.no_accidents) AS SumOfno_accidents
FROM accidents_2011
GROUP BY accidents_2011.AccPlace, accidents_2011.Witness1Name
ORDER BY Sum(accidents_2011.no_accidents) DESC;

Note the ORDER BY clause in the last line.