Move records from one table to another

[Minty]

This is a basic query - in the query builder select the fields you want including the Shipped yes /no field. If you allow nulls in your shipped check box then

Not Shipped
SELECT * From YourOrderTable WHERE Shipped = 0 or Is Null

Shipped
SELECT * From YourOrderTable WHERE Shipped = -1

 

[Khushalay]

tried it.

Not working

 

[Minty]

Okay - so can you show us exactly what the SQL is for your query? Telling us it's not working without showing WHAT isn't working is like phoning a doctor and only telling him you don't feel well.

 

[Khushalay]

The query is working but giving me duplicate records. It shows me one record 5 times.

SELECT order.[SalesOrderNo], order.[oDate], order.[Application], order.[AdditionalInformation], order.[Image], order.[Image].[FileData], [Image].[FileFlags] AS Expr1, order.[Image].[FileName], [Image].[FileTimeStamp] AS Expr2, order.[Image].[FileType], [Image].[FileURL] AS Expr3, order.[Customer], order.[Brand], order.[RepeatofOrderNo], order.[StyleNo], order.[Description], order.[SizeRange], order.[SizeRange].[Value], order.[size], order.[Ratio], order.[ColorOne], order.[ColorTwo], order.[ColorThree], order.[ColorFour], order.[q1], order.[q2], order.[q3], order.[q4], order.[q5], order.[q6], order.[q7], order.[q8], order.[q9], order.[q10], order.[q11], order.[q12], order.[q13], order.[q14], order.[q15], order.[q16], order.[q17], order.[q18], order.[q19], order.[q20], order.[q21], order.[q22], order.[q23], order.[q24], order.[q25], order.[q26], order.[q27], order.[CustomerOrderNo], order.[q28], order.[CustomerRefNo], order.[q29], order.[CustomerDeliveryDate], order.[q30], order.[q31], order.[Trims], order.[Trims].[Value], order.[q32], order.[Sub-Panel], order.[q33], order.[Sub-AOP], order.[Emb], order.[q34], order.[Laser], order.[q35], order.[IssuedDate], order.[q36], order.[t1], order.[t2], order.[t3], order.[t4], order.[Fabric1], order.[Color1], order.[Composition1], order.[Supplier1], order.[Costing1], order.[Fabric2], order.[Color2], order.[Composition2], order.[Supplier2], order.[Costing2], order.[Fabric3], order.[Color3], order.[Composition3], order.[Supplier3], order.[Costing3], order.[Fabric4], order.[Color4], order.[Composition4], order.[Supplier4], order.[Costing4], order.[Fabric5], order.[Color5], order.[Composition5], order.[Supplier5], order.[Costing5], order.[Label], order.[Label].[Value], order.[app], order.[app].[Value], order.[SizeOne], order.[SizeTwo], order.[SizeThree], order.[SizeFour], order.[SizeFive], order.[SizeSix], order.[SizeSeven], order.[SizeEight], order.[SizeNine], order.[SizeTen], order.[Ref], order.[sizeBoundaries], order.[MillenniumDeliveryDate], order.[Approved], order.[Pattern], order.[ToCut], order.[Fabric], order.[Trims1], order.[Status], order.[Updated], order.[Remarks], order.[Cut], order.[Shipped]
FROM [order]
WHERE (((order.[Shipped])<>False));

 

[Minty]

How many records are returned if you only do

SELECT order.[SalesOrderNo], order.[oDate] 
FROM [order]
WHERE order.[Shipped])<>False

And your data is very badly stored, what are all the order.[qXX] fields, and order.[Size????] fields?

 

[Khushalay]

q are the 30 text boxes where user enter 30 sizes of the order. Same is size boxes.

 

[Khushalay]

and no duplicates are coming in the query you gave.

 

[Khushalay]

I guess I made some mistakes that Im unaware of. In the start it was only giving me the required records but when I tried after a while it started giving me duplicates.

 

[gemma-the-husky]

I would do it as a separate process.

Say, once a week move all the processed items to the processed table.

one append query, one delete query.
I would do a check count.

count the item in the processed table
count the items to transfer
do the transfer
count the items in the processed table again
if the total is correct, delete the transferred items.

that's the problem. If anything goes wrong, you have a bit of detective work!

---
for what it's worth, I do a similar thing in a database. Sometimes, I wish I hadn't but there are only a few hundred pending items, and there are many thousands of processed records. It actually helps in some ways having the pending items separate.

 

[Minty]

You shouldn't be storing 30 size boxes. You should have a OrderSize field that stores the size value . If you need to add a size you will have to rewrite all your queries, and forms to accommodate it. Same with your other Size field.
If you have many types of size measurement use 2 fields size type, size value.

If the simple version of the query brings in the correct values, slowly add the other fields you need until it duplicates again, that will show you where your error is.

 

[Khushalay]

You shouldn't be storing 30 size boxes. You should have a OrderSize field that stores the size value . If you need to add a size you will have to rewrite all your queries, and forms to accommodate it. Same with your other Size field.
If you have many types of size measurement use 2 fields size type, size value.

If the simple version of the query brings in the correct values, slowly add the other fields you need until it duplicates again, that will show you where your error is.

yes, thats what I did, slowly adding fields but at a point it duplicates, I checked the sql code, couldn't find anything.
just wondering what could be the reason.

 

[Minty]

So what field causes the duplicate? I suspect it was where you were bringing in effectively the same field twice for some reason e.g.

order.[app], order.[app].[Value]

Although I'm not sure why this would duplicate the result...

 

[Khushalay]

its like the whole record is coming 5 times

 

[Minty]

Do you have any multivalued or lookup fields?

 

[Khushalay]

Do you have any multivalued or lookup fields?

No :banghead:

 

[Minty]

Okay - can you post up a stripped down zipped version of your database with the query and table causing the problem ?