February 23, 2006 at 3:51 pm
We are having an issue with a query. The department for this claims that a query used to work but doesn't anymore. They want to show distinct entries but still see the person type field even though each person can have more then one type and hence more then one row. I assume that the information in the type field will be arbirtrary since only one result would be displayed. UID is a combination of the PEOPLE_CODE_ID and PEOPLE_TYPE fields. This is the query they claimed used to work.
SELECT Appeal.UID, Appeal.PEOPLE_CODE_ID, Appeal.PEOPLE_TYPE, Appeal.PREFIX, Appeal.FIRST_NAME, Appeal.MIDDLE_NAME, Appeal.LAST_NAME, Appeal.DECEASED_FLAG, Appeal.ADDRESS_TYPE, Appeal.ADDRESS_LINE_1, Appeal.ADDRESS_LINE_2, Appeal.ADDRESS_LINE_3, Appeal.CITY, Appeal.STATE, Appeal.ZIP_CODE, Appeal.DAY_PHONE, Appeal.NO_MAIL
FROM Appeal
WHERE (((Appeal.UID) Not In (SELECT DISTINCT x.[UID] FROM [Appeal] x, [Appeal] y Where x.[UID] > y.[UID] AND x.[PEOPLE_CODE_ID] = y.[PEOPLE_CODE_ID]; )))
ORDER BY Appeal.PEOPLE_CODE_ID;
February 24, 2006 at 2:07 pm
*SOLVED*
A cross tab query acts like a pivot table. The people_type field becomes the column headers for the query and a one is in the field of the people that belong to that group. That query then acts as a table for another query that lists all the extra information they want. It is a little slow with 30k entries but it isn't run very often.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply