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
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