Problem updating query

[Guest]

Hi,

I have a problem updating a query that calls fields from two tables - lined
on a common field. If I remove one of the tables, I can update the query.

Could you please advise how I can change the SQL so that it allows me to
update the query. Here is the query in SQL view:

SELECT [Transaction Log].[Property Address], [Transaction Log].[Transaction
Date], [Transaction Log].[Description of Transaction], [Transaction
Log].[Withdrwawal Amount], [Transaction Log].[Deposit Amount], [Transaction
Log].ID, [Transaction Log].[Vendor ID], [Transaction Log].[Contract ID],
[Transaction Log].[Capital Purchase], [Transaction Log].VAT,
Landlords.[Landlord Name]
FROM [Transaction Log] LEFT JOIN Landlords ON [Transaction Log].[Property
Address] = Landlords.[Property Address]
ORDER BY [Transaction Log].[Transaction Date];

Thanks very much!!

Richard

 

[Sylvain Lafontaine]

The LEFT JOIN might be the problem. You should use sub-forms or the Access
AutoLookup facility or a DLookup in the OnCurrent event for such a case.

May also help if you include the primary key for the table Landlords.

Also, using something like the [Property Address] as a foreigh key (or as
the Landlords' primary key?) might cause you a lot of problems in the
future.

S. L.

 

[John Vinson]

Hi,

I have a problem updating a query that calls fields from two tables - lined
on a common field. If I remove one of the tables, I can update the query.

Could you please advise how I can change the SQL so that it allows me to
update the query. Here is the query in SQL view:

SELECT [Transaction Log].[Property Address], [Transaction Log].[Transaction
Date], [Transaction Log].[Description of Transaction], [Transaction
Log].[Withdrwawal Amount], [Transaction Log].[Deposit Amount], [Transaction
Log].ID, [Transaction Log].[Vendor ID], [Transaction Log].[Contract ID],
[Transaction Log].[Capital Purchase], [Transaction Log].VAT,
Landlords.[Landlord Name]
FROM [Transaction Log] LEFT JOIN Landlords ON [Transaction Log].[Property
Address] = Landlords.[Property Address]
ORDER BY [Transaction Log].[Transaction Date];

As Sylvain says, the Join is the likely source of the problem. This
will be updateable only if there is a unique Index on the [Property
Address] field.

For what it's worth, I would be very dubious about using an address as
a joining field! Unless you maintain strict (downright intrusive) data
entry control, you'll risk missing links (312 W. Main St. is not a
match to 312 W Main St. for example).


John W. Vinson[MVP]

 

[Guest]

Thanks for your advice - which fixed the issue & with regard to the links. I
will have to think about changing them...

Best wishes

Richard

Hi,

I have a problem updating a query that calls fields from two tables - lined
on a common field. If I remove one of the tables, I can update the query.

Could you please advise how I can change the SQL so that it allows me to
update the query. Here is the query in SQL view:

SELECT [Transaction Log].[Property Address], [Transaction Log].[Transaction
Date], [Transaction Log].[Description of Transaction], [Transaction
Log].[Withdrwawal Amount], [Transaction Log].[Deposit Amount], [Transaction
Log].ID, [Transaction Log].[Vendor ID], [Transaction Log].[Contract ID],
[Transaction Log].[Capital Purchase], [Transaction Log].VAT,
Landlords.[Landlord Name]
FROM [Transaction Log] LEFT JOIN Landlords ON [Transaction Log].[Property
Address] = Landlords.[Property Address]
ORDER BY [Transaction Log].[Transaction Date];

As Sylvain says, the Join is the likely source of the problem. This
will be updateable only if there is a unique Index on the [Property
Address] field.

For what it's worth, I would be very dubious about using an address as
a joining field! Unless you maintain strict (downright intrusive) data
entry control, you'll risk missing links (312 W. Main St. is not a
match to 312 W Main St. for example).


John W. Vinson[MVP]