need help with update query | Newbie | | Join Date: Oct 2006
Posts: 23
| | |
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
| | Member | | Join Date: Oct 2006 Location: Virginia
Posts: 65
| | | re: need help with update query
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
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
Hello,
the following is the SQL
UPDATE Inventory, WorkorderNewValve, [SQ Customers and Sales] INNER JOIN Workorders ON [SQ Customers and Sales].SalesID = Workorders.SalesID SET Inventory.[Drawing#] = Left([Model#],5) & [Abbreviation], Inventory.WorkorderNumber = [Forms]![Sales Form].[SalesID] & [WOIndex], Inventory.Description = [WorkorderNewValve].[part], Inventory.Material = [Workorders].[Material], Inventory.Quantity = [WorkorderNewValve].[Quantity], Inventory.Remarks = [Workorders].[Remarks], Inventory.[Distinguishing Mark] = "changed"
WHERE (((Inventory.InventoryID) Like [Forms]![First empty InventoryID]![InventoryID]));
Does that help? Thank you...
Imke
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,737
| | | re: need help with update query
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.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
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 workordernumbers 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?
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,737
| | | re: need help with update query
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.
|  | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Firstly, you have to create proper joins between all of your tables:
UPDATE (Inventory INNER JOIN
(WorkorderNewValve INNER JOIN
([SQ Customers and Sales] INNER JOIN Workorders
ON [SQ Customers and Sales].SalesID = Workorders.SalesID)
ON WorkorderNewValve.[ID Field]=[SQ Customers and Sales].[ID Field])
ON Inventory.[ID Field]=WorkorderNewValve.[ID Field])
SET Inventory.[Drawing#] = Left([Model#],5) & [Abbreviation], Inventory.WorkorderNumber = [Forms]![Sales Form].[SalesID] & [WOIndex], Inventory.Description = [WorkorderNewValve].[part], Inventory.Material = [Workorders].[Material], Inventory.Quantity = [WorkorderNewValve].[Quantity], Inventory.Remarks = [Workorders].[Remarks], Inventory.[Distinguishing Mark] = "changed"
WHERE Inventory.InventoryID=[Forms]![First empty InventoryID]![InventoryID];
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
Now it is saying "Type mismatch in expressions"
UPDATE Inventory INNER JOIN (WorkorderNewValve INNER JOIN ([SQ Customers and Sales] INNER JOIN Workorders ON [SQ Customers and Sales].SalesID = Workorders.SalesID) ON WorkorderNewValve.WOIndex = [SQ Customers and Sales].SalesID) ON Inventory.InventoryID = WorkorderNewValve.WOIndex SET Inventory.[Drawing#] = Left([Model#],5) & [Abbreviation], Inventory.WorkorderNumber = [Forms]![Sales Form].[SalesID] & [WOIndex], Inventory.Description = [WorkorderNewValve].[part], Inventory.Material = [Workorders].[Material], Inventory.Quantity = [WorkorderNewValve].[Quantity], Inventory.Remarks = [Workorders].[Remarks], Inventory.[Distinguishing Mark] = "changed"
WHERE (Inventory.InventoryID=[Forms]![First empty InventoryID]![InventoryID]);
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Are WorkorderNewValve.WOIndex and [SQ Customers and Sales].SalesID of the same Data type?
Are Inventory.InventoryID and WorkorderNewValve.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.WorkorderNumber = [Forms]![Sales Form].[SalesID] & [WOIndex]
Inventory.Description = [WorkorderNewValve].[part]
Inventory.Material = [Workorders].[Material]
Inventory.Quantity = [WorkorderNewValve].[Quantity] Inventory.Remarks = [Workorders].[Remarks]
Inventory.[Distinguishing Mark] = 'changed'
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
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.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
If you run this query you will see if the joins are working property. It may be that you are not returning any records.
SELECT Inventory.*, WorkorderNewValve.*, Workorders.*, [SQ Customers and Sales].*
FROM Inventory INNER JOIN (WorkorderNewValve
INNER JOIN ([SQ Customers and Sales] INNER JOIN Workorders
ON [SQ Customers and Sales].SalesID = Workorders.SalesID)
ON WorkorderNewValve.WOIndex = [SQ Customers and Sales].SalesID)
ON Inventory.InventoryID = WorkorderNewValve.WOIndex
WHERE (Inventory.InventoryID=[Forms]![First empty InventoryID]![InventoryID]);
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
By the way
The 'First empty InventoryID' form has to be open when this query is running.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
Yes the form is open, it is integrated in the according macro.
When I run the Select query it is saying "Too many fields defined".
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
run this instead
SELECT Inventory.InventoryID, WorkorderNewValve.WOIndex, Workorders..SalesID, [SQ Customers and Sales].SalesID
FROM Inventory INNER JOIN (WorkorderNewValve
INNER JOIN ([SQ Customers and Sales] INNER JOIN Workorders
ON [SQ Customers and Sales].SalesID = Workorders.SalesID)
ON WorkorderNewValve.WOIndex = [SQ Customers and Sales].SalesID)
ON Inventory.InventoryID = WorkorderNewValve.WOIndex
WHERE (Inventory.InventoryID=[Forms]![First empty InventoryID]![InventoryID]);
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
Yes, now it was working. But it is showing me 20,000 records instead of 100. It is repeating the workorders. And the inventoryID was the first empty one, but it didn't go on. It uses the same InventoryID for all 20,000 records.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Try this:
SELECT Inventory.InventoryID, Workorders.SalesID
FROM (Inventory INNER JOIN Workorders
ON Inventory.InventoryID = Workorders.SalesID)
WHERE (Inventory.InventoryID=[Forms]![First empty InventoryID]![InventoryID]); Quote:
Originally Posted by arion Yes, now it was working. But it is showing me 20,000 records instead of 100. It is repeating the workorders. And the inventoryID was the first empty one, but it didn't go on. It uses the same InventoryID for all 20,000 records. | | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
It is then not showing any record. When I delete the joint property it is showing me every workorder but still with the same first empty inventoryID.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Ok Arion back to basics
In your Inventory table you have a primary key called InventoryID, Yes?
In your Workorders table you have a key called Sales ID which is based on InventoryID, Yes/No?
If No, then what is the relationship between the two tables. Quote:
Originally Posted by arion It is then not showing any record. When I delete the joint property it is showing me every workorder but still with the same first empty inventoryID. | | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
Yes, the primary key for Inventory is InventoryID, but the primary key for Workorders is WorkorderNumber, not salesID.
When I build the joint property between inventory.workordernumber and workorders.workordernumber it is not showing any record either.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Quote:
Originally Posted by arion
When I build the joint property between inventory.workordernumber and workorders.workordernumber it is not showing any record either.
To join two tables you must have a one to many relationship. This means that one of the tables has a field (doesn't have to be primary key but it usually is) or a combination of fields??? that are unique to that table.
The field or fields would have to be represented in the other table. Although you can preform this join through a series of tables the condition must be true for each. Does this make sense. If you post the structure of your tables and tell me how they relate I will see if I can help. Make sure the date types of the relationships are the same.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
The inventory table is related to the workorders table through workorderNumber, the table workorders and SQ customers and sales are realted to each other through salesID.
I don't know how to relate the WorkordersNewValve table to these tables. This table shows the WOIndex which is included in the workordernumber. WorkorderNumber is built from salesID+WOIndex. Is that what you mean?
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Quote:
Originally Posted by arion
WorkorderNumber is built from salesID+WOIndex
Can I get examples of the structure and content of both these fields.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
The WorkorderNewValve table consists of the following fields:
Field Name Data Type
WOIndex Number
Part Text
Abbreviation Text
Material Text
Quantity Number
WOIndex content is the numbers 1 through 100. Part is e.g. Sideplate and the Abbreviation could be Sp.
The Workorders table consists of the fields:
Field Name Data Type
WorkorderNumber Number
SalesID Number
Quantity Number
Material Text
For the sale with salesID 3456 the workorderNumbers would then be 345601 through 3456100.
The Inventory table has the fields
InventoryID Number
WorkorderNumber Number
Quantity Number
Distinguishing Mark Text
etc.
The inventoryID already exists and the Distinguishing mark field is telling if the inventoryID is empty or not.
The problem that I have is that it is recognizing the first empty InventoyID but is not updateting with all 100 records. It is just showing me the record for workorderNumber 345699. Do you need more information?
I am going offline soon and will continue on monday. I thank you for your help, I really appreciate that.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Workorders Field Type
WorkorderNumber Number (PK)
SalesID Number
Quantity Number
Material Text
For the sale with salesID 3456 the workorderNumbers would then be 345601 through 3456100. Inventory Field Type
InventoryID Number (PK)
WorkorderNumber Number (FK to WorkOrders)
Quantity Number
Distinguishing Mark Text
etc. Quote:
The inventoryID already exists and the Distinguishing mark field is telling if the inventoryID is empty or not.
The problem that I have is that it is recognizing the first empty InventoyID but is not updateting with all 100 records. It is just showing me the record for workorderNumber 345699. Do you need more information? Are you trying to do an update or an append (insert) query?
Are you trying to add workorders to the Inventory table?
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
If I would use an append query it would create new InventoryIDs, right?
I thought I need to use an update query because the inventoryIDs already exist. The query should just fill out the empty fields, for example "WorkorderNumber", "Quantity" etc., for existing InventoryIDs.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
The problem is you are trying to update a table that has no relationship to the table you are updating from or any other table.
Try the following:
UPDATE Inventory SET ([Drawing#], WorkorderNumber, Material, Remarks, [Distinguishing Mark])=(SELECT Left([Model#],5) & [Abbreviation], [SalesID] & [WOIndex], Material, Remarks, 'Changed' FROM WorkOrders WHERE WorkOrders.SalesID=[Forms]![Sales Form].[SalesID]) WHERE WorkorderNumber Is Null;
omit the following you can do them later:
Inventory.Description = [WorkorderNewValve].[part],
Inventory.Quantity = [WorkorderNewValve].[Quantity],
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
When I try to do the following, it is saying "Syntax error in Update statement".
UPDATE Inventory SET ([Drawing#], WorkorderNumber, Material, Remarks, [Distinguishing Mark])=(SELECT Left([Model#],5) & [Abbreviation], [SalesID] & [WOIndex], Material, Remarks, 'Changed' FROM WorkOrders WHERE WorkOrders.SalesID=[Forms]![Sales Form].[SalesID]) WHERE WorkorderNumber Is Null;
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
I just don't think it can be done. I'd a feeling the last one wouldn't work but thought it was worth a try.
Why are you adding the InventoryNo first it would be so much easier to do this as an insert query.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
The problem is that we are having one main database and some satellite databases. Every person is working with his or her own database and the data will be imported to the main database. If I use auto number for InventoryID there will be some dublicates or some entries will just be overwritten. That is the reason why I thought we need to give each satellite its own inventoryIDs and use an update query. Do you see any other opportunity how to do the exchange?
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
The only other way to do this is using recordsets in vba code. Leave it with me.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Which tables are the following two fields from:
[Model#]
[Abbreviation]
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Had an idea.
I found abbreviation but still don't know the table containing model# so substitute the correct table for tableName in the following query. Run this query first and call it qry1.
qry1
SELECT WorkorderNewValve.WOIndex, WorkorderNewValve.part, Workorders.Material,
WorkorderNewValve.Quantity, Workorders.SalesID, Workorders.Remarks,
TableName.[Model#], WorkorderNewValve.Abbreviation
FROM WorkorderNewValve INNER JOIN Workorders
ON WorkorderNewValve.WOIndex = WorkOrders.SalesID
GROUP BY WorkorderNewValve.WOIndex;
now try the following update query:
UPDATE Inventory INNER JOIN qry1 ON Inventory.InventoryID=qry1.WOIndex
SET ([Drawing#] = Left(qry1.[Model#],5) & qry1.[Abbreviation],
WorkorderNumber = qry1.[SalesID] & qry1.[WOIndex],
Description = qry1.[part],
Material = qry1.[Material],
Quantity = qry1.[Quantity],
Remarks = qry1.[Remarks],
Distinguishing Mark = 'changed');
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
Model# is from "SQ Customers and Sales" Query. when I try to run the following query, it is saying "You tried to execute a query that does not include the specified expression 'Part' as part of an aggregate function."
SELECT WorkorderNewValve.WOIndex, WorkorderNewValve.Part, Workorders.Material, WorkorderNewValve.Quantity, Workorders.SalesID, Workorders.Remarks, [SQ Customers and Sales].[Model#] AS Expr1, WorkorderNewValve.Abbreviation
FROM WorkorderNewValve, Workorders INNER JOIN [SQ Customers and Sales] ON Workorders.SalesID = [SQ Customers and Sales].SalesID
GROUP BY WorkorderNewValve.WOIndex;
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
OK try this: (Make sure there are no As Expr1)
qry1
SELECT WorkorderNewValve.WOIndex, WorkorderNewValve.part, Workorders.Material,
WorkorderNewValve.Quantity, Workorders.SalesID, Workorders.Remarks, [SQ Customers and Sales].[Model#], WorkorderNewValve.Abbreviation
FROM WorkorderNewValve INNER JOIN (Workorders INNER JOIN [SQ Customers and Sales] ON Workorders.SalesID = [SQ Customers and Sales].SalesID) ON WorkorderNewValve.WOIndex = WorkOrders.SalesID
GROUP BY WorkorderNewValve.WOIndex;
now try the following update query:
UPDATE Inventory INNER JOIN qry1 ON Inventory.InventoryID=qry1.WOIndex
SET ([Drawing#] = Left(qry1.[Model#],5) & qry1.[Abbreviation],
WorkorderNumber = qry1.[SalesID] & qry1.[WOIndex],
Description = qry1.[part],
Material = qry1.[Material],
Quantity = qry1.[Quantity],
Remarks = qry1.[Remarks],
Distinguishing Mark = 'changed');
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
First it did not do anything at all, but after making some changes it is showing me 10,000 records instead of 100. It is repeating every record a hundred times. I did the following:
SELECT WorkorderNewValve.WOIndex, WorkorderNewValve.Part, Workorders.Material, WorkorderNewValve.Quantity, Workorders.SalesID, Workorders.Remarks, [SQ Customers and Sales].[Model#], WorkorderNewValve.Abbreviation, Workorders.WorkorderNumber
FROM WorkorderNewValve, Workorders INNER JOIN [SQ Customers and Sales] ON Workorders.SalesID = [SQ Customers and Sales].SalesID
GROUP BY WorkorderNewValve.WOIndex, WorkorderNewValve.Part, Workorders.Material, WorkorderNewValve.Quantity, Workorders.SalesID, Workorders.Remarks, [SQ Customers and Sales].[Model#], WorkorderNewValve.Abbreviation, Workorders.WorkorderNumber;
When I do run the update query:
UPDATE Inventory INNER JOIN qry1 ON Inventory.WorkorderNumber = qry1.WorkorderNumber SET Inventory.[Drawing#] = Left(qry1.[Model#],5) & qry1.[Abbreviation], Inventory.WorkorderNumber = qry1.[SalesID] & qry1.[WOIndex], Inventory.Description = qry1.[part], Inventory.Material = qry1.[Material], Inventory.Quantity = qry1.[Quantity], Inventory.Remarks = qry1.[Remarks], Inventory.[Distinguishing Mark] = 'changed';
It is then not showing me anything.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
You keep leaving out one of the joins I'm giving you that's why you're getting 10000 records.
Put in back in as follows:
SELECT WorkorderNewValve.WOIndex, WorkorderNewValve.Part, Workorders.Material, WorkorderNewValve.Quantity, Workorders.SalesID, Workorders.Remarks, [SQ Customers and Sales].[Model#], WorkorderNewValve.Abbreviation, Workorders.WorkorderNumber
FROM WorkorderNewValve INNER JOIN (Workorders INNER JOIN [SQ Customers and Sales] ON Workorders.SalesID = [SQ Customers and Sales].SalesID) ON WorkorderNewValve.WOIndex = WorkOrders.SalesID
GROUP BY WorkorderNewValve.WOIndex, WorkorderNewValve.Part, Workorders.Material, WorkorderNewValve.Quantity, Workorders.SalesID, Workorders.Remarks, [SQ Customers and Sales].[Model#], WorkorderNewValve.Abbreviation, Workorders.WorkorderNumber;
You can't do this:
UPDATE Inventory INNER JOIN qry1
ON Inventory.WorkorderNumber = qry1.WorkorderNumber
There is no value in WorkOrderNumber in the Inventory table to join on.
You have to use the InventoryId and WOIndex as follows:
UPDATE Inventory INNER JOIN qry1
ON Inventory.InventoryID = qry1.WOIndex
SET Inventory.[Drawing#] = Left(qry1.[Model#],5) & qry1.[Abbreviation], Inventory.WorkorderNumber = qry1.[SalesID] & qry1.[WOIndex], Inventory.Description = qry1.[part], Inventory.Material = qry1.[Material], Inventory.Quantity = qry1.[Quantity], Inventory.Remarks = qry1.[Remarks], Inventory.[Distinguishing Mark] = 'changed';
Copy these queries without making any changes and see if it works.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
OK, I did the two queries. They were running without error messages but it is not showing me any records.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Quote:
Originally Posted by arion OK, I did the two queries. They were running without error messages but it is not showing me any records. SELECT WorkorderNewValve.WOIndex, WorkorderNewValve.part, Workorders.Material,
WorkorderNewValve.Quantity, Workorders.SalesID, Workorders.Remarks, [SQ Customers and Sales].[Model#], WorkorderNewValve.Abbreviation
FROM WorkorderNewValve INNER JOIN (Workorders INNER JOIN [SQ Customers and Sales] ON Workorders.SalesID = [SQ Customers and Sales].SalesID) ON WorkorderNewValve.WOIndex = WorkOrders.SalesID
GROUP BY WorkorderNewValve.WOIndex;
If this is not showing any records then you have a problem with the tables you're trying to join. Not all of them have records in the fields you are trying to join on.
e.g.
Try running both of these queries separately. Are you getting results in each of them.
SELECT Workorders.* , [SQ Customers and Sales].[Model#]
FROM Workorders INNER JOIN [SQ Customers and Sales]
ON Workorders.SalesID = [SQ Customers and Sales].SalesID;
SELECT WorkorderNewValve.* , WorkOrders.SalesID
FROM WorkorderNewValve INNER JOIN Workorders
ON WorkorderNewValve.WOIndex = WorkOrders.SalesID;
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
No, I just got results for the first one:
SELECT Workorders.* , [SQ Customers and Sales].[Model#]
FROM Workorders INNER JOIN [SQ Customers and Sales]
ON Workorders.SalesID = [SQ Customers and Sales].SalesID;
The second one did not show results:
SELECT WorkorderNewValve.* , WorkOrders.SalesID
FROM WorkorderNewValve INNER JOIN Workorders
ON WorkorderNewValve.WOIndex = WorkOrders.SalesID;[/quote]
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
I guess I know why the second one is not showing results. We tried to join table WorkorderNewValve and table Workorders. They do not have any field in common.
The workorderNewValve table is just a table to help creating the workorderNumber. The salesID and the WOIndex are building the workorderNumber together.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
Do you know a possibility to do the update query just with the workorders table? I mean, everything that I would like to have updated in the Inventory table is already in the workorders table.
But when I do the following, it is not showing me any result:
UPDATE Inventory INNER JOIN Workorders ON Inventory.WorkorderNumber = Workorders.WorkorderNumber SET Inventory.[Drawing#] = [Workorders].[Drawing#], Inventory.WorkorderNumber = [Workorders].[WorkorderNumber], Inventory.Description = [Workorders].[Description], Inventory.Material = [Workorders].[Material], Inventory.Quantity = [Workorders].[Quantity], Inventory.Remarks = [Workorders].[Remarks], Inventory.[Distinguishing Mark] = "changed"
WHERE (((Inventory.InventoryID)=[Forms]![First empty InventoryID]![InventoryID]));
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
It can't because there is nothing in the field WorkorderNumber in the Inventory table.
SELECT *
FROM Inventory INNER JOIN Workorders
ON Inventory.InventoryID = Cint(right(Cstr(Workorders.WorkorderNumber),2));
Does this give you 100 empty records?
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
No, it gives me no records at all.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Quote:
Originally Posted by arion No, it gives me no records at all. My understand originally was that InventoryID contained 0 - 99 and Workorders.WorkOrderNumber contained salesID + 00 - 99 in sequence.
Is this not correct?
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
Workorders.WorkOrderNumber contained salesID + 00 - 99 in sequence is correct. But the InventoryID is not going from 00 - 99. InventoryID is right now for the person's database that I am using 141-390.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: need help with update query
I tried something else. If I do:
SELECT Left([Model#],5) & [Abbreviation] AS [Drawing#], [Forms]![Sales Form].[SalesID] & [WOIndex] AS WorkorderNumber, WorkorderNewValve.Part AS Description
FROM WorkorderNewValve, Workorders INNER JOIN [SQ Customers and Sales] ON Workorders.SalesID = [SQ Customers and Sales].SalesID
WHERE (((Workorders.WorkorderNumber) Like [Forms]![Workorder Form]![WorkorderNumber]));
it is showing me the 100 records. But it is not updating it in the inventory table when I do the following:
UPDATE [SQ WorkorderNumber NewValve], Workorders INNER JOIN Inventory ON Workorders.WorkorderNumber = Inventory.WorkorderNumber SET Inventory.[Drawing#] = [SQ WorkorderNumber NewValve].[Drawing#], Inventory.WorkorderNumber = [SQ WorkorderNumber NewValve].[WorkorderNumber], Inventory.Description = [SQ WorkorderNumber NewValve].[Description], Inventory.Material = [Workorders].[Material], Inventory.Quantity = [Workorders].[Quantity], Inventory.Remarks = [Workorders].[Remarks], Inventory.[Distinguishing Mark] = "changed"
WHERE (((Inventory.InventoryID)=[Forms]![First empty InventoryID]![InventoryID]));
Any idea? At least I get the 100 records now...
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,886
| | | re: need help with update query
Quote:
Originally Posted by arion I tried something else. If I do:
SELECT Left([Model#],5) & [Abbreviation] AS [Drawing#], [Forms]![Sales Form].[SalesID] & [WOIndex] AS WorkorderNumber, WorkorderNewValve.Part AS Description
FROM WorkorderNewValve, Workorders INNER JOIN [SQ Customers and Sales] ON Workorders.SalesID = [SQ Customers and Sales].SalesID
WHERE (((Workorders.WorkorderNumber) Like [Forms]![Workorder Form]![WorkorderNumber]));
it is showing me the 100 records.
Your problems are 1:
Your tables don't have proper relationships created between them and if you don't resolve this problem you are going to continue to have problems like this. The fact that the above query had a FULL OUTER JOIN, which is what happens when you don't put any join between two tables, means you have no way to relate the WorkorderNewValve table to the other tables in your database. So the fact that it is returning the 100 records you want worries me. Quote:
WHERE (((Inventory.InventoryID)=[Forms]![First empty InventoryID]![InventoryID])); This query basicly says to update the records in the Inventory table where the InventoryID is the same as it is on form "First empty InventoryID" in the control "InventoryID". This is only one value so only one record can be updated at best. Quote:
Workorders INNER JOIN Inventory ON Workorders.WorkorderNumber = Inventory.WorkorderNumber You have repeated said that Inventory.WorkorderNumber has no value in it so how can you join on it.
I don't see any way without using VBA recordset code that you can achieve what you are trying to do.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
