Linked Table query (1 Viewer)

[Gismo]

Hi all,

I have a query with 3 tables linked with join fields 2 and 3 as per below
I need to update a table with data from a 4th table but there is no joint field
If the joint between tables 1, 2 and 3 was joint where the fields are equal then there is no problem adding table 4

How do I contenate the below to not link table 4 but be able to update a table from table 4

I need to update the currency field

UPDATE DISTINCTROW ([Transactions - Procurement - Temp] LEFT JOIN Products ON [Transactions - Procurement - Temp].Product = Products.[Short Description]) LEFT JOIN Transactions ON Products.[Product ID] = Transactions.[Product ID] SET [Transactions - Procurement - Temp].UOM = [Products]![UOM], [Transactions - Procurement - Temp].[Mass Required] = [Products]![Mass Required], [Transactions - Procurement - Temp].[Price per Unit] = [Transactions]![Price per Unit], [Transactions - Procurement - Temp].[Exchange Rate] = [Transactions - Procurement - Temp]![Exchange Rate], [Transactions - Procurement - Temp Header]![Currency] = [Transactions - Procurement - Temp Header]![Currency]
WHERE ((([Transactions - Procurement - Temp].Product)=[Forms]![PurchaseOrder_GRV]![PurchaseOrderGRVSubSorm].[Form]![Product]) AND ((Transactions.[Transaction Type Ind])=3));

 

[Minty]

If you constrain the records to have a specific value in the far right-hand table ( Transaction Type Ind]=3 ) then there is no point having the outer joins as it will only return matching records in all three tables anyway.

 

[XPS35]

I need to update a table with data from a 4th table but there is no joint field

Please be more clear.
Which table do you want to update?
If you want to update one table, why do you make a query with 3 tables?
If there is no joint field in "table4" how can you find the right value?
But most important: explain the purpose of the tables and the logic behind the update you want to do.

 

[Gismo]

If you constrain the records to have a specific value in the far right-hand table ( Transaction Type Ind]=3 ) then there is no point having the outer joins as it will only return matching records in all three tables anyway.

Please be more clear.
Which table do you want to update?
If you want to update one table, why do you make a query with 3 tables?
If there is no joint field in "table4" how can you find the right value?
But most important: explain the purpose of the tables and the logic behind the update you want to do.

Table 4 only has one record, that is the currency value I need to update in the Transactions - Procurement - Temp table

I am gathering information from different tables to update just the one table to enable me to do calculations based on those values updated

If I have joint tables which are not equal to the other then I can not add the 4th table because of ambiguous joints

I am looking into have just one query instead of forst gathering the information into one query and then to update from the 4th table

 

[XPS35]

UPDATE [Transactions - Procurement - Temp table] SET Currency = DLookup("Currency", "Table4")?

 

[Gismo]

UPDATE [Transactions - Procurement - Temp table] SET Currency = DLookup("Currency", "Table4")?

It is still looking for the table to be present in the query and also a data type mismatch

 

[XPS35]

I posted a complete query. Not just a part of your query.

 

[Gismo]

I posted a complete query. Not just a part of your query.

Edited

 

[Pat Hartman]

You need to back up a few steps and start by fixing the schema.

1. the relationship between the temp table and Products is incorrect. the temp table needs the ProductID and that is the field you need to join (not joint) on.
2. the temp table has no PK
3. the Transactions table has no PK
4. the fourth table has no PK
5. It also looks like you have no RI enforced but that is probably because you don't have any PKs defined
6. there is no way to update a record in a table unless you have matching join fields with the table that has the replacement value. So, if you want to replace the Currency value in the temp table with the one from the fourth table, then the temp table needs VendorID and Location on which to join.
7. Transactions has VendorID but the fourth table doesn't, it has Vendor but it needs VendorID

You need to rethink the whole schema before you move on.

Also, proper object names are comprised of the letters a-z and A-Z, the numbers 0-9, and the underscore. NO OTHER CHARACTERS including embedded spaces. Your object names should conform to the VBA rules for defining variable names. They should also avoid like the plague any names of functions like Month, Year, Date or properties - especially Name since EVERY object has a Name property and that will conflict with a column named "Name". What does Me.Name refer to? The name of the form object or the name of your "Name" field. You will not be happy with the answer.

 

[Gismo]

You need to back up a few steps and start by fixing the schema.

1. the relationship between the temp table and Products is incorrect. the temp table needs the ProductID and that is the field you need to join (not joint) on.
2. the temp table has no PK
3. the Transactions table has no PK
4. the fourth table has no PK
5. It also looks like you have no RI enforced but that is probably because you don't have any PKs defined
6. there is no way to update a record in a table unless you have matching join fields with the table that has the replacement value. So, if you want to replace the Currency value in the temp table with the one from the fourth table, then the temp table needs VendorID and Location on which to join.
7. Transactions has VendorID but the fourth table doesn't, it has Vendor but it needs VendorID

You need to rethink the whole schema before you move on.

Also, proper object names are comprised of the letters a-z and A-Z, the numbers 0-9, and the underscore. NO OTHER CHARACTERS including embedded spaces. Your object names should conform to the VBA rules for defining variable names. They should also avoid like the plague any names of functions like Month, Year, Date or properties - especially Name since EVERY object has a Name property and that will conflict with a column named "Name". What does Me.Name refer to? The name of the form object or the name of your "Name" field. You will not be happy with the answer.

Thank you for the info

I did not add PK to the temp files as I did not think it would be used
My transaction file I am updating does have the PK
I mad all the amendments as sugested

Thank you very much

 

[Pat Hartman]

Having PKs for all tables is good practice. Where is the temp data coming from, were you able to get the necessary join fields in the tables?
OK, can you make the query work now?

 

[Gismo]

Having PKs for all tables is good practice. Where is the temp data coming from, were you able to get the necessary join fields in the tables?
OK, can you make the query work now?

Yes thank you Pat

All seems to be ok now
The temp data is when a new purchase order is raised then transferred to the transactions table

I will take the PK on all tables in consideration and adapt

Thank you all for the advise

 

[Pat Hartman]

So, you got the necessary join fields added?