Update data directly in select query recordset 1

[JoPaBC]

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.

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:

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.

 

[dhookom]

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

 

[JoPaBC]

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).

 

[dhookom]

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

 

[JoPaBC]

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

 

[dhookom]

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

 

[JoPaBC]

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.

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

 

[dhookom]

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

 

[JoPaBC]

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.

 

[dhookom]

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

 

[JoPaBC]

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!