Query that needs help

[Lamb2087]

I have an MS Access that is the front end and link it to a SQL DB through the tables.
I am wanting to only show the oldest date in a class year if the same person is listed more than once.

Tried Min in the Total Criteria and it did not work.

I can post the sql if needed.

 

[GumbyD]

Try using first instead of min. If you have any other fields that are not groupby or where what are they set to?

GumbyD

 

[Lamb2087]

I changed the field Class_Year to First as the Total Criteria. I left the other fields as Group By. When I run the query it will lock up and I never get to see the results.

 

[GumbyD]

Please post the SQL.

GumbyD

 

[Lamb2087]

I want the oldest Class Year for each record.

SELECT dbo_PEOPLE.PEOPLE_CODE_ID, dbo_CODE_PREFIX.SHORT_DESC, dbo_PEOPLE.FIRST_NAME, dbo_PEOPLE.MIDDLE_NAME, dbo_PEOPLE.LAST_NAME, dbo_PEOPLE.SUFFIX, dbo_ALUMNICLASS.CLASS_YEAR, dbo_ALUMNICLASS.FORMAL_TITLE, dbo_GIFTPLEDGE.GIFT_BATCH, dbo_GIFTPLEDGE.GIFT_DATE, dbo_GIFTPLEDGE.GIFT_AMOUNT, dbo_GIFTPLEDGE.GIFT_TENDER, dbo_GIFTPLEDGE.COMMENTS, dbo_ADDRESS.ADDRESS_LINE_1, dbo_ADDRESS.ADDRESS_LINE_2, dbo_ADDRESS.ADDRESS_LINE_3, dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIP_CODE, dbo_ADDRESS.COUNTRY, dbo_ADDRESS.NO_MAIL, dbo_ADDRESS.ADDRESS_TYPE, dbo_SALUTATION.SALUTATION, dbo_GIFTACKNOWLEDGE.ACKNOWLEDGE_TYPE
FROM dbo_GIFTCREDITDETAIL, dbRGANIZATION, ((((dbo_GIFTPLEDGE INNER JOIN (dbo_PEOPLE INNER JOIN dbo_ALUMNICLASS ON dbo_PEOPLE.PEOPLE_CODE_ID = dbo_ALUMNICLASS.PEOPLE_CODE_ID) ON dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID = dbo_PEOPLE.PEOPLE_CODE_ID) INNER JOIN dbo_ADDRESS ON (dbo_PEOPLE.PEOPLE_CODE_ID = dbo_ADDRESS.PEOPLE_ORG_CODE_ID) AND (dbo_ALUMNICLASS.PEOPLE_CODE_ID = dbo_ADDRESS.PEOPLE_ORG_CODE_ID) AND (dbo_PEOPLE.PREFERRED_ADD = dbo_ADDRESS.ADDRESS_TYPE)) INNER JOIN dbo_CODE_PREFIX ON dbo_PEOPLE.PREFIX = dbo_CODE_PREFIX.CODE_VALUE) INNER JOIN dbo_SALUTATION ON dbo_PEOPLE.PEOPLE_CODE_ID = dbo_SALUTATION.PEOPLE_CODE_ID) INNER JOIN dbo_GIFTACKNOWLEDGE ON dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID = dbo_GIFTACKNOWLEDGE.PEOPLE_ORG_CODE_ID
WHERE (((dbo_GIFTPLEDGE.GIFT_DATE)>=#9/30/2003#));

 

[GumbyD]

The SQL you posted is just a select query. Does it run as a select query and the problem only exists with the totals query only or does it lock up in both cases? Do you get a timeout message or does it just never come up?

GumbyD

 

[Lamb2087]

I never get a time out message!

It does not lock up in the select query, only when I put in the first for the total criteria.

 

[GumbyD]

What is the datatype in SQL of the ClassYear field? It may be that it is a text value and that is causing the problem. Is the data for that field "1999" or is it an actual date or a number?

GumbyD

 

[Lamb2087]

The data type in the table is text and there is a numeric value as the data.
This is why I am trying to convert to varchar with a query that bombs out on me and I can not figure what the problem is.

 

[GumbyD]

You can try putting the expression:
Cint([dbo_GIFTPLEDGE.GIFT_DATE])
instead of just the field that will convert the data to an integer and then you may be able to get the min or first on that data.

GumbyD

 

[Lamb2087]

You can try putting the expression:
Cint([dbo_GIFTPLEDGE.GIFT_DATE])
instead of just the field that will convert the data to an integer and then you may be able to get the min or first on that data.

This is not where the problem is. It is Class_Year, and I want the oldest classyear for each record, since they can have multiple classyears for the same record. This is why I trying to use min in the criteria to make this work. SO far I hav not had any luck making it happen.

 

[GumbyD]

Sorry - When I copied and pasted I got the wrong field. Try this:

Cint([dbo_ALUMNICLASS.CLASS_YEAR])

The cint function will convert the field to an integer value which you should then be able to get the min value of. I think the problem you were having is that Access could not resolve the datatype to provide a minimum.

 

[GumbyD]

One other thought that just occured to me. When I have delt with tables from a SQL database in Access. Sometimes I will make a local table out of the data by utilizing a make table query. Then I will try what I was doing with the linked table on the local table to see if the problem is with the data or the connection to the SQL database.

GumbyD

 

[Lamb2087]

Hey do I put the Cint([dbo_ALUMNICLASS.CLASS_YEAR])
in the criteria for the field Class_Year in the design view for the query or does it go some where else?

 

[Lamb2087]

How are the IT jobs in Denver these days? Have they picked up at all?

 

[GumbyD]

Put it in the the field area
ClassYear:CINT([dbo_ALUMNICLASS.CLASS_YEAR])

If this does not work I would bring the data locally. IT jobs in Denver are still pretty tight. I have had a few friends that are now starting to get decent jobs again. It looks like things will get better pretty soon (I hope!).

GumbyD