query help for a report in access 2003

[cgrider]

ok I ma having a problem trying to filter out extra data from a table to generate a report
the table has a list of drawings and a particular project number associated with it so I created a report based on this query select * from tblwiringdiagram where project number = [Please enter Value]

now this works great.

then i get an Oh BTW we only need to show the latestest revisions of a particular drawing that is part of a project

so iam having a problem trying to add that extra piece to the report
the select statement i was thinking about was select * from tblwiringdiagram where revision=max(revision) but I dont know how to make it go for each individual drawing since the revsions for the drawings will be different

i am using Access 2003 if that helps any

thanks in advance for your help

CG

[MMcCarthy]

You can't Select * and have Max(Revision). What is the metadata of the table tblwiringdiagram and what fields do you absolutely have to return from that query.

Here is an example of how to post table MetaData :
Table Name=tblBookings

Field; Type; IndexInfo
 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Mary

[cgrider]

here is the metadata for the table
tablename=tblwiringdiagram

project_number,string
Serial_number, numeric
Drawing_number,string
Wire_number,string
From_Conn,string
From_term,string
To_conn,string
To_term,string
revision,string
 

the report has all of the fields from the table on it

originally the select statement for the report was
select * from tblwiringdiagram since the revision did not come into play
but now the revision is a factor and I am pretty clueless on how to create the sql statement for this

what is needed on the report is a list of all the drawings and their connectors from a particular project but only show the latest revision of that said drawing

the revision is in the form of A,B,C...etc so I was going to use the max of it to only select the latest.

I hope this helps

[MMcCarthy]

First problem is Revision is a string so what is the value in there.

[cgrider]

the revision field is of one Char revision A, revision B etc so it is just stripped down to the individual char A, B, etc

here is the sql command I just came up with let me know if this looks correct and/or is there anythign in there that is not needed.

SELECT *
FROM tblwiringdiagram AS db1 RIGHT JOIN [SELECT tblWiringDiagram.Drawing_Number, Max(tblWiringDiagram.Revision) AS MaxOfRevision
FROM tblWiringDiagram
GROUP BY tblWiringDiagram.Drawing_Number]. AS db2 ON (db1.Drawing_Number = db2.Drawing_Number) AND (db1.Revision = db2.MaxOfRevision)
WHERE (((db1.Project_number)=[Please Enter the Project Number]));
 
 

[MMcCarthy]

SELECT *
FROM tblwiringdiagram AS db1 RIGHT JOIN (SELECT tblWiringDiagram.Drawing_Number, Max(tblWiringDiagram.Revision) AS MaxOfRevision
FROM tblWiringDiagram
GROUP BY tblWiringDiagram.Drawing_Number) AS db2 
ON (db1.Drawing_Number = db2.Drawing_Number 
AND db1.Revision = db2.MaxOfRevision)
WHERE (((db1.Project_number)=[Please Enter the Project Number]));
 

I don't think the Max will work properly on the text field but you can try it. I made a couple of changes to the syntax. Just wondering why you are using a right join rather than an inner join.

[cgrider]

it looks like it does work so far so keeping fingers corssed :) thanks for you help as for the right as opposed to the inner I changed it to the latter and the query worked also with no change in number of records so I guess either works. is one better than the other for speed wise? I am not that familiar with either approach so any input would be appreciated. As for the Max() working with string I believe it converts it to the ascii representation of the text and does it calculations based off that. So a lower case A would be greater than an uppercase A. I am only guessing, if someone could verify that I would greatly appreciate it. If this is the case I will have to add the function ToUpper() for the string to make sure noone put a lower case in there to mess with the revision.

Thank you for all your help

CG

[MMcCarthy]

it looks like it does work so far so keeping fingers corssed :) thanks for you help as for the right as opposed to the inner I changed it to the latter and the query worked also with no change in number of records so I guess either works. is one better than the other for speed wise? I am not that familiar with either approach so any input would be appreciated.

The right join pulls all records from the recordset on the right and only matching records from the left recordset. Left join does the reverse. In this case you only want records that match in both tables so an inner join is appropriate.

As for the Max() working with string I believe it converts it to the ascii representation of the text and does it calculations based off that. So a lower case A would be greater than an uppercase A. I am only guessing, if someone could verify that I would greatly appreciate it. If this is the case I will have to add the function ToUpper() for the string to make sure noone put a lower case in there to mess with the revision.

I'm not expert on this but I'm pretty sure you are correct on this.

Thank you for all your help

CG

You're welcome

[cgrider]

well I did some testing wiuth upper and lower cases it did not seem to care. An "A" was the same as an "a" so I am not sure how it does its calculations. So if anyone has any insight on this i would appreciate it.

thanks

CG
but the code is working so all is good for the time being :)