3 tables

[Guest]

I currently have a query that runs matching data from one table to either one
table or the other i.e. Serial ID matches that in Table 2 or Table 3.
I need to edit this data within the query but it will not let me.
I have links in place already, but Serial ID is not a primary key.

Any ideas as to how to solve this problem?
Cheers

 

[Kevin Jameson]

you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin

 

[Jeff Boyce]

Disregard the "you cant do this" message. It is SPAM (or worse), and has
been showing up all over the newsgroups.

We're not there ... I can't tell what you mean by "runs data from one table
to either one table or the other"

What kind of data do you have in your three tables?

How are the tables related to each other?

What is the SQL of your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for the note about Kevin,

This is the SQL behind the query and even if I just open the query as it is
i can't edit anything and I get the message 'this recordset is not updatable'
at the bottom upon opening:

SELECT [All T&M Data].[Request ID], [All T&M Data].[First Name], [All T&M
Data].Surname, [All T&M Data].[Invoiced?], [All T&M Data].[Agent ID] AS
[Assigned To], [All T&M Data].[Serial ID], [All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP, IIf(IsNull(PO.[Serial
ID]),'N','Y') AS PO
FROM ([All T&M Data] LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial
ID]) LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

Many Thanks,
Matt

 

[Neil Sunderland]

This is the SQL behind the query and even if I just open the query as it is
i can't edit anything and I get the message 'this recordset is not updatable'
at the bottom upon opening:

SELECT
[All T&M Data].[Request ID],
[All T&M Data].[First Name],
[All T&M Data].Surname,
[All T&M Data].[Invoiced?],
[All T&M Data].[Agent ID] AS [Assigned To],
[All T&M Data].[Serial ID],
[All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO
FROM
([All T&M Data]
LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial ID])
LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE
(((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

The IIF(IsNull...) gives it away. Since you are returning records that
do not have a unique match across all the tables, you will never be
able to make an updateable query from this.

 

[Guest]

Right ok,

If I were to delete the IIf functions, would there be a way to write around
it and make it updateable?

Matt

This is the SQL behind the query and even if I just open the query as it is
i can't edit anything and I get the message 'this recordset is not updatable'
at the bottom upon opening:

SELECT
[All T&M Data].[Request ID],
[All T&M Data].[First Name],
[All T&M Data].Surname,
[All T&M Data].[Invoiced?],
[All T&M Data].[Agent ID] AS [Assigned To],
[All T&M Data].[Serial ID],
[All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO
FROM
([All T&M Data]
LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial ID])
LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE
(((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

The IIF(IsNull...) gives it away. Since you are returning records that
do not have a unique match across all the tables, you will never be
able to make an updateable query from this.

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[David Cox]

The calculated fields have got to go.

In Access 2007 the help section "Edit data in a query" is very helpful. It
may be so in the version you are running.

Right ok,

If I were to delete the IIf functions, would there be a way to write
around
it and make it updateable?

Matt

This is the SQL behind the query and even if I just open the query as it
is
i can't edit anything and I get the message 'this recordset is not
updatable'
at the bottom upon opening:

SELECT
[All T&M Data].[Request ID],
[All T&M Data].[First Name],
[All T&M Data].Surname,
[All T&M Data].[Invoiced?],
[All T&M Data].[Agent ID] AS [Assigned To],
[All T&M Data].[Serial ID],
[All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO
FROM
([All T&M Data]
LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial ID])
LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE
(((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

The IIF(IsNull...) gives it away. Since you are returning records that
do not have a unique match across all the tables, you will never be
able to make an updateable query from this.

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[Guest]

David,

I have tried looking at the Access 2003 version of it but to no avail.

The calculated fields are a big part of how the agents want the report laid
out.
Can you think of any other way in which i can assign Agents to a particular
record yet making it update to that query?

The calculated fields have got to go.

In Access 2007 the help section "Edit data in a query" is very helpful. It
may be so in the version you are running.

Right ok,

If I were to delete the IIf functions, would there be a way to write
around
it and make it updateable?

Matt

Matt Dawson wrote:
This is the SQL behind the query and even if I just open the query as it
is
i can't edit anything and I get the message 'this recordset is not
updatable'
at the bottom upon opening:

SELECT
[All T&M Data].[Request ID],
[All T&M Data].[First Name],
[All T&M Data].Surname,
[All T&M Data].[Invoiced?],
[All T&M Data].[Agent ID] AS [Assigned To],
[All T&M Data].[Serial ID],
[All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO
FROM
([All T&M Data]
LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial ID])
LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE
(((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

The IIF(IsNull...) gives it away. Since you are returning records that
do not have a unique match across all the tables, you will never be
able to make an updateable query from this.

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[David Cox]

In theory any query that has joins should not be updatable because changing
the data could invalidate the join. Access is more liberal than that. I am
not sufficiently versed in all of the rules it applies, which are sometimes
hidden deep within, to give an off-the cuff answer. I would have to sit and
experiment.

It seems to me that you could put the calculated fields on a subform and use
a linked query for that bit. If the joins still cause problems I might try
queries in the where clause.instead of joins e.g. WHERE [key] in (SELECT X
FROM .....)
I usually end up with something that works, but it is often trial and error
in my case.

David,

I have tried looking at the Access 2003 version of it but to no avail.

The calculated fields are a big part of how the agents want the report
laid
out.
Can you think of any other way in which i can assign Agents to a
particular
record yet making it update to that query?

The calculated fields have got to go.

In Access 2007 the help section "Edit data in a query" is very helpful.
It
may be so in the version you are running.

Right ok,

If I were to delete the IIf functions, would there be a way to write
around
it and make it updateable?

Matt

:

Matt Dawson wrote:
This is the SQL behind the query and even if I just open the query as
it
is
i can't edit anything and I get the message 'this recordset is not
updatable'
at the bottom upon opening:

SELECT
[All T&M Data].[Request ID],
[All T&M Data].[First Name],
[All T&M Data].Surname,
[All T&M Data].[Invoiced?],
[All T&M Data].[Agent ID] AS [Assigned To],
[All T&M Data].[Serial ID],
[All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO
FROM
([All T&M Data]
LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial ID])
LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE
(((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

The IIF(IsNull...) gives it away. Since you are returning records that
do not have a unique match across all the tables, you will never be
able to make an updateable query from this.

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address