last 5 entries (1 Viewer)

[shafh]

getting last 5 entries from a table

Hi,
I have to create a query that will get the 'last 5' transaction entries for 'each' company in a table. The
table consists of about 100 companies, each with anywhere from 1 to 10 or so transactions. Any ideas on how to proceed. I tried the Multiple Entry Query Wizard but that didn't really help.

My table consists of 5 fields: ID, UpdateDate, CompanyName, AnalystName and TransactionDetail.
We have about 100 different companies and everytime there is a transaction for a particular company the transactions detail and analyst name and a company name and update date are entered into the database. I would like to create a query (and am missing the Criteria) that will return the UpdateDate, CompanyName, AnalystName and TransactionDetail info for the last 5 transactions for each company.
Thanks,
Sam

 

[dcx693]

If you sort your query in descending order of UpdateDate, you can use an TOP values query to return the 5 most recent transactions.

 

[shafh]

Thanks dcx693.
However I needed the last 5 transactions for each company. So if there were 100 companies then the query would show 500 transaction details.
Using the SELECT TOP 5 function alone will just give me the most recent 5 entries in the database.

 

[Rich]

Are you outputing the results to a form/report?

 

[shafh]

I'm trying to write a query that will eventually be used in a report. However I haven't got my query to work yet.

I have this so far but the syntax is off and it doesn't work.

SELECT ParentRecommendationDetail.RecommendationDetail, ParentRecommendationDetail.ParentName
FROM ParentRecommendationDetail As A
WHERE (((ParentRecommendationDetail.ParentName&ParentRecommendationDetail.RecommendationDetail In (Select TOP 5 [ParentRecommendationDetail.ParentName&ParentRecommendationDetail.RecommendationDetail] FROM ParentRecommendationDetail As B WHERE A.RecommendationDetail = B.RecommendationDetail ORDER By ParentName,RecommendationDetail))));

 

[Rich]

Use a subReport and set the criteria for the keyID on the main report, you will then get the top 5 for each company

 

[Jon K]

To do it with a query, you can use the UpdateDate field and Count(*) to get the last 5 records for each company as shown in the attached DB, which contains the query:

SELECT a.*
FROM tblDetail AS a
WHERE (Select Count(*) from tblDetail where CompanyName=a.CompanyName and UpdateDate >= a.UpdateDate) between 1 and 5
ORDER BY a.CompanyName, a.UpdateDate;


If more than one record may be added to the table in one day for a company, you may use the ID field instead of the UpdateDate field if the ID is in ascending order such as an autonumber.

 

[shafh]

Thanks Jon. That worked.
I'm afraid I have to bore you people once again though.
I used that query in my report, however since Company 1's transaction details are on 3 different lines the report shows them as 3 separate pages. Is there any way that I can have the query results such that the report includes all three transactions one after another on the same page rather then treating them as 3 separate entries for Company 1.
Thanks

 

[Jon K]

Make sure the format of Force New Page in the Detail Section is set to None.

You can use the Report Wizard to create the report. When you are asked whether you want to add any grouping level, select CompanyName.

In the DB, I have included a report created using the Report Wizard.

 

[reel knotty]

Shafh thanks for the question because I am working on something very similar and Jon K thanks for the replies but I'm affraid I need a bit more help...

I copied your SQL statement and when I run it I get:

"The Microsoft Jet database engine cannot find the input table or query 'Shipment_Table'. Make sure it exists and that its name is spelled correctly."



here is what I have in the SQL:

SELECT a.*
FROM Shipment_Table AS a
WHERE (Select Count(*) from Shipment_Table where Trailer=a.trailer and Appointment_Date >= a.Appointment_Date) between 1 and 5
ORDER BY a.Trailer, a.Appointment_Date;


As always any and all help is greatly appreciated!
Nate

 

[Jon K]

I created a table Shipment_Table with the fields Trailer and Appointment_Date and found that your SQL statement ran fine.

Have you checked the spelling of the table name?

 

[reel knotty]

Thats what I did first. Is there a setting somewhere I am missing?

As far as I can see everything is spelled correctly and _ in for the spaces.

 

[reel knotty]

OK I got it to work. I went back and changed the original table name to take out the space and that took care of it. Now....

It is pulling the last 5 entries for each trailer. I would like it to just pull the last entry. What do I put in to replace the Between 1 and 5??

Sorry for the simpleness. This is my first time with SQL.
Nate

 

[Jon K]

Replace Between 1 and 5 with =1

 

[shafh]

add another field

Hi,
I wanted to add another field to my query using the code below. The new field is another set of "Transaction Detail2" from another table. How can I add the new field to the existing code below from another table which has the same fields (as my original table) except for the one I want to add to my query.
Thanks

To do it with a query, you can use the UpdateDate field and Count(*) to get the last 5 records for each company as shown in the attached DB, which contains the query:

SELECT a.*
FROM tblDetail AS a
WHERE (Select Count(*) from tblDetail where CompanyName=a.CompanyName and UpdateDate >= a.UpdateDate) between 1 and 5
ORDER BY a.CompanyName, a.UpdateDate;


If more than one record may be added to the table in one day for a company, you may use the ID field instead of the UpdateDate field if the ID is in ascending order such as an autonumber.

 

[reel knotty]

Duh !!

Thanks Jon!

 

[1starr]

This is exactly what I need but I keep getting a "syntax error in query expression" . This is how I wrote it: (Select Count(*) from [CERT June] where OS =[Catagory IIs].os and [Sum of Catergory] >=[Catagory IIs].[sum of categroy])

 

[shafh]

new field

I added a new field using show table and then creating a inner join in the Design View. However the problem is that every company's listing is repeated thrice. There is only one unique entry on the second table for "Transaction Detail2" so I'm wondering why everything is repeating thrice.
For every UpdateDate for a CompanyName I'm getting the exact same thing thrice.

 

[reel knotty]

Starr for starters double check your spelling...

(Select Count(*) from [CERT June] where OS =[Catagory IIs].os and [Sum of Catergory] >=[Catagory IIs].[sum of categroy ])

Also take a look at your spaces:

(Select Count(*) from [CERT_June] where OS =[Catagory_IIs].os and [Sum_of_Catergory] >=[Catagory_IIs].[sum_of_categroy])

I'm am surely no expert but that is what popped out at me.

 

[1starr]

I can elimate the spaces for everything except Cert June which is the table name. If I change the name I would have to go in and change all the queries, forms and other reports in the database