Multiple Query Edit

[Jeremy Dove]

I am working on a dbse and i have the following problem.

I have the following tables

contract
service
housetype

on contract i have contract_number which is the primary
key.

On contract i can select housetype.

on housetype i have multiple model# for different
options.

on service i have contract_number as well as the serial #
for the model# in housetype.

What i am trying to do is this

I have a 1 to many relationship between contract and
service.

i have a form for service in which i have the serial# to
be inputted however i want the model# displayed based on
the housetype selected on contract.

I can get this to work but i can't edit. the only thing i
can figure out is because there is an inner join to make
this work.

Can anyone tell me how to get this to work. Thanks

 

[Michel Walsh]

Hi,


Inner join does not, by itself, broke updateability of a query.
Invoices, in Northwind, has 5 inner joins and allows to edit its data! The
problem is probably in you SQL statement itself. Does it uses DISTINCT, an
aggregate (SUM, COUNT, MIN, MAX, FIRST, LAST, ... ) or do you use UNION?
Furthermore, if you are in MS SQL Server, do your tables have a primary key?
and you can't use TOP either, in that case, or you may have to specify in
the form, which table would be updated (UniqueTable property) (Access and
MS SQL Server version dependant).

In short, if you can post the SQL statement of your query supporting
your form, that may help, maybe...



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks fir the response.

Here is the sql statment.

SELECT main.Plan_number, main.Builder, main.Subdivision,
main.Section, main.Housetype, main.Lot, main.County,
main.opt1, main.opt2, main.opt3, main.opt4, main.opt5,
main.opt6, main.opt7, main.opt8, main.opt9, main.opt10,
main.opt11, main.opt12, main.opt13, main.opt14,
main.opt15, main.opt16, main.opt17, main.opt18,
main.opt19, main.opt20, housetype.z1_furn_model,
housetype.z1_coil_model, housetype.z1_ac_model,
housetype.z2_furn_model, housetype.z2_coil_model,
housetype.z2_ac_model, housetype.z3_furn_model,
housetype.z3_coil_model, housetype.z3_ac_model,
housetype.z4_furn_model, housetype.z4_coil_model,
housetype.z4_ac_model, housetype.z5_furn_model,
housetype.z5_coil_model, housetype.z5_ac_coil,
service.z1_serial_furn, service.z1_location_furn,
service.z1_serial_coil, service.z1_location_coil,
service.z1_serial_ac, service.z1_location_ac,
service.z2_serial_furn, service.z2_location_furn,
service.z2_serial_coil, service.z2_location_coil,
service.z2_serial_ac, service.z2_location_ac,
service.z3_serial_furn, service.z3_location_furn,
service.z3_serial_coil, service.z3_location_coil,
service.z3_serial_ac, service.z3_location_ac,
service.humid_z1_model, service.humid_z1_install_date,
service.humid_z1_tech, service.humid_z2_model,
service.humid_z2_install_date, service.humid_z2_tech,
service.humid_z3_model, service.humid_z3_install_date,
service.humid_z3_tech, service.elecac_z1_model,
service.elecac_z1_install_date, service.elecac_z1_tech,
service.elecac_z2_model, service.eleac_z2_install_date,
service.elecac_z2_tech, service.elecac_z3_model,
service.elecac_z3_install_date, service.elecac_z3_tech,
service.ptstat_z1_model, service.ptstat_z1_install_date,
service.ptstat_z1_tech, service.ptstat_z2_model,
service.ptstat_z2_install_date, service.ptstat_z2_tech,
service.ptstat_z3_model, service.ptstat_z3_install_date,
service.ptstat_z3_tech, service.zoning_systems_notes,
service.other_options_notes, service.st_check_comp_date,
service.st_check_tech, service.settle_date,
service.provided_by, service.final_inspected_by,
service.final_results, service.notes,
service.service_history
FROM (housetype INNER JOIN main ON housetype.house_name =
main.Housetype) INNER JOIN service ON main.Plan_number =
service.plan_number;


Let me know what you think

 

[Michel Walsh]

Hi,


Clearly, those inner joins and that query are not involved in the lost
of updateability. Are all these tables or some are queries (if so, are they
themselves updateable)? Can you update through the query (in data view) ?


Vanderghast, Access MVP

 

[Jeremy Dove]

Hey,

They are all tables. They are all also updateable.

No i can't update the query in data view.

The model numbers are just supposed to be for show not
change but the rest are supposed to be able to be updated.

 

[Michel Walsh]

Hi,


I assumed the inner joins are one-to-many. If there are many-to-many
JOIN, then you loose updatability (at least, on some of the fields in case
of multiple tables). If you got, as result:


1 a
1 b
2 a
2 b



where one table supplies the only two records with values 1 and 2
respectively, and the other table supplies a and b, respectively, then,
clearly, changing one "a" in the first row would have to change all the
other rows too, here, the third row, which is not allowed, and the query is
not updateable.



Hoping it may help,
Vanderghast, Access MVO

 

[Jeremy Dove]

That fixed the problem. Thanks

-----Original Message-----
Hi,


I assumed the inner joins are one-to-many. If there are many-to-many
JOIN, then you loose updatability (at least, on some of the fields in case
of multiple tables). If you got, as result:


1 a
1 b
2 a
2 b



where one table supplies the only two records with values 1 and 2
respectively, and the other table supplies a and b, respectively, then,
clearly, changing one "a" in the first row would have to change all the
other rows too, here, the third row, which is not allowed, and the query is
not updateable.



Hoping it may help,
Vanderghast, Access MVO




.