ACCESS query question- extra ) in query

[Infofetish]

I can't tell where my extra ) is in this- but it won't let me save it and says there is an extra )

here is the query

SELECT ['C].[Faculty Co-Investigators], ['C].[Faculty_Co_PI], ['C].[PI- LastName], ['C].[Co-Inves 1- LastName], ['C].[Co-Inves 2- LastName], ['C].[Co-Inves 3- LastName], ['C].[Co-Inves 4- LastName], ['C].[Co-Inves 5- LastName], ['C].[Co-Inves 6- LastName], ['C].[Co-Inves 8- LastName], ['C].[Co-Inves 7- LastName], ['C].[Co-Inves 9- LastName], ['C].[Co-Inves 10- LastName], ['C].[Co-Inves 11- LastName], ['C].[Co-Inves 13- LastName], ['C].[Co-Inves 12- LastName], ['C].[Co-Inves 14- LastName], ['C].[Co-inves 15- LastName], ['C].[Co-Inves 16- LastName], ['C].[Co-Inves 17- LastName], ['C].[Co-Inves 18- LastName], ['C].[Co-Inves 19- LastName], ['C].[Co-Inves 20- LastName], ['C].[Co-Inves 21- LastName], ['C].[Co-Inves 22- LastName], ['C].[Co-Inves 23- LastName], ['C].[Co-Inves 24- LastName], ['C].[Co-Inves 25- LastName], ['C].[Co-Inves 26- LastName], ['C].[Grant Agency], ['C].[Grant Title], ['C].[$ Amount], ['C].[Submitted Date], ['C].[Awarded date], ['C].[End Date], ['C].Status, ['C].[PI- FirstName]
FROM ['C]
WHERE (((['C].[PI- LastName])="Hanna") AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 1- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 2- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 3- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 4- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 5- LastName])="Hanna"))) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 6- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 8- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 7- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 9- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 10- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 11- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 13- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 12- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 14- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-inves 15- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 16- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 17- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 18- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 19- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 20- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 21- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 22- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 23- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 24- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 25- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S"))) OR (((['C].[Co-Inves 26- LastName])="Hanna")) AND ((['C].[PI- FirstName])="S")));

 

[Infofetish]

I'll have to try that- so what your saying is combine the first and last name fields??

 

[boblarson]

I'm not an ACCESS person at all...so I don't know what normalizing my table structure means....

I can't figure out how to pull the data I need????

Can you explain your process and what it is you are trying to store and how you plan on using the data? That can help us give you better information on how to set it up.

 

[Norie]

Yes combining those fields might help.

As for the parentheses you have an extra ) at here:

(((['C].[Co-Inves 5- LastName])="Hanna")))

There might be others.

Mind you I agrees with Bob, the tables behind this need to be reorganized.

I think that's, at least part, of what he's recommending.

 

[boblarson]

Yes, I am advocating a redesign to fit what is needed in a normalized fashion and THEN work on the queries, etc. The names should not be in many fields but should only have ONE first name field and ONE last name field. If there are multiple names needed for something then it should be part of a junction table or a table to capture the many side of a one-to-many or many-to-many relationship.

 

[Infofetish]

SO this is a database that stores information about grants held in a unviersity department and I need to pull data from it to report on the grants held by faculty memebrs. So I need to pull their names out. I think the suggestions to combine the fields of first and last name might be the best answer. Because now its a bit confusing to try top get certian more common names out but if I have their firs tinital int he last name field it might just work better by creating more unique identifiers.
I'm going to give that a try and then get back to you on how it worked.

 

[Norie]

You do seem to have a many-to-many relationship here, between grants and members.

ie a grant can be held by one or more members, a member can hold one or more grants.

Is that correct or is it not possible for members to have more than one grant?

 

[boblarson]

SO this is a database that stores information about grants held in a unviersity department and I need to pull data from it to report on the grants held by faculty memebrs. So I need to pull their names out. I think the suggestions to combine the fields of first and last name might be the best answer. Because now its a bit confusing to try top get certian more common names out but if I have their firs tinital int he last name field it might just work better by creating more unique identifiers.
I'm going to give that a try and then get back to you on how it worked.

A simple answer I'm going to give here because I don't have the entirety of what fields you need to have, is this:

tblFaculty
FacultyID - Autonumber (Primary Key)
FirstName - Text
LastName - Text
DepartmentID - Long Integer (Foreign Key from Departments table)

tblDepartments
DepartmentID - Autonumber (PK)
DepartmentName - Text

tblGrants
GrantID - Autonumber (PK)
GrantDescription - Text
DateProposalSubmitted - Date/Time
DateGranted - Date/Time

tblFacultyGrants
FacultyGrantID - Autonumber (PK)
FacultyID - Long Integer (FK from tblFaculty)
GrandID - Long Integer (FK from tblGrants)


And using tblFacultyGrants, that is how we track the faculty associated with any particular grant. And we don't need to add their names to the table because we are using their ID which then the tables can be linked up when building a query/report for reporting. And this way you can have as many faculty members associated with a particular grant as you need without limitation and it makes querying easy because you are only dealing with rows of data and not columns.


So, like I said, this is the "simplified version" just to show the concept. Yours would need to be worked out based on what data you are trying to capture and use.

 

[Infofetish]

that sounds a bit over my ACCESS head but thanks...I think I'm going to try the frist name and Last Name combo as it will require less reorganization of the data.

 

[Infofetish]

Oh and Norie- yes faculty member have multiple grants.

 

[xenou]

all the And / Or in the original where clause you post are quite confusing. What is the "logic" behind that query?

By the way, I'd probably choke if someone gave me a database with a table call ['C] in it.

Also, on finding parenthesis download Notepad++ and copy the SQL into it. A good text editor such as this one will highlight matching pairs of parenthesis.