[zhouma]

Ok...I have a query as the record source for a form that I want to show the last record entered and all the data associated with the last record entered.

So for example...I have the following companies

Company A
Company B
Company C
Company D

Each has a manager or person in our company assigned to them and have projects open at different times...what I want the query to output is the following

Field 1 - Record 1 - Company A's Name
Filed 2 - Record 1 - Person attached to Company A
Field 3 - Record 1 - Last date that information was entered for Company A
Field 4 - Record 1 - Last stage Company A's project is at
Field 5 - Record 1- Last person who worked on a project with Company A or for Company A

Field 1 - Record 2 - Company B
Filed 2 - Record 2 - Person attached to Company B
Field 3 - Record 2 - Last date that information was entered for Company B
Field 4 - Record 2 - Last stage Company B's project is at
Field 5 - Record 2- Last person who worked on a project with Company B or for Company B

And so on and so forth. Just to note...the Person attached to Company A, Company B, etc. is in one table and the info in Field 1, 3, 4, and 5 is in another table. So I'm thinking the best way to connect the two is a query. The info that connects the two is the company name being the same in both tables.

I've tried using the last and first in the totals for the QBE...getting all kinds of doubles and wrong data. I guess this may be b/c a lot of the dates for the last date are identical. I tried changing the date from Date() to Now() but then the last sort for the QB only shows the 2nd to last record...not the last record. The only thing that has worked for the totals in the QBE is the Max for the date besides that everything else won't filter down to only the information associated with that particular DateofMax.

So any help is greatly appreciated.

Thanks.

Best,
Zhouma

Btw...I'm using Access 2007

[OBP]

http://forums.techguy.org/business-a...uery-last.html

You need to do that, it may take 3 queries to get the Last dates for the 3 items. You may also need to join the tables & queries in the finla query by more than one field.

[zhouma]

Hi OBP,

Thanks for your response.

All I have to say is Whoa Nellie!...it took me 5 queries to get the result I wanted and needed. First thing I did was change the date from Date() to Now() which helped a lot and cut down on a lot of doubles as I suspected it would. So it was quite easy to get the first query done which consisted of CompanyName (group by) and DateGiven (Max) in the QBE. So let's call that qryDateCN. The second and third queries were a query of qryDateCN and one of the stages I wanted to narrow down. Query 2 joined the particular stage the project was at w/ qryDateCN. Query 3 joined qryDateCN w/ the person working on that particular stage.Then query 4 joined queries 2 and 3. The fifth query added in another field that I needed from a table which was the company's account manager.

So thanks for pointing me in the right direction.

Best,
Zhouma

[OBP]

Well, done, I though it would take at least 3.