Copy Access Query WITHOUT the make table part

[Maggie Barr]

Greetings and thank you in advance if you can help,
I am using Access 2019, and I have a query that required a lot of time. It is working great, but when I ran a make table of the query, I unfortunately saved it. Now every time I want to run the query, it only refreshes and makes the table. Does anyone know what the sql portion of the make table command is within a query so I can copy the query and remove that portion so I just have the original query. I have read through the sql of the query and can't find it anywhere. I copied the sql portion of the query to make a new query, but it was a make table query still. I realized that my data is too large to even want to make a table anymore, and I just want to put the query in the subsequent database, but when I do that, it comes in as a make table query
I do not want to just make a query based of that to get all the fields because I want to be able to review and/or modify the manipulations within the query in the future. It seems ridiculous that I can't find a way to remove the make table portion of this query.
Any help would be appreciated, thank you for your time.
SIncerely,
Maggie Barr

 

[alansidman]

Post the SQL statement for the query so that it may be amended.

 

[Maggie Barr]

Post the SQL statement for the query so that it may be amended.

SELECT [EBird_ALL_DATA_Working Query].[GLOBAL UNIQUE IDENTIFIER], Flag_Archive_Lookup.Flag_Results, Flag_Archive_Lookup.[Why_Flagged:], Flag_Archive_Lookup.Final_Breeding_Code, Flag_Archive_Lookup.Final_Breeding_Category, [Non-Atlas_Flag_Archive_Lookup].[Non-Atlas_Flag_Results], [Non-Atlas_Flag_Archive_Lookup].[Non-Atlas_Why_Flagged:], [Non-Atlas_Flag_Archive_Lookup].[Non-Atlas_Final_Breeding_Code], [Non-Atlas_Flag_Archive_Lookup].[Non-Atlas_Final_Breeding_Category], IIf([PROJECT CODE]<>"EBIRD_ATL_ME","Not Atlas Portal","Atlas Portal") AS PORTAL_TYPE, [EBird_ALL_DATA_Working Query].[Data Source], Maine_Species_List.Species_Type, SpWithRiskyCode.Invalid_Code_4_Species, Valid_Breeders.Breeding_Type, [EBird_ALL_DATA_Working Query].[OBS_DAY-MONTH], DateValues_Lookup.[DAY-MONTH_VALUE], SP_Safe_Breeding_Dates.START_Date_VALUE, SP_Safe_Breeding_Dates.END_Date_VALUE, IIf([START_Date_VALUE] Is Null,"",IIf([DAY-MONTH_VALUE]<[START_Date_VALUE] Or [DAY-MONTH_VALUE]>[END_Date_VALUE],"OUTSIDE","GOOD RECORD")) AS OBS_DATE_ACCEPTANCE, Buffer_Acptble_Breeding_Dates.Buffer_START_Date_VALUE, Buffer_Acptble_Breeding_Dates.Buffer_END_Date_VALUE, IIf([Buffer_START_Date_VALUE] Is Null,"",IIf([DAY-MONTH_VALUE]<[Buffer_START_Date_VALUE] Or [DAY-MONTH_VALUE]>[Buffer_END_Date_VALUE],"OUTSIDE","GOOD RECORD")) AS OBS_Buffer_DATE_ACCEPTANCE, [EBird_ALL_DATA_Working Query].CATEGORY, [EBird_ALL_DATA_Working Query].[COMMON NAME], [EBird_ALL_DATA_Working Query].NEW_COMMON_NAME, [NEW_COMMON_NAME] & "; " & [CLOBlockNa] AS [Species; BlockName], [EBird_ALL_DATA_Working Query].[OBSERVATION DATE], [EBird_ALL_DATA_Working Query].[Species; BreedingCode], [EBird_ALL_DATA_Working Query].[BREEDING BIRD ATLAS CODE], Breeding_Code_Values.[Breeding_Code_Value ], Breeding_Code_Values.[Breeding_Category_Value_1-3], [EBird_ALL_DATA_Working Query].[BREEDING BIRD ATLAS CATEGORY], [EBird_ALL_DATA_Working Query].CLOBlockNa, [EBird_ALL_DATA_Working Query].CoordRegio, [EBird_ALL_DATA_Working Query].Modified_Block_Type, [EBird_ALL_DATA_Working Query].COUNTY, [EBird_ALL_DATA_Working Query].[OBSERVER ID], [EBird_ALL_DATA_Working Query].[SAMPLING EVENT IDENTIFIER], [EBird_ALL_DATA_Working Query].[GROUP IDENTIFIER], [EBird_ALL_DATA_Working Query].LOCALITY, [EBird_ALL_DATA_Working Query].[TRIP COMMENTS], [EBird_ALL_DATA_Working Query].APPROVED, [EBird_ALL_DATA_Working Query].REVIEWED, [EBird_ALL_DATA_Working Query].REASON, [EBird_ALL_DATA_Working Query].[PROTOCOL TYPE], [EBird_ALL_DATA_Working Query].[PROJECT CODE], [EBird_ALL_DATA_Working Query].[DURATION MINUTES], [EBird_ALL_DATA_Working Query].[NUMBER OBSERVERS], [EBird_ALL_DATA_Working Query].[SPECIES COMMENTS] INTO [eBird_ALL_DATA Query2 Base Data Output]
FROM (((((((([EBird_ALL_DATA_Working Query] LEFT JOIN Maine_Species_List ON [EBird_ALL_DATA_Working Query].NEW_COMMON_NAME = Maine_Species_List.Species) LEFT JOIN DateValues_Lookup ON [EBird_ALL_DATA_Working Query].[OBS_DAY-MONTH] = DateValues_Lookup.[DAY-MONTH]) LEFT JOIN SP_Safe_Breeding_Dates ON [EBird_ALL_DATA_Working Query].NEW_COMMON_NAME = SP_Safe_Breeding_Dates.Species) LEFT JOIN SpWithRiskyCode ON [EBird_ALL_DATA_Working Query].[Species; BreedingCode] = SpWithRiskyCode.[Species;_Code]) LEFT JOIN Valid_Breeders ON [EBird_ALL_DATA_Working Query].[Species; BreedingCode] = Valid_Breeders.[Species;_Breeding_Code]) LEFT JOIN Buffer_Acptble_Breeding_Dates ON [EBird_ALL_DATA_Working Query].[Species; BreedingCode] = Buffer_Acptble_Breeding_Dates.[Species;_Code]) LEFT JOIN Breeding_Code_Values ON [EBird_ALL_DATA_Working Query].[BREEDING BIRD ATLAS CODE] = Breeding_Code_Values.Breeding_Code) LEFT JOIN Flag_Archive_Lookup ON [EBird_ALL_DATA_Working Query].[GLOBAL UNIQUE IDENTIFIER] = Flag_Archive_Lookup.GLOBAL_UNIQUE_IDENTIFIER) LEFT JOIN [Non-Atlas_Flag_Archive_Lookup] ON [EBird_ALL_DATA_Working Query].[GLOBAL UNIQUE IDENTIFIER] = [Non-Atlas_Flag_Archive_Lookup].GLOBAL_UNIQUE_IDENTIFIER
WHERE ((([EBird_ALL_DATA_Working Query].CATEGORY) Not In ("hybrid","slash","spuh")) AND (([EBird_ALL_DATA_Working Query].[COMMON NAME]) Not In ("Helmeted Guineafowl (Domestic type)","Domestic goose sp. (Domestic type)","Muscovy Duck (Domestic type)","Graylag Goose (Domestic type)")) AND (([EBird_ALL_DATA_Working Query].CLOBlockNa) Is Not Null And ([EBird_ALL_DATA_Working Query].CLOBlockNa)<>""));

 

[Maggie Barr]

Post the SQL statement for the query so that it may be amended.

Hello alansidman!!
I want to thank you so much for your rapid response and interest in helping me, but I found it. It turns out the the portion of the SQL statement associated with the make table is "INTO newtable [IN externaldatabase]", so I went into my sql and removed "INTO [eBird_ALL_DATA Query2 Base Data Output] " and it worked at that point, the query is no longer a make table query and it preserved all of my manipulations.
Again, thank you for taking the time to reach out to assist. I really do appreciate it.
Best wishes, and I hope you are able to stay healthy through all the trying times we are all having with the Covid-19 pandemic.
Maggie Barr

 

[welshgasman]

I'm not sure something that big/complicated would go back to Design view, but if it could, all you would do is change the Query type icon in the toolbar.?

 

[Maggie Barr]

I'm not sure something that big/complicated would go back to Design view, but if it could, all you would do is change the Query type icon in the toolbar.?

welshgasman!
You are right! Just clicking from table to select in the Query type in the toolbar and saving the changes worked. I feel very foolish for not realizing that before. Guess it is par for the course not knowing Access all that well. I had found a way to remove it in the SQL part of the query as noted in my earlier reply, so it is good to have learned the SQL language for that part, but your method was much easier.
Thank you,
Maggie

 

[xenou]

Hi, are you are working on a birding project?

 

[Maggie Barr]

xenou,
Yes, I am working on a birding project. It has been a challenge to learn all the different database techniques I use, (Access, MS Excel Power Query, & ArcMap), but it is super cool and an amazing learning opportunity.
Maggie

 

[xenou]

Sounds cool!