Max issue with an Insert Into Select

  • Thread starter Thread starter Microsoft
  • Start date Start date
M

Microsoft

Hello, i am hoping someone maybe able to help me with a problem i am having
with an Insert Into Select statement.

I am getting the Parameter dialog coming up when i try to execute the
following query.
The query uses a lot of predefined values but the problem is with the Max
statement i think

Thanks for any help given
John

INSERT INTO GH_STREET_REFERENCES
(GSR_PK,GL3A_PK,GL4A_PK,GF_PK,LANGUAGE_CODE,
CHANGE_TYPE, END_X, END_Y, USRN,
DESCRIPTOR, NAMING_AUTHORITY_CODE, PROCESSED_FLAG,START_X, START_Y,
TOLERANCE, TYPE, VERSION_NUMBER)

SELECT Max(gh_street_references.gsr_pk) + 1 as gsr_pk,
GH_LEVEL3_ADDRESSES.GL3A_PK,
GH_LEVEL4_ADDRESSES.GL4A_PK,GH_FILES.GF_PK,'ENG','I',1,2,30400002,
'John Road',9,'N',3,4,5,6,7

FROM GH_FILES,
GH_LEVEL3_ADDRESSES,
GH_LEVEL4_ADDRESSES

WHERE GH_FILES.FILENAME = "1" AND
GH_LEVEL3_ADDRESSES.PL3A_PK = 0 AND
GH_LEVEL4_ADDRESSES.PL4A_PK = 0

Group by GH_LEVEL3_ADDRESSES.GL3A_PK,
GH_LEVEL4_ADDRESSES.GL4A_PK,GH_FILES.GF_PK,'ENG','I',1,2,30400002,
'John Road',9,'N',3,4,5,6,7
 
Since the table gh_street_references is not in the FROM clause of the SELECT
query there is no way to get a MAX of the gsr_pk field. You might be able
to get the max for ONE record using the DMAX function. Or you might need to
write some vba code that would add records one at a time.

What type of field is GSR_PK? If it is an autonumber then you don't need to
include it at all since it will be automatically generated.

If you are adding only one record then you could try
DMax("gsr_pk","gh_street_references")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top