Query Issues

[hudaz]

Hi Everyone,

I'm basically having issues with a query and in turn a list box displaying duplicate records and I've tried everything within my knowledge to make it work but I'm at a loss as to what else i can try at present.

I am trying to run a query that shows the job number once as well as the project title and i am trying to pull information from another table but only show the most recent record related to the job number.

It might help if i give you an example of my tables at this point because it might help you guys understand what I'm trying to achieve.

TBL_Bottle

Job Number Project Title
1111 Big Bottle
2222 Round Bottle

TBL_Notes

Job Number Notes Issued
1111 Spec sent 17/12/2013
1111 Modification 12/12/2013
2222 Spec required 15/12/2013
2222 Speak to customer 10/12/2013


So the result i would be hoping to get from a query would be:

Job Number Project Title Notes
1111 Big Bottle Spec Sent
2222 Round Bottle Spec Required

So the query filters out the most recent notes made about that specific job number as well as only displaying the job number once i.e no duplicates

Is this possible ?

Or could someone suggest a better way of doing this as i'm stumped at the minute ?

Thanks in advance for your help

 

[namliam]

make a query like:

Select JobNumber, Max(issued) as MaxIssues 
from TBL_Notes

And join that to your TBL_Notes and TBL_Bottle, should make it unique unless there are 2 notes on the same day.

 

[Mihail]

Run Query2

 

[hudaz]

Thanks Mihail & namliam,

This seems to be exactly what I needed. I'm going to try it on my data this afternoon and i'll let you know how it goes. Thanks again for your quick replies

Hugely appreciated.

 

[jdraw]

Hudaz,

Hopefully Mihail and namliam have helped solve your immediate problem. When you post, the readers only know what you tell us. It helps put the issue into context if you give an overview of your business and business rules. The readers have no idea of what your database is about.

Good luck.

 

[Mihail]

Hudaz,

Hopefully Mihail and namliam have helped solve your immediate problem. When you post, the readers only know what you tell us. It helps put the issue into context if you give an overview of your business and business rules. The readers have no idea of what your database is about.

Good luck.

Indeed.
And I suspect a very wrong setup.