Filter

[sturner333]

I am trying to create a report on projects. Each report number has update notes . I would like to only include the note with the newest date with any given project number. The report is generated from a query. I am not sure how to filter out for just one. note. The way the report displays right now(wrongly) is multiple instances of a project number for each note associted with it.
Thanks

 

[lespaul]

what is the query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[sturner333]

The query involves 2 tables, linked by a common id number.
The query includes all projects based on a filter for the engineer. Then all notes that go with those projects. I would like to add a last step that only showed the last note entered for that project.

 

[lespaul]

Basically, what you need to do is create a correlated sub query that has only the information you need and join into that.

If you would like some assistance in completing that, post the SQL of the query (switch from the query design grid to the SQL view).


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[sturner333]

I am goin to do this insections since it won't let me send at 1 time:


SELECT Notes.NoteDate, Sheet2.ID, Sheet2.Year, Sheet2.Market, Sheet2.ProjectNumber, Sheet2.Engr, Sheet2.PltEngr, Sheet2.Cust, Sheet2.Application, Sheet2.Model, Sheet2.ProjectDescription, Sheet2.AnnalUnits, Sheet2.EstSystemPrice, Sheet2.AnnualSales, Sheet2.ProductionDate2, Sheet2.ProjectStatus, Sheet2.ProjectClosed

 

[sturner333]

2nd part:

FROM Sheet2 INNER JOIN Notes ON Sheet2.ID = Notes.ProjectID

WHERE (((Sheet2.Engr)=[Forms]![Admin Menu]![Engineer]) AND ((Sheet2.ProjectClosed)=[Forms]![Admin Menu]![Geo] Or (Sheet2.ProjectClosed)=[Forms]![Admin Menu]![Piv])) OR (((Sheet2.PltEngr)=[Forms]![Admin Menu]![Engineer]) AND ((Sheet2.ProjectClosed)=[Forms]![Admin Menu]![Geo] Or (Sheet2.ProjectClosed)=[Forms]![Admin Menu]![Piv]))

ORDER BY Sheet2.Year, Sheet2.ProjectNumber;

 

[lespaul]

I don't see the field that actually contains the note, just the notedate, but this may do what you need:

[tt]
SELECT B.MaxNoteDate, Sheet2.ID, Sheet2.Year, Sheet2.Market, Sheet2.ProjectNumber, Sheet2.Engr, Sheet2.PltEngr, Sheet2.Cust, Sheet2.Application, Sheet2.Model, Sheet2.ProjectDescription, Sheet2.AnnalUnits, Sheet2.EstSystemPrice, Sheet2.AnnualSales, Sheet2.ProductionDate2, Sheet2.ProjectStatus, Sheet2.ProjectClosed
FROM Sheet2
INNER JOIN (SELECT ProjectID, Max(NoteDate) As MaxNoteDate FROM Notes GROUP BY ProjectID) As B ON Sheet2.ID = B.ProjectID

WHERE (((Sheet2.Engr)=[Forms]![Admin Menu]![Engineer]) AND ((Sheet2.ProjectClosed)=[Forms]![Admin Menu]![Geo] Or (Sheet2.ProjectClosed)=[Forms]![Admin Menu]![Piv])) OR (((Sheet2.PltEngr)=[Forms]![Admin Menu]![Engineer]) AND ((Sheet2.ProjectClosed)=[Forms]![Admin Menu]![Geo] Or (Sheet2.ProjectClosed)=[Forms]![Admin Menu]![Piv]))

ORDER BY Sheet2.Year, Sheet2.ProjectNumber;[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[sturner333]

Thanks for your help. I will try it tomorrow.

 

[sturner333]

I get this error when I added your code.

The Microsoft Jet Database engine cannot find the input table or query 'Select ProjectId, Max(NoteDate) As MaxNoteDate From Notes DROUP BY ProjectID'. Make sure it exists and that its name is spelled correctly.

Any ideas?
Thanks

 

[Golom]

Unless it's just a typo in posting, change

... [red]D[/red]ROUP BY ProjectID'

to

... [red]G[/red]ROUP BY ProjectID'

 

[sturner333]

typo

 

[lespaul]

what version of access?

Leslie

 

[sturner333]

2003, SP2

 

[lespaul]

Ok you have two tables, Sheet2 and Notes.

Notes:
ProjectID
NoteDate
Note(?)

there are multiple notes for each date and project.

ProjectId NoteDate Note
1 2/16/06 Something happened
1 2/16/06 Joe did it
1 2/28/06 Joe made it better

Now you want all the fields from Sheet2 and just the MAX NoteDate from Notes? In this example, the 3rd record with the 2/28/06 date?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[sturner333]

Correct.

 

[sturner333]

Leslie:
Since this is for a report, if this way of querying is too difficult, is there another route to do what I want to do?
Thanks for the help.