Combining two queries in one sql statement (1 Viewer)

Razzbarry

Registered User.
Local time
Today, 08:25
Joined
Sep 28, 2018
Messages
28
Hi,
I am relatively new at access and am trying to reduce the amount of objects in my database by combining queries or embedding queries in forms. I am breaking the "if it ain't broke don't fix it rule".
I receive data updates daily which I have linked to a table in access. It requires some formatting to make it usable i.e. removal of spaces and unnecessary text which I do with a append query. I clean out the previous data with a delete query.
I tried to combine the two in sql with the following code:
DELETE [TBL Shipping report CLEAN].*
FROM [TBL Shipping report CLEAN];


INSERT INTO [TBL Shipping report CLEAN] ( [Customer Num], [Bill To Name], [Invoice Num], [Invoice Date], [PL Name], [Item Num], [Qty Shipped], Split, FQ, [Sales Post Split], [GP Post Split], [GP %], [End Customer], [Customer PO], [Opp Review] )
SELECT [tbl- LKD Shipping report]!F3 AS [Customer Num], [tbl- LKD Shipping report]!F5 AS [Bill To Name], [tbl- LKD Shipping report]!F6 AS [Invoice Num], [tbl- LKD Shipping report]!F8 AS [Invoice Date], [tbl- LKD Shipping report]!F9 AS [PL Name], [tbl- LKD Shipping report]!F10 AS [Item Num], [tbl- LKD Shipping report]!F11 AS [Qty Shipped], [tbl- LKD Shipping report]!F14 AS Split, [tbl- LKD Shipping report]!F15 AS FQ, [tbl- LKD Shipping report]!F16 AS [Sales Post Split], [tbl- LKD Shipping report]!F20 AS [GP Post Split], [tbl- LKD Shipping report]!F24 AS [GP %], [tbl- LKD Shipping report]!F30 AS [End Customer], [tbl- LKD Shipping report]!F32 AS [Customer PO], [tbl- LKD Shipping report]!F33 AS [Opp Review]
FROM [tbl- LKD Shipping report]
WHERE (((IsNumeric([f6]))=True));

The code below the delete query looks like extra characters so it doesn't work. Any suggestions?
Can you embed a delete query in form? what is the most efficient? Thanks for your help in advance.
Razzbarry
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:25
Joined
Jan 23, 2006
Messages
15,362
I moved your post from Code Repository to Queries. (jdraw)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:25
Joined
Oct 29, 2018
Messages
21,357
Hi. Not sure I understand what you're asking. It sounds like you're saying you have a DELETE query and an APPEND query, and you're asking if they can be combined together. Is this correct? If so, I only know how to sort of combine an UPDATE and an APPEND query but not a DELETE and APPEND query. Otherwise, you can certainly execute as many action queries as you like using VBA. Also, why were you asking about a form?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:25
Joined
Aug 30, 2003
Messages
36,118
Access doesn't support multiple SQL statements in one query. You'll need to execute two queries, in whatever form (saved queries, VBA, etc).
 

Micron

AWF VIP
Local time
Today, 10:25
Joined
Oct 20, 2018
Messages
3,476
You cannot "combine" queries. You can nest them, or have a query containing a subquery, but there's no combining. You can run call one then another, or you can run one then another, or you can execute one then another, or any combination of those actions that suit the query or sql type. IMHO, you don't embed queries - they either belong to the queryDefs collection (as standard queries) or as sql behind a form/report, or as sql in a procedure outside of a form/report. Lately I've seen attempts to store sql in a table and call it from a form. Have to say that at this point, it makes about as much sense as storing beer in the oven. About the only thing I know of that you can embed is macros, and that I never do.
 

isladogs

MVP / VIP
Local time
Today, 14:25
Joined
Jan 14, 2017
Messages
18,186
Agree with previous comments. Separate queries required.

The only exceptions to this that I can think of are:
a) UNION queries which consist of two or more select queries 'joined' together
b) Subqueries as part of a SELECT query
c) The combined APPEND/UPDATE (AKA UPEND or UPSERT) query

To fully synchronise your data you can do it in several ways e.g.
a) DELETE all existing records then APPEND all new records
b) UPDATE existing records, APPEND new records, DELETE outdated records

I've written a long article about different methods of Synchronising Data on my website: http://www.mendipdatasystems.co.uk/synchronise-data-1/4594514001
 

Users who are viewing this thread

Top Bottom