SQL view Information not working

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

The SQL view information you gave me is not working:


Per your instructions to remove duplicates go into the SQL
View of the query after the word SELECT add the word
DISTINCT. I received an error message:

Invaid SQL Statement; expected DELETE, INSERT, PROCEDURE,
SELECT OR UPDATE.

I'm trying to produce a make table query from a linked
table. The field labeled APPL ID has duplicate grant
numbers and looks like this:


APPL ID Total Grant Award
6294701 1,500
6294701 1,500
6293862 1,500
6293862 1,500
6294789 1,500

The problem with the above is I'm counting the same
grant twice. I have over 5000 records and half are
duplicates. Any suggestions or can you tell me why the
above does not work????
 
Hi,


It is very hard to follow, you should have try to stay in the original
thread of discussion.

The query should be like SELECT DISTINCT ...
not DISTINCT SELECT ...


Since you did not post YOUR SQL statement, it is very have to tell why it is
wrong.


Hoping it may help,
Vanderghast, Access MVP
 
I have attached the SQL view. I have also tried to place
the word DISTINCT behind SELECT but when I run the query
an error appears:

Invaid SQL Statement; expected DELETE, INSERT, PROCEDURE,
SELECT OR UPDATE.

Pleas review the SQL View below. All I want to do is
remove the duplicates in the field labeled APPL ID.

SELECT LINK_QVR_QVR_DOWNLOAD_CAN_V.FY,
LINK_QVR_QVR_DOWNLOAD_CAN_V.APPL_TYPE_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.ADMIN_PHS_ORG_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.SUPPORT_YEAR,
LINK_QVR_QVR_DOWNLOAD_CAN_V.SUFFIX_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.SOURCE_CODE_DC,
LINK_QVR_QVR_DOWNLOAD_CAN_V.RFA_PA_NUMBER,
LINK_QVR_QVR_DOWNLOAD_CAN_V.PRIORITY_SCORE_NUM,
LINK_QVR_QVR_DOWNLOAD_CAN_V.PROG_CLASS_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.PI_NAME,
LINK_QVR_QVR_DOWNLOAD_CAN_V.LATEST_ENCUMBRANCE_DATE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.JOINT_FUNDED_APPL_CODE_DC,
LINK_QVR_QVR_DOWNLOAD_CAN_V.CAS_DOCUMENT_NUMBER,
LINK_QVR_QVR_DOWNLOAD_CAN_V.AIDS_RELATED_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.APPL_ID,
LINK_QVR_QVR_DOWNLOAD_CAN_V.EXPANDED_AUTHORITIES_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.MECHANISM_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.MECHANISM_DESCRIP,
LINK_QVR_QVR_DOWNLOAD_CAN_V.PROJECT_NUM,
LINK_QVR_QVR_DOWNLOAD_CAN_V.APPL_STATUS_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.APPL_STATUS_GROUP_DESCRIP,
LINK_QVR_QVR_DOWNLOAD_CAN_V.APPL_STATUS_DESCRIP,
LINK_QVR_QVR_DOWNLOAD_CAN_V.COMPETING_GRANT_CODE_DC,
LINK_QVR_QVR_DOWNLOAD_CAN_V.PROJECT_TITLE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.TOTAL_COST_REQUESTED_AMT,
LINK_QVR_QVR_DOWNLOAD_CAN_V.TOTAL_COST_REQUESTED_YEAR1_AMT,
LINK_QVR_QVR_DOWNLOAD_CAN_V.ACTIVITY_CODE,
LINK_QVR_QVR_DOWNLOAD_CAN_V.COUNCIL_MEETING_DATE
FROM LINK_QVR_QVR_DOWNLOAD_CAN_V
WHERE (((LINK_QVR_QVR_DOWNLOAD_CAN_V.FY)=2002) AND
((LINK_QVR_QVR_DOWNLOAD_CAN_V.APPL_TYPE_CODE) In
("1","2","3")) AND
((LINK_QVR_QVR_DOWNLOAD_CAN_V.ADMIN_PHS_ORG_CODE)="AI")
AND ((LINK_QVR_QVR_DOWNLOAD_CAN_V.SOURCE_CODE_DC)
="CURRENT") AND
((LINK_QVR_QVR_DOWNLOAD_CAN_V.APPL_STATUS_CODE) In
("01","02","03","04","07","08","09","10","12","13","14","15
","16","17","18","19","21","22","23","24","25","26","28","2
9","30","31","32","33","34","35","97","98")));
 
Hi,

SELECT distinct LINK_QVR_QVR_DOWNLOAD_CAN_V.FY, ...


should work without error, but may leave duplicated values in APPL_ID
field, since DISTINCT works by looking at all the selected fields (well,
there is probably a limit, the same as the maximum number of fields that can
be ORDER BY). To get just one record per APPL_ID, with its relevant
associated fields, but from any record (for a given specific APPL_ID), under
JET (not under MS SQL Server):


SELECT Last(FY),
Last(APPL_TYPE_CODE),
Last(ADMIN_PHS_ORG_CODE),
Last(SUPPORT_YEAR),
Last(SUFFIX_CODE),
...
Last(AIDS_RELATED_CODE),
APPL_ID,
Last(EXPANDED_AUTHORITIES_CODE),
Last(MECHANISM_CODE),
...
Last(COUNCIL_MEETING_DATE)

FROM LINK_QVR_QVR_DOWNLOAD_CAN_V

WHERE ((FY=2002) AND
(APPL_TYPE_CODE In
("1","2","3")) AND
(ADMIN_PHS_ORG_CODE="AI")
AND (SOURCE_CODE_DC ="CURRENT") AND
(APPL_STATUS_CODE In
("01","02","03","04","07","08","09","10","12","13","14","15
","16","17","18","19","21","22","23","24","25","26","28","2
9","30","31","32","33","34","35","97","98")))

GROUP BY APPL_ID;



where basically I just place each field in the SELECT with a LAST( ), except
for Appl_id, since it is in the GROUP BY clause I also added. No need to use
DISTINCT in that case.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top