By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,967 Members | 1,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,967 IT Pros & Developers. It's quick & easy.

need help with update query

P: 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
Oct 18 '06 #1
Share this Question
Share on Google+
46 Replies


Expert 100+
P: 1,221
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
Oct 19 '06 #2

P: 23
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
Oct 19 '06 #3

NeoPa
Expert Mod 15k+
P: 31,347
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
Expand|Select|Wrap|Line Numbers
  1. WHERE (((Inventory.InventoryID) Like "*" & [Forms]![First empty InventoryID]![InventoryID] & "*"));
as the last line.
If not, the last line should read
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 19 '06 #4

P: 23
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?
Oct 19 '06 #5

NeoPa
Expert Mod 15k+
P: 31,347
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.
Oct 19 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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];
Oct 20 '06 #7

P: 23
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]);
Oct 20 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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'
Oct 20 '06 #9

P: 23
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.
Oct 20 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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]);
Oct 20 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
By the way

The 'First empty InventoryID' form has to be open when this query is running.
Oct 20 '06 #12

P: 23
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".
Oct 20 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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]);
Oct 20 '06 #14

P: 23
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.
Oct 20 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
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]);



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.
Oct 20 '06 #16

P: 23
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.
Oct 20 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
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.


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.
Oct 20 '06 #18

P: 23
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.
Oct 20 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534

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.
Oct 20 '06 #20

P: 23
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?
Oct 20 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534

WorkorderNumber is built from salesID+WOIndex
Can I get examples of the structure and content of both these fields.
Oct 20 '06 #22

P: 23
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.
Oct 20 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
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.


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?
Oct 20 '06 #24

P: 23
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.
Oct 23 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
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],
Oct 23 '06 #26

P: 23
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;
Oct 23 '06 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 23 '06 #28

P: 23
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?
Oct 23 '06 #29

MMcCarthy
Expert Mod 10K+
P: 14,534
The only other way to do this is using recordsets in vba code. Leave it with me.
Oct 23 '06 #30

MMcCarthy
Expert Mod 10K+
P: 14,534
Which tables are the following two fields from:

[Model#]
[Abbreviation]
Oct 23 '06 #31

MMcCarthy
Expert Mod 10K+
P: 14,534
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');
Oct 23 '06 #32

P: 23
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;
Oct 24 '06 #33

MMcCarthy
Expert Mod 10K+
P: 14,534
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');
Oct 25 '06 #34

P: 23
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.
Oct 25 '06 #35

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 25 '06 #36

P: 23
OK, I did the two queries. They were running without error messages but it is not showing me any records.
Oct 26 '06 #37

MMcCarthy
Expert Mod 10K+
P: 14,534
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;
Oct 26 '06 #38

P: 23
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]
Oct 27 '06 #39

P: 23
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.
Oct 27 '06 #40

P: 23
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]));
Oct 27 '06 #41

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 27 '06 #42

P: 23
No, it gives me no records at all.
Oct 30 '06 #43

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 30 '06 #44

P: 23
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.
Oct 30 '06 #45

P: 23
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...
Oct 30 '06 #46

MMcCarthy
Expert Mod 10K+
P: 14,534
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.


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.


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.
Oct 30 '06 #47

Post your reply

Sign in to post your reply or Sign up for a free account.