SQL Modify Data

[shamas21]

Hi All

I am unable to modify the data within the following Query. Is this because there are multiple table joined together?
Is there a way around this? Once i receive the data that i retrieve, then i would like to modify them.

SELECT subtable.prodid, subtable.name, customers.forename
FROM customers, subtable
WHERE customers.forename=subtable.name;

Thanks

 

[DCrake]

Simple Software Solutions

You need to create a JOIN between the two tables so that a link is made between the two based on the name. Also don't use the word 'Name' as a field name Access doesn't like it. Check Access help for reserved words and characters.

CodeMaster:

 

[MSAccessRookie]

Actually, there is a "JOIN" here. The query

SELECT subtable.prodid, subtable.name, customers.forename
FROM customers, subtable
WHERE customers.forename=subtable.name;

could also have been written as

SELECT subtable.prodid, subtable.name, customers.forename
FROM customers INNER JOIN subtable ON customers.forename=subtable.name;

Because it is an INNER JOIN, you cannot update the resulting dataset UNLESS all of the columns from BOTH of the tables are selected. It might ALSO work if you change the INNER JOIN to OUTER JOIN

SELECT subtable.prodid, subtable.name, customers.forename
FROM customers OUTER JOIN subtable ON customers.forename=subtable.name;

 

[shamas21]

Actually, there is a "JOIN" here. The query

SELECT subtable.prodid, subtable.name, customers.forename
FROM customers, subtable
WHERE customers.forename=subtable.name;

could also have been written as

SELECT subtable.prodid, subtable.name, customers.forename
FROM customers INNER JOIN subtable ON customers.forename=subtable.name;

Because it is an INNER JOIN, you cannot update the resulting dataset UNLESS all of the columns from BOTH of the tables are selected. It might ALSO work if you change the INNER JOIN to OUTER JOIN

SELECT subtable.prodid, subtable.name, customers.forename
FROM customers OUTER JOIN subtable ON customers.forename=subtable.name;

Hi

I have tried your suggestions but they dont to accept data entry or any modifications. Im embedding a two datasheets tables into a form that is linked by a unique column ID.

for example, if the first table reads Customer: 001 , then the second datasheet at the bottom of the form will bring back all the products that customer 001 purchased. So i wanted to create an SQL where the user selects the customer from the first table in the form (at the top of the form) and it would display the results in the second table (at the bottom part of the form) where it can be modified or new entries added.

It seems to work fine by linking the tables using the child and master fields in the properties section, but why is this? can you give me an understanding how the child and master fields work in the properties section? i cant seem to understand how to do it with an SQL query, so i can embed the query into the data sheet.

Thanks