Read Only Query

[Guest]

I am trying to join two tables. Each table has an account in it multiple
times. I can get it to join by account number but it then becomes a read
only query. I can not enter information in it. I keep thinking that because
the account number is in each table multiple times, the tables need to have a
many to many relationship. I have been away from this for awhile so I am
feeling a little rusty. Any ideas no how to join these tables in a query and
be able to edit the query. Any help would be greatly appreciated.

 

[OfficeDev18 via AccessMonster.com]

Does the query remain read-only when you add the DISTINCTROW keyword?

 

[Guest]

I'm sorry, I guess that I don't know what you mean. Is that in the
properties? Do you mean Unique Records. It is set to yes. When you go in
to the SQL format, it does say DISTINCTROW.

 

[OfficeDev18 via AccessMonster.com]

You answered my question.
That being the case, I'll give you a brief explanation why the query is read-
only. Modifications are only meant to be accomplished in a table, nowhere
else. However, in a non-totals query (the Totals icon is not depressed),
Access allows changes to be made also. However, in a totals query, which is
your situation, Access doesn't allow changes to be made because of the
possibility of corrupting data. This is called a non-updateable query in
Access-ese.

There is one way you can still make this happen. You have two tables, and you
want to make changes to the data in one of those tables. In that case and
ONLY in that case, what you can do is create a new table from the result of a
totals make-table query based on the table you're NOT changing. Once you
create this new table, which will now provide you with one-to-many
relationships (to the other table) instead of many-to-many, open the query
designer and bring in the table needing modification first, and then bring in
the new table, and create your relationship(s). You should now have an
updateable query.

HTH

I'm sorry, I guess that I don't know what you mean. Is that in the
properties? Do you mean Unique Records. It is set to yes. When you go in
to the SQL format, it does say DISTINCTROW.

Does the query remain read-only when you add the DISTINCTROW keyword?

[quoted text clipped - 5 lines]

 

[Guest]

I am not sure that I understand your explination on how to create the tables
but I will look at that. However, this is not a total query. It is a select
query. What I am trying to do is join two tables that both have accounts in
them multiple times. When they find the accounts in the query, they need to
enter expenses in the appropriate record. The same account can be in both
tables more then once. I need it to match up so that they can look at the
closed date from the one table so they can make sure they enter expenses in
on the appropriate account from the other table. I am wondering if I need to
have two joins, however I do not have two identical fields, or do I need to
create a many to many relationship. I believe what you gave me is an
explanation on how do to that. Am I right.

You answered my question.
That being the case, I'll give you a brief explanation why the query is read-
only. Modifications are only meant to be accomplished in a table, nowhere
else. However, in a non-totals query (the Totals icon is not depressed),
Access allows changes to be made also. However, in a totals query, which is
your situation, Access doesn't allow changes to be made because of the
possibility of corrupting data. This is called a non-updateable query in
Access-ese.

There is one way you can still make this happen. You have two tables, and you
want to make changes to the data in one of those tables. In that case and
ONLY in that case, what you can do is create a new table from the result of a
totals make-table query based on the table you're NOT changing. Once you
create this new table, which will now provide you with one-to-many
relationships (to the other table) instead of many-to-many, open the query
designer and bring in the table needing modification first, and then bring in
the new table, and create your relationship(s). You should now have an
updateable query.

HTH

I'm sorry, I guess that I don't know what you mean. Is that in the
properties? Do you mean Unique Records. It is set to yes. When you go in
to the SQL format, it does say DISTINCTROW.

Does the query remain read-only when you add the DISTINCTROW keyword?

[quoted text clipped - 5 lines]

feeling a little rusty. Any ideas no how to join these tables in a query and
be able to edit the query. Any help would be greatly appreciated.

 

[Guest]

I am trying to join two tables. Each table has an account in it multiple
times. I can get it to join by account number but it then becomes a read
only query. I can not enter information in it. I keep thinking that because
the account number is in each table multiple times, the tables need to have a
many to many relationship. I have been away from this for awhile so I am
feeling a little rusty. Any ideas no how to join these tables in a query and
be able to edit the query. Any help would be greatly appreciated.

The many records in each table will indeed cause problems. If you have six
records in TableA, and five in TableB, a query referencing both tables will
display thirty records for that account number - all possible combinations.
Access will not have any way to determine WHICH table you want to update,
because any one record in the first table will be paired with five or six
records in the other table.

You need some logical way to uniquely identify which record is to be
updated. What is the meaning of "an account" in each of these tables? Is
there an Accounts table for which AccountNo is the Primary Key? How (in real
life) would you identify which record in which table needs to be updated?

 

[OfficeDev18 via AccessMonster.com]

If you follow my advice, you won't need a many-to-many relationship. You will
be making a new table with each account number only once. Therefore, you will
need only a one-to-many relationship.

Over and above this entire conversation, as it were, you do know what the
underlying problem is, don't you? Your data is not normalized. If your data
were properly normalized, you wouldn't have a problem with many-to-many at
all. If you want to come up with a long-term solution, and one that will make
your work easier in future projects, read up on data normalization in the
help file.

However, if you look at what I'm telling you, I'm leading you in a similar
path, that is, a way to provide a one-to-many relationship by isolating
duplicate information.

HTH

I am not sure that I understand your explination on how to create the tables
but I will look at that. However, this is not a total query. It is a select
query. What I am trying to do is join two tables that both have accounts in
them multiple times. When they find the accounts in the query, they need to
enter expenses in the appropriate record. The same account can be in both
tables more then once. I need it to match up so that they can look at the
closed date from the one table so they can make sure they enter expenses in
on the appropriate account from the other table. I am wondering if I need to
have two joins, however I do not have two identical fields, or do I need to
create a many to many relationship. I believe what you gave me is an
explanation on how do to that. Am I right.

You answered my question.
That being the case, I'll give you a brief explanation why the query is read-

[quoted text clipped - 26 lines]

 

[Guest]

The account number is not the primary key in either of the tables. I have
auto numbers as primary keys. The reason that an account is in there more
the once is that an account can be worked more then once. We can get rid of
it but it could come back. Our primary table does have account number as
primary key because we only want on open account worked at a time. However,
once it is closed, it is moved to the closed table. That is where it can be
in there more then once. We also have a closed expense table where the
account can be in there more then once. These are the tables that I am
trying to join. The close table has a closed date in it and the expense
table has a archive date in it. They are not always the same date but they
are very close. That is how we tell what account gets the expense.

 

[Guest]

I know that the data is not normalized. This is a database that was
inherited and I am trying to put some fixes in. I do plan on totaly
restructuring the database but I am waiting for the project to be approved.
In the mean time, I am trying to make what I have work. I understand what
you are saying but right now I need the account number in the query more then
once. That way we can look at the closed date and know what account to post
the expense to. I am going to try a different way of joining them.

If you follow my advice, you won't need a many-to-many relationship. You will
be making a new table with each account number only once. Therefore, you will
need only a one-to-many relationship.

Over and above this entire conversation, as it were, you do know what the
underlying problem is, don't you? Your data is not normalized. If your data
were properly normalized, you wouldn't have a problem with many-to-many at
all. If you want to come up with a long-term solution, and one that will make
your work easier in future projects, read up on data normalization in the
help file.

However, if you look at what I'm telling you, I'm leading you in a similar
path, that is, a way to provide a one-to-many relationship by isolating
duplicate information.

HTH

I am not sure that I understand your explination on how to create the tables
but I will look at that. However, this is not a total query. It is a select
query. What I am trying to do is join two tables that both have accounts in
them multiple times. When they find the accounts in the query, they need to
enter expenses in the appropriate record. The same account can be in both
tables more then once. I need it to match up so that they can look at the
closed date from the one table so they can make sure they enter expenses in
on the appropriate account from the other table. I am wondering if I need to
have two joins, however I do not have two identical fields, or do I need to
create a many to many relationship. I believe what you gave me is an
explanation on how do to that. Am I right.

You answered my question.
That being the case, I'll give you a brief explanation why the query is read-

[quoted text clipped - 26 lines]

feeling a little rusty. Any ideas no how to join these tables in a query and
be able to edit the query. Any help would be greatly appreciated.