×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Update data directly in select query recordset

Update data directly in select query recordset

Update data directly in select query recordset

(OP)
Hello,
Is there any way to modify this select query so it would be able to edit data?
All three tables ([NAMES], [WHOURS], [PAYPER]) used in this query has a primary key defined.

CODE --> sql

SELECT NAMES.FULL_NAME, WHOURS.WDATE, WHOURS.WTYPE, WHOURS.WHRS, WHOURS.PCODE, WHOURS.WRATE, WHOURS.HIP
FROM [NAMES], [WHOURS], [PAYPER]
WHERE (((NAMES.EMPID)=[WHOURS].[EMPID]) AND ((PAYPER.PAYPER)=[Enter pay period mmm dd-dd:]) AND ((WHOURS.PAYPER)=[PAYPER].[PPID])); 

sample data from this query:

CODE -->

FULL_NAME	WDATE	WTYPE	WHRS	PCODE	WRATE	HIP
MONDOR, ADAM	2/27/19	R/T	8	55	37.05	61
MONDOR, ADAM	2/27/19	O/T	2	55	37.05	62
MONDOR, ADAM	2/28/19	R/T	8	55	37.05	63
MONDOR, ADAM	2/28/19	O/T	2	55	37.05	64
FOERTER, MYLES	2/21/19	R/T	7	55	16	65
FOERTER, MYLES	2/22/19	R/T	7	55	16	66
FOERTER, MYLES	2/25/19	R/T	7.25	55	16	67 

PS: I found this article '15 Reasons why You Sometimes Cannot Edit Data in an Access Query'
Link
Is #7 my problem? But I need all three tables to get a correct data.

RE: Update data directly in select query recordset

Your tables aren't joined in the FROM statement. This might be editable if you included joins on primary key fields.

Do you need to add records? Do fields in all tables need to be added/edited?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update data directly in select query recordset

(OP)
Thanks for your reply; no, I don't need to add records, I would just need to edit a couple fields (WTYPE, WHRS) in one table (WHOURS).

RE: Update data directly in select query recordset

You didn't provide much information on your primary keys or relationships. Are PAYPER.PPID and NAMES.EMPID primary keys?

I would edit in a form based on WHOURS with combo boxes for the related field values you need to view.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update data directly in select query recordset

(OP)
Sorry for missing info; primary keys are NAMES.ID, WHOURS.HIP, PAYPER.PPID

RE: Update data directly in select query recordset

If ID is the primary key of the NAMES table, I would think you would be storing that value in the WHOURS table.

Ideally you would use a form for this data entry and have the only table in the record source of WHOURS.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update data directly in select query recordset

(OP)
Yes, NAMES.ID is stored in the WHOURS table, this select query shows just names for easy identification.

I also try to modify this query using all fields (see below) but still wasn't able to edit data.

CODE --> sql

SELECT * FROM [NAMES], [WHOURS], [PAYPER]
WHERE (((NAMES.EMPID)=[WHOURS].[EMPID]) AND ((PAYPER.PAYPER)=[Enter pay period mmm dd-dd:]) AND ((WHOURS.PAYPER)=[PAYPER].[PPID])); 

RE: Update data directly in select query recordset

You need to abandon thinking you can edit this query. It is simply not possible if your tables are not joined by a primary key in the FROM clause. You should not be creating your "joins" in the WHERE clause.

Why are you not using a continuous form? You can use combo boxes to display information from tables other than the WHOURS table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update data directly in select query recordset

(OP)
Well, my idea was to use this query as input data for a datagrid in vb6 program. Will have to find a different way.
Thanks anyway for your help.

RE: Update data directly in select query recordset

So this question is about vb6 data entry?

Did you every try to JOIN the tables in the query design view?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update data directly in select query recordset

(OP)
Not exactly vb6 data entry, just to review with an option to edit.

Yes, the JOIN the tables in the query design view makes the trick!
Thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close