need help with update query

[arion]

Hello,

I am working on a very complex database and now I am trying to create an update query. The query is showing too few records. I am trying to update data in the table "inventory" with data from "workorder" table. The query is just copying the last workorder. I would like to update all new workorders to the inventory (can be until 100 records).

I would really appreciate your help! Thank you!

Imke

[jimatqsi]

We're going to need some info about what your query looks like. Can you post the query/queries SQL code? Or describe any criteria you've applied?

Jim

[arion]

Hello,

the following is the SQL

UPDATE Inventory, WorkorderNewVal ve, [SQ Customers and Sales] INNER JOIN Workorders ON [SQ Customers and Sales].SalesID = Workorders.Sale sID SET Inventory.[Drawing#] = Left([Model#],5) & [Abbreviation], Inventory.Worko rderNumber = [Forms]![Sales Form].[SalesID] & [WOIndex], Inventory.Descr iption = [WorkorderNewVal ve].[part], Inventory.Mater ial = [Workorders].[Material], Inventory.Quant ity = [WorkorderNewVal ve].[Quantity], Inventory.Remar ks = [Workorders].[Remarks], Inventory.[Distinguishing Mark] = "changed"
WHERE (((Inventory.In ventoryID) Like [Forms]![First empty InventoryID]![InventoryID]));

Does that help? Thank you...

Imke

[NeoPa]

It's still hard to see too much because we don't have the data, but I would guess, from the WHERE clause using the term 'Like' that you wanted to enter in the form a non-exact match.
If that is the case you want something like

WHERE (((Inventory.InventoryID) Like "*" & [Forms]![First empty InventoryID]![InventoryID] & "*"));

as the last line.
If not, the last line should read

WHERE (((Inventory.InventoryID) = [Forms]![First empty InventoryID]![InventoryID]));

although, to be honest, that would have little effect on the overall result.

I'm hoping the former is true because that might explain why fewer are found than you expected.
If not, something in the way of indicative data would be required.

[arion]

Unfortunately, it is not working. I need the second code that you suggested. I would like to update the first empty inventory ID. It is working perfectly with other update queries that I am using.

I am creating some workorders through clicking on a button in a form. There are one hundred workorders for each form. I am trying to update existing InventoryIDs with these workorders.

he InventoryIDs do exist because different databases will be imported in one main database, so each one needs to have its own id numbers.

The workordernumber s that should be copied are created through using the sales ID and the number of the workorder, e.g. the first one from saleID 1234 is then 123401. The update query is just showing the workordernumber 123499 instead of all 100, starting with 123401. Sorry, it is confusing. But does that help?

[NeoPa]

Arion,

There's so much you're not telling here.
What you need to do is :-
1. Cut out everything from the query that isn't part of the problem. If you have any tables in there that make no difference - cut them out. If you have any field updates in there that make no difference - cut them out too.
2. Give enough data - example recordsets of the tables used - to show the problem, as well as the results which are wrong and explain what is expected.
3. Think of the terms you use. Do they mean anything generally, or are they terms that only mean something in your business or if you are already familiar with the work you're doing.

If you consider all these points then it should be a lot easier for someone to help you.
I'm going offline soon but I hope to catch up again tomorrow if I get any free time. If you can post something clearer then I'll certainly look at this thread first.

-NeoPa.

[MMcCarthy]

Firstly, you have to create proper joins between all of your tables:

UPDATE (Inventory INNER JOIN
(WorkorderNewVa lve INNER JOIN
([SQ Customers and Sales] INNER JOIN Workorders
ON [SQ Customers and Sales].SalesID = Workorders.Sale sID)
ON WorkorderNewVal ve.[ID Field]=[SQ Customers and Sales].[ID Field])
ON Inventory.[ID Field]=WorkorderNewVa lve.[ID Field])
SET Inventory.[Drawing#] = Left([Model#],5) & [Abbreviation], Inventory.Worko rderNumber = [Forms]![Sales Form].[SalesID] & [WOIndex], Inventory.Descr iption = [WorkorderNewVal ve].[part], Inventory.Mater ial = [Workorders].[Material], Inventory.Quant ity = [WorkorderNewVal ve].[Quantity], Inventory.Remar ks = [Workorders].[Remarks], Inventory.[Distinguishing Mark] = "changed"
WHERE Inventory.Inven toryID=[Forms]![First empty InventoryID]![InventoryID];

[arion]

Now it is saying "Type mismatch in expressions"

UPDATE Inventory INNER JOIN (WorkorderNewVa lve INNER JOIN ([SQ Customers and Sales] INNER JOIN Workorders ON [SQ Customers and Sales].SalesID = Workorders.Sale sID) ON WorkorderNewVal ve.WOIndex = [SQ Customers and Sales].SalesID) ON Inventory.Inven toryID = WorkorderNewVal ve.WOIndex SET Inventory.[Drawing#] = Left([Model#],5) & [Abbreviation], Inventory.Worko rderNumber = [Forms]![Sales Form].[SalesID] & [WOIndex], Inventory.Descr iption = [WorkorderNewVal ve].[part], Inventory.Mater ial = [Workorders].[Material], Inventory.Quant ity = [WorkorderNewVal ve].[Quantity], Inventory.Remar ks = [Workorders].[Remarks], Inventory.[Distinguishing Mark] = "changed"
WHERE (Inventory.Inve ntoryID=[Forms]![First empty InventoryID]![InventoryID]);

[MMcCarthy]

Are WorkorderNewVal ve.WOIndex and [SQ Customers and Sales].SalesID of the same Data type?

Are Inventory.Inven toryID and WorkorderNewVal ve.WOIndex of the same Data type?

If so, then check that all the following are of the same Data type also:

Inventory.[Drawing#] = Left([Model#],5) & [Abbreviation] Inventory.Worko rderNumber = [Forms]![Sales Form].[SalesID] & [WOIndex]
Inventory.Descr iption = [WorkorderNewVal ve].[part]
Inventory.Mater ial = [Workorders].[Material]
Inventory.Quant ity = [WorkorderNewVal ve].[Quantity] Inventory.Remar ks = [Workorders].[Remarks]
Inventory.[Distinguishing Mark] = 'changed'

[arion]

Yes, that was the mistake. WOIndex was a text field instead of numeric field.

But it is not updating the inventory table. Now, it is not showing any record in the inventory table.