What is wrong with my SQL Statement

[Guest]

Can anyone tell me what is wrong with The following Query?
I have two Queries one is base on another.
"Inventory Transactions Query" & "QOH Query"
Inventory Transactions sums all data [UnitsReceived],[UnitsUsed] & [QOH]
Keep in mind that when I post a product in my assembly creation Table it
post UnitsUsed on a seperate row this is why I have one Query based on
another.
The problem is when I try to make QOH Query into an update Query to update
my [Products.QOH] It says:
'UnitsReceivedd]-[UnitsUsed]' is not a valid name. Make sure that it does
not include invalid characters or punctuation and that it is not too long."

In my Query I have
QOH: [UnitsReceived]-[UnitsUsed] and under this in the Update to I have
"Products.QOH"

I tried an SQL Statement but when I try to view it, it tells me there is a
missing Operater in Query expression
'Products.ProductID=Inventory Transactions.ProductID'.
The following is the SQL for this problem:

UPDATE Products INNER JOIN Inventory Transactions ON Products.ProductID =
Inventory Transactions.ProductID SET
Products.QOH = [Inventory Transactions.QOH];

I am trying to make an update Query. I want to update [Products.QOH] With
[Inventory Transactions.QOH] I been working on this for 4 days now.
Thanks in advance.
Alvin

 

[George Nicholson]

Not 100% sure i understand your structure but I think that

In my Query I have
QOH: [UnitsReceived]-[UnitsUsed] and under this in the Update to I have
"Products.QOH"

should be reversed. Products.QOH is at the top, the field you want to
update.

[UnitsReceived]-[UnitsUsed] is the "Update To" value, what you want
Products.QOH to represent when updated.
You may also have to specify the query/table name for those fields in order
to get it to run:
[MyQuery].[UnitsReceived]-[MyQuery].[UnitsUsed]

--
George Nicholson

Remove 'Junk' from return address.

Can anyone tell me what is wrong with The following Query?
I have two Queries one is base on another.
"Inventory Transactions Query" & "QOH Query"
Inventory Transactions sums all data [UnitsReceived],[UnitsUsed] & [QOH]
Keep in mind that when I post a product in my assembly creation Table it
post UnitsUsed on a seperate row this is why I have one Query based on
another.
The problem is when I try to make QOH Query into an update Query to update
my [Products.QOH] It says:
'UnitsReceivedd]-[UnitsUsed]' is not a valid name. Make sure that it does
not include invalid characters or punctuation and that it is not too
long."

In my Query I have
QOH: [UnitsReceived]-[UnitsUsed] and under this in the Update to I have
"Products.QOH"

I tried an SQL Statement but when I try to view it, it tells me there is a
missing Operater in Query expression
'Products.ProductID=Inventory Transactions.ProductID'.
The following is the SQL for this problem:

UPDATE Products INNER JOIN Inventory Transactions ON Products.ProductID =
Inventory Transactions.ProductID SET
Products.QOH = [Inventory Transactions.QOH];

I am trying to make an update Query. I want to update [Products.QOH] With
[Inventory Transactions.QOH] I been working on this for 4 days now.
Thanks in advance.
Alvin

 

[Guest]

Thank George for the reply,
I Don't know how to explain my structure so you can understand it, LOL so I
uploaded it to the Internet.
If you look at it maybe my instructions through it will help you to
understand what I have.
The address is.

http://www.pittmangamecalls.com/Product_Assembly.mdb

The following is where I assign my child parts to my Parent
"Enter/View Assembly Details"
"Assembly Detail"> Bound to my Products
"Kits" >Subform for "Assembly Detail" it list all the
child parts
If you download it you will see what I've been talking about.

when you click on If you click on "Enter/View Order childparts"
you will see the "Units Ordered", "units Received" and "Units Used" in the
subform
and on that main Form you will see txtbox "QOH"
If I manually type in units Received in that text box "QOH" all works fine.
But, If I don't the
Assembly Creations doesn't recognize that value and I can post an assembly
even though I don't have the parts.
Example:
Click on assembly creations then in txtbox "Quanity to make" Type in 1 and
post Transaction will enable but don't click it yet click Exit for now.
Now click on Enter/View & Order Child Parts
In there you will see where I have
Units ordered = 75
Units Received= 75
Units Used = 0
Now, If you click in "QOH" on that form and enter the amount Received from
the subform"75" for first product.
Scroll through the first 4 products and add the amount received to QOH. Now
go to the main Switchboard and click on
"Assembly Creations" and you will see QOH in the subform = 75 for each that
you listed from "Enter/View Order child Products"
If you try to make say 200 of that product it won't let you because there
isn't enough childparts and it recognizes QOH.
But if you erase the QOH from Enter/View Order Child Parts then go back QOH
shows "0" you will be able to Post a transaction
and you will see that value in Inventory Transactions Table.
I have a Backup copy so don't worry about anything you do to this one.
Thanks again and sorry but I didn't really know how to explain it to you
without you looking at it.

It got a little confusing but I hope I explained all well enough.
Thanks again George.
Alvin

Not 100% sure i understand your structure but I think that

In my Query I have
QOH: [UnitsReceived]-[UnitsUsed] and under this in the Update to I have
"Products.QOH"

should be reversed. Products.QOH is at the top, the field you want to
update.

[UnitsReceived]-[UnitsUsed] is the "Update To" value, what you want
Products.QOH to represent when updated.
You may also have to specify the query/table name for those fields in order
to get it to run:
[MyQuery].[UnitsReceived]-[MyQuery].[UnitsUsed]

--
George Nicholson

Remove 'Junk' from return address.

Can anyone tell me what is wrong with The following Query?
I have two Queries one is base on another.
"Inventory Transactions Query" & "QOH Query"
Inventory Transactions sums all data [UnitsReceived],[UnitsUsed] & [QOH]
Keep in mind that when I post a product in my assembly creation Table it
post UnitsUsed on a seperate row this is why I have one Query based on
another.
The problem is when I try to make QOH Query into an update Query to update
my [Products.QOH] It says:
'UnitsReceivedd]-[UnitsUsed]' is not a valid name. Make sure that it does
not include invalid characters or punctuation and that it is not too
long."

In my Query I have
QOH: [UnitsReceived]-[UnitsUsed] and under this in the Update to I have
"Products.QOH"

I tried an SQL Statement but when I try to view it, it tells me there is a
missing Operater in Query expression
'Products.ProductID=Inventory Transactions.ProductID'.
The following is the SQL for this problem:

UPDATE Products INNER JOIN Inventory Transactions ON Products.ProductID =
Inventory Transactions.ProductID SET
Products.QOH = [Inventory Transactions.QOH];

I am trying to make an update Query. I want to update [Products.QOH] With
[Inventory Transactions.QOH] I been working on this for 4 days now.
Thanks in advance.
Alvin

 

[ja]

Alvin

try this:

avoid using spaces. ex. instead of 'Inventory Transactions' make
'Inventory_Transactions' or 'InventoryTransactions'

hope this helps.