Serious Query Problem for Inexperienced Access user -- Help Please

[John Baker]

Hi:

As those who have looked at this newsgroup recently will realize, I am a neophyte with
Access, although I have experienced with Approach (the Lotus product). There are things I
could easily do in Approach that appear to require some subtle and complex manipulation in
Access. Herein lies my present problem.

I have a number of tables, including one called "tblPO", which contains purchase orders.
Some of the details of these change over time, and I want to be able to programmaticall y
select all the purchase orders for a specific client, key in changes, and go on my way. I
have developed a query which will pull out all the record for a given client, but that
query wont let me update the purchase order data.

The query in question consists of a table (setup) which contains the Client ID for the
client whose POs I want to update. I am using a standard query with he tblPO table and the
setup table, and this neatly produces an output that shows all the purchase orders. I want
to be able to change the data in these purchase orders, and it just wont let me. I tried
using a Update query, and that didn't work! So I am flummoxed. I have been through two
manuals and still don't quite understand what's happening and why i cant do updating and,
most important, what I can do to make this do what I need to. There is something
fundamental that I am missing and am desperate for help!

Thanks in advance

John Baker

[DFS]

John,

Post your table structures and someone can more easily help you.
"John Baker" <Ba******@Veriz on.net> wrote in message
news:uk******** *************** *********@4ax.c om...

Hi:

As those who have looked at this newsgroup recently will realize, I am a neophyte with Access, although I have experienced with Approach (the Lotus product). There are things I could easily do in Approach that appear to require some subtle and complex manipulation in Access. Herein lies my present problem.

I have a number of tables, including one called "tblPO", which contains purchase orders. Some of the details of these change over time, and I want to be able to programmaticall y select all the purchase orders for a specific client, key in changes, and go on my way. I have developed a query which will pull out all the record for a given client, but that query wont let me update the purchase order data.

The query in question consists of a table (setup) which contains the Client ID for the client whose POs I want to update. I am using a standard query with he tblPO table and the setup table, and this neatly produces an output that shows all the purchase orders. I want to be able to change the data in these purchase orders, and it just wont let me. I tried using a Update query, and that didn't work! So I am flummoxed. I have been through two manuals and still don't quite understand what's happening and why i cant do updating and, most important, what I can do to make this do what I need to. There is something fundamental that I am missing and am desperate for help!

Thanks in advance

John Baker

[John Baker]

DFS:
How would I do that? I would be please to try, the tables and things are 7 pages using the
data documentation tools. I suppose I could make a PDF of them, but then where would I
post them?

Regards

John

"DFS" <no******@nospa m.com> wrote:

John,

Post your table structures and someone can more easily help you.
"John Baker" <Ba******@Veriz on.net> wrote in message
news:uk******* *************** **********@4ax. com...

Hi:

As those who have looked at this newsgroup recently will realize, I am a

neophyte with

Access, although I have experienced with Approach (the Lotus product).

There are things I

could easily do in Approach that appear to require some subtle and complex

manipulation in

Access. Herein lies my present problem.

I have a number of tables, including one called "tblPO", which contains

purchase orders.

Some of the details of these change over time, and I want to be able to

programmatical ly

select all the purchase orders for a specific client, key in changes, and

go on my way. I

have developed a query which will pull out all the record for a given

client, but that

query wont let me update the purchase order data.

The query in question consists of a table (setup) which contains the

Client ID for the

client whose POs I want to update. I am using a standard query with he

tblPO table and the

setup table, and this neatly produces an output that shows all the

purchase orders. I want

to be able to change the data in these purchase orders, and it just wont

let me. I tried

using a Update query, and that didn't work! So I am flummoxed. I have been

through two

manuals and still don't quite understand what's happening and why i cant

do updating and,

most important, what I can do to make this do what I need to. There is

something

fundamental that I am missing and am desperate for help!

Thanks in advance

John Baker

[John Baker]

This note is in response to the request for more information about my problem.

The data structures are:

a. tblSetup
This file is N OT linked to anything in the relationship window, and contains a number
of parameters, including a client identification . This is NOT the record key, but rather
the name of the client. This is extracted from a client record on selection and stored in
the setup table.

b tblPO had Purchase order data in it, and has a key and a PO number (a real text field)

c. The query has both files in the query setup, and under the PO Number from the tblPO is
an expression: [tblSetup]![PO Number]. The expression does a fine job of selecting the
records I want to change, BUT I am unable to modify them! The system just wont let me. If
I take out the tblSetup and remove the expression, then I can update fine. If I remove the
expression and leave the setup table, I still cant update the records.

The issue appears to be that you cant update when there is another file in the query
expression, but this makes no sense in the real world, where one to many selection is the
rule rather than the exception. I am missing something, but don't know what!

Help Please

Regards

John Baker
"DFS" <no******@nospa m.com> wrote:

John,

Post your table structures and someone can more easily help you.
"John Baker" <Ba******@Veriz on.net> wrote in message
news:uk******* *************** **********@4ax. com...

Hi:

As those who have looked at this newsgroup recently will realize, I am a

neophyte with

Access, although I have experienced with Approach (the Lotus product).

There are things I

could easily do in Approach that appear to require some subtle and complex

manipulation in

Access. Herein lies my present problem.

I have a number of tables, including one called "tblPO", which contains

purchase orders.

Some of the details of these change over time, and I want to be able to

programmatical ly

select all the purchase orders for a specific client, key in changes, and

go on my way. I

have developed a query which will pull out all the record for a given

client, but that

query wont let me update the purchase order data.

The query in question consists of a table (setup) which contains the

Client ID for the

client whose POs I want to update. I am using a standard query with he

tblPO table and the

setup table, and this neatly produces an output that shows all the

purchase orders. I want

to be able to change the data in these purchase orders, and it just wont

let me. I tried

using a Update query, and that didn't work! So I am flummoxed. I have been

through two

manuals and still don't quite understand what's happening and why i cant

do updating and,

most important, what I can do to make this do what I need to. There is

something

fundamental that I am missing and am desperate for help!

Thanks in advance

John Baker

[John Baker]

This note is in response to the request for more information about my problem.

The data structures are:

a. tblSetup
This file is N OT linked to anything in the relationship window, and contains a number
of parameters, including a client identification . This is NOT the record key, but rather
the name of the client. This is extracted from a client record on selection and stored in
the setup table.

b tblPO had Purchase order data in it, and has a key and a PO number (a real text field)

c. The query has both files in the query setup, and under the PO Number from the tblPO is
an expression: [tblSetup]![PO Number]. The expression does a fine job of selecting the
records I want to change, BUT I am unable to modify them! The system just wont let me. If
I take out the tblSetup and remove the expression, then I can update fine. If I remove the
expression and leave the setup table, I still cant update the records.

The issue appears to be that you cant update when there is another file in the query
expression, but this makes no sense in the real world, where one to many selection is the
rule rather than the exception. I am missing something, but don't know what!

Help Please

Regards

John Baker

[TC]

John, a good way >not< to get help is to post the same question twice within
a few hours, under different subject lines.

I just answered your other one. Now I see that someone else has answered
this one. So I have effectively wasted my time in answering the other one.

Please give everyone >at least a day< to answer your question. Or, post back
to the >original thread<. This is only considerate of those who answer
questions for free.

HTH,
TC
"John Baker" <Ba******@Veriz on.net> wrote in message
news:uk******** *************** *********@4ax.c om...

Hi:

As those who have looked at this newsgroup recently will realize, I am a neophyte with Access, although I have experienced with Approach (the Lotus product). There are things I could easily do in Approach that appear to require some subtle and complex manipulation in Access. Herein lies my present problem.

I have a number of tables, including one called "tblPO", which contains purchase orders. Some of the details of these change over time, and I want to be able to programmaticall y select all the purchase orders for a specific client, key in changes, and go on my way. I have developed a query which will pull out all the record for a given client, but that query wont let me update the purchase order data.

The query in question consists of a table (setup) which contains the Client ID for the client whose POs I want to update. I am using a standard query with he tblPO table and the setup table, and this neatly produces an output that shows all the purchase orders. I want to be able to change the data in these purchase orders, and it just wont let me. I tried using a Update query, and that didn't work! So I am flummoxed. I have been through two manuals and still don't quite understand what's happening and why i cant do updating and, most important, what I can do to make this do what I need to. There is something fundamental that I am missing and am desperate for help!

Thanks in advance

John Baker

[DFS]

"John Baker" <Ba******@Veriz on.net> wrote in message
news:pf******** *************** *********@4ax.c om...

DFS:
How would I do that? I would be please to try, the tables and things are 7 pages using the data documentation tools. I suppose I could make a PDF of them, but then where would I post them?
Just list the name of each table and the field names.
Regards

John

"DFS" <no******@nospa m.com> wrote:

John,

Post your table structures and someone can more easily help you.
"John Baker" <Ba******@Veriz on.net> wrote in message
news:uk******* *************** **********@4ax. com...

Hi:

As those who have looked at this newsgroup recently will realize, I am

aneophyte with

Access, although I have experienced with Approach (the Lotus product).

There are things I

could easily do in Approach that appear to require some subtle and

complexmanipulation in

Access. Herein lies my present problem.

I have a number of tables, including one called "tblPO", which contains

purchase orders.

Some of the details of these change over time, and I want to be able to

programmatical ly

select all the purchase orders for a specific client, key in changes,

andgo on my way. I

have developed a query which will pull out all the record for a given

client, but that

query wont let me update the purchase order data.

The query in question consists of a table (setup) which contains the

Client ID for the

client whose POs I want to update. I am using a standard query with he

tblPO table and the

setup table, and this neatly produces an output that shows all the

purchase orders. I want

to be able to change the data in these purchase orders, and it just

wontlet me. I tried

using a Update query, and that didn't work! So I am flummoxed. I have

beenthrough two

manuals and still don't quite understand what's happening and why i

cantdo updating and,

most important, what I can do to make this do what I need to. There is

something

fundamental that I am missing and am desperate for help!

Thanks in advance

John Baker