Help - can you edit data in a query?

[Lisa - NH]

Hi. I have setup different queries to show the Legion Roster, the Sons
Roster, the Auxiliary Roster and a few others items (newsletter list, life
members, etc). I currently don't have forms setup as I'm very slowly working
on things.

Can you go into one of the queries and edit/add data and will it reflect in
the table the data is based on?
Lisa

 

[Jackie L]

Lisa,
This will work if you are using Updateable queries (which has to do with the
joins or if it is an Action query). By opening up your query in datasheet
view, you should be able to test if it can be changed. If you cannot change
it in datasheet view, it is not Updateable.

Hope this helps,
Jackie

 

[Lisa - NH]

Hi Jackie,

Hmm...I have no idea what joins means. When I setup the query, clicked on
Create, Query Design and then input all the fields and other information.
When I right click and view the properties of the query it says "select
query".

I just did a test. I opened the Auxiliary Roster Query and added a field at
the end of it. I had the main table open at this point. When I went to view
on the main table, the information wasn't there. I then closed the table and
opened it again and the datat I had entered did show up.

It looks like it can definately be done but you have to be sure that either
the table is closed while you are doing it, or you close and then re-open the
table to view the changed/added info. Does that sound correct?
Lisa

 

[Jackie L]

Lisa,
I join has to do with a query that has more than one table, so the tables
are joined by common fields.

Yes, your table will not show refreshed data if you keep it open when you
are running a query off the same table. What you should strive to do is do
not work in the tables while you are also working in queries for those same
tables. Spending some time on form design might be helpful. Eventually, you
should be doing all your data entry and reporting off of forms and never
actually see the tables or queries except during programming.

Jackie

 

[John Spencer]

You can edit data in the datasheet view of a query in many circumstances.
However, there are times when a query is not updatable.

When you do edit data in the datasheet view of a query, it is entered into
the table when you go to another record (row) or when you close the query or
when you select Save Record.

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
Select that from the list for an explanation of some of the causes and
alternative solutions.

Access MVP Allen Browne has summarized the reasons:

Query results will be read-only if any of the following apply:
.. It has a GROUP BY clause (totals query).
.. It has a TRANSFORM clause (crosstab query).
.. It contains a DISTINCT predicate.
.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).
.. It involves a UNION.
.. It has a subquery in the SELECT clause.
.. It uses JOINs of different directions on multiple tables in the FROM
clause.
.. The query's Recordset Type property is set to Snapshot.
.. The query is based on another query that is read-only (stacked query.)
.. Your permissions are read-only (Access security.)
.. The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Lisa - NH]

Jackie,

Ok, I understand the concept of joins but have nothing furthur than that
since right now it's just the one main table.

I figured that. Right now, I am sorta looking all over the place at things
and I end up leaving stuff open. Forms is a while off because I'm not done
with the main setup yet. Also I did try in my test file to figure out the
forms and I can't get what I want. I have another post on that subject.
Lisa

 

[Lisa - NH]

Hi John,

Thanks for the info. Unfortunately right now that help wouldn't have helped
me. LOL!
Lisa

 

[Albert D. Kallal]

Hi. I have setup different queries to show the Legion Roster, the Sons
Roster, the Auxiliary Roster and a few others items (newsletter list, life
members, etc). I currently don't have forms setup as I'm very slowly
working
on things.

Can you go into one of the queries and edit/add data and will it reflect
in
the table the data is based on?
Lisa

yes. A query is often called a "view". Even if you use a query on a query,
you can still edit the data, and the changes are reflected in the table.

Those records while retrieved from a query are the LIVE ACTUAL records from
the table. no copy is being made here, and you are directly editing the
data...

I think the others giving you answers here complete miss-understood your
question. Often, people new to ms-access do get the idea that quires are
somehow a copy of the data, and they are not....they directly use and edit
the live actual data from the data.

This means that you can freely base a form on a table, or on a query, and
the form will be update the actual live data for you.

 

[Lisa - NH]

Hi Albert,

Thanks for the response. Even though I'm very new to Access (& databases in
general), I did realize that queries wern't a copy but just a view of the
data that the query is setup for. I've only been using this for a few weeks
now....and not daily either. At first I was at a complete loss as to the
whole query thing. At least now I know how to set up a basic one from one
table and I think I could figure out how to do it if there's more than one
table that I need info from.
Lisa

 

[Tom Wickerath]

Hi Lisa,

and I think I could figure out how to do it if there's more than one
table that I need info from.

Do read up on establishing relationships first, especially the three options
under Join Types:

Defining Relationships Between Tables in a Microsoft Access Database
http://support.microsoft.com/?id=304467


Note: When you access the above link, you will notice that the title of this
KB (Knowledge Base) article includes "ACC2000:", implying that it only
applies to Access 2000. In general, disregard that portion of a title that
indicates a version of Access. Many of these KB article apply equally well to
other versions of Access.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Lisa - NH]

Hi Tom,

I briefly looked at the info. I have saved it as a favorite and will read
it in more detail later. I also found a lot of informaiton yesterday when I
went on a search. A lot of it over my head but some very helpful.
Lisa

 

[Tom Wickerath]

Take it a little bit at a time. Do spend lots of time reading up on database
design. The first two papers at this link, written by Michael Hernandez, are
good introductions to the subject:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Lisa - NH]

Hi Tom,

I'm wishing I had done the research I'm doing now before I had started. I
just sorta jumped right into things. I am definately trying to read simple &
easy to understand information that I can find on the basics, normalization,
tables & queries. I'm not getting into reading about forms yet as I want to
get the table part set up better first. Thanks for the link. I'll check it
out.
Lisa

 

[Tom Wickerath]

Hi Lisa,

You are very wise to wait on designing forms. Many people new to databases
attempt to create tables, based on what they have in mind for a form. The
structure of your database is much like the foundation of a building. Get
that part right, first, before attempting to build the walls, hang doors, etc.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Lisa - NH]

Hi Tom,

I did mess around a little with the forms. Just trying to see what te
different creation methods showed. I was able to get a very simple form but
it wasn't exactly what I wanted. I knew it was best to wait until I had the
tables & queries set first before getting serious about the forms. I haven't
even done any reading/research on them yet.
Lisa