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 know- I'm crazy right...

 

[Infofetish]

I tried this one too and it still says extra )

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")));

 

[Norie]

I really think you need to reorganise your data.

You appear to have 26 fields for PI-LastName and Co Inves-LastName.

 

[Infofetish]

thanks- I think I might see where my error is now. thanks!

 

[Infofetish]

ok...so now I fixed the query a bit but it's still telling me that 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].[ Co-Inves 1- FirstName])="S")) OR (((['C].[Co-Inves 2- LastName])="Hanna")) AND ((['C].[ Co-Inves 2- FirstName])="S")) OR (((['C].[Co-Inves 3- LastName])="Hanna")) AND ((['C].[ Co-Inves 3- FirstName])="S")) OR (((['C].[Co-Inves 4- LastName])="Hanna")) AND ((['C].[ Co-Inves 4- FirstName])="S")) OR (((['C].[Co-Inves 5- LastName])="Hanna"))) AND ((['C].[ Co-Inves 5- FirstName])="S")) OR (((['C].[Co-Inves 6- LastName])="Hanna")) AND ((['C].[ Co-Inves 6- FirstName])="S")) OR (((['C].[Co-Inves 8- LastName])="Hanna")) AND ((['C].[ Co-Inves 7- FirstName])="S")) OR (((['C].[Co-Inves 7- LastName])="Hanna")) AND ((['C].[ Co-Inves 8- FirstName])="S")) OR (((['C].[Co-Inves 9- LastName])="Hanna")) AND ((['C].[ Co-Inves 9- FirstName])="S")) OR (((['C].[Co-Inves 10- LastName])="Hanna")) AND ((['C].[ Co-Inves 10- FirstName])="S")) OR (((['C].[Co-Inves 11- LastName])="Hanna")) AND ((['C].[ Co-Inves 11- FirstName])="S")) OR (((['C].[Co-Inves 13- LastName])="Hanna")) AND ((['C].[ Co-Inves 12- FirstName])="S")) OR (((['C].[Co-Inves 12- LastName])="Hanna")) AND ((['C].[ Co-Inves 13- FirstName])="S")) OR (((['C].[Co-Inves 14- LastName])="Hanna")) AND ((['C].[ Co-Inves 14- FirstName])="S")) OR (((['C].[Co-inves 15- LastName])="Hanna")) AND ((['C].[ Co-Inves 15- FirstName])="S")) OR (((['C].[Co-Inves 16- LastName])="Hanna")) AND ((['C].[ Co-Inves 16- FirstName])="S")) OR (((['C].[Co-Inves 17- LastName])="Hanna")) AND ((['C].[ Co-Inves 17- FirstName])="S")) OR (((['C].[Co-Inves 18- LastName])="Hanna")) AND ((['C].[ Co-Inves 18- FirstName])="S")) OR (((['C].[Co-Inves 19- LastName])="Hanna")) AND ((['C].[ Co-Inves 19- FirstName])="S")) OR (((['C].[Co-Inves 20- LastName])="Hanna")) AND ((['C].[ Co-Inves 20- FirstName])="S")) OR (((['C].[Co-Inves 21- LastName])="Hanna")) AND ((['C].[ Co-Inves 21- FirstName])="S")) OR (((['C].[Co-Inves 22- LastName])="Hanna")) AND ((['C].[ Co-Inves 22- FirstName])="S")) OR (((['C].[Co-Inves 23- LastName])="Hanna")) AND ((['C].[ Co-Inves 23- FirstName])="S")) OR (((['C].[Co-Inves 24- LastName])="Hanna")) AND ((['C].[ Co-Inves 24- FirstName])="S")) OR (((['C].[Co-Inves 25- LastName])="Hanna")) AND ((['C].[ Co-Inves 25- FirstName])="S")) OR (((['C].[Co-Inves 26- LastName])="Hanna")) AND ((['C].[ Co-Inves 26- FirstName])="S")));

 

[boblarson]

I think I might see where my error is now.

If you don't see your error as the table structure then you don't see your error. The table structure is not normalized and if it was it would not require a query of this length and magnitude to pull the data you want. But the way it is now it is atrocious and pulling meaningful data is a pain in the butt, to be nice about it.

To REALLY fix it, you need to normalize the database.

 

[Infofetish]

so I tried a script...
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")) OR (((['C].[Co-Inves 2- LastName])="Hanna")) OR (((['C].[Co-Inves 3- LastName])="Hanna")) OR (((['C].[Co-Inves 4- LastName])="Hanna")) OR (((['C].[Co-Inves 5- LastName])="Hanna")) OR (((['C].[Co-Inves 6- LastName])="Hanna")) OR (((['C].[Co-Inves 8- LastName])="Hanna")) OR (((['C].[Co-Inves 7- LastName])="Hanna")) OR (((['C].[Co-Inves 9- LastName])="Hanna")) OR (((['C].[Co-Inves 10- LastName])="Hanna")) OR (((['C].[Co-Inves 11- LastName])="Hanna")) OR (((['C].[Co-Inves 13- LastName])="Hanna")) OR (((['C].[Co-Inves 12- LastName])="Hanna")) OR (((['C].[Co-Inves 14- LastName])="Hanna")) OR (((['C].[Co-inves 15- LastName])="Hanna")) OR (((['C].[Co-Inves 16- LastName])="Hanna")) OR (((['C].[Co-Inves 17- LastName])="Hanna")) OR (((['C].[Co-Inves 18- LastName])="Hanna")) OR (((['C].[Co-Inves 19- LastName])="Hanna")) OR (((['C].[Co-Inves 20- LastName])="Hanna")) OR (((['C].[Co-Inves 21- LastName])="Hanna")) OR (((['C].[Co-Inves 22- LastName])="Hanna")) OR (((['C].[Co-Inves 23- LastName])="Hanna")) OR (((['C].[Co-Inves 24- LastName])="Hanna")) OR (((['C].[Co-Inves 25- LastName])="Hanna")) OR (((['C].[Co-Inves 26- LastName])="Hanna"));


and this one works but I need to add all the feilds for first name from 2 to 26...what was I doing wrong??

 

[Infofetish]

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????

 

[Norie]

I actually meant reorganise the table.

I'm pretty it isn't normalised, if it was this query would probably be straightforward.

You certainly wouldn't be dealing with so many fields.

If you can't reorganise it you might be able to shorten the query or use more than one query.

For example you could use concatenation to combine the first and last name for each Co Inves field 'pair' in a query:

CoInves1:['C].[Co-Inves 1- LastName] & ['C].[ Co-Inves 1- FirstName]

CoInves2:['C].[Co-Inves 2- LastName] & ['C].[ Co-Inves 2- FirstName]
...
CoInves26:['C].[Co-Inves 26- LastName] & ['C].[ Co-Inves 26- FirstName]="S"

Obviously you would include all the other required fields.

Then if you use that query you could use something like this for part of the criteria:

ConInves1 = "HannaS" OR ConInves2 = "HannaS" OR ... OR ConInves26 = "HannaS"

Which doesn't use any parentheses.