Add Data to table when linked with union query (MSACCESS)

[tasawer]

I need some assistance with SQL query. I will be grateful if you can provide help please.

I have a database that records details of a road traffic accident and name of driver.
sub-form records names of passengers.
The two tables are related with a one-to-many relationship.

I need to view all the names (Drivers and Passengers) as datasheet view and be able to tick a box to show investigation for this particular person is complete.

I have made a union query that is able to list all names as I want them.
I made a third table 'CaseComplete', to hold the additonal information.
however, when linked, I cannot add data to the third table.
My SQL knowledge ends here. :)

tbl_Accident – This lists all drivers involved in an vehicle accident
tbl_acc_Passengers – This lists all passengers associated with the driver.
CaseComplete - This table is new table common to driver and passenger with additonal information related to the person (not the incident).
MMQ_Clients: Union Query

Below is my code:
Profesisonal assistance will be greatly appreciated.

Union Query

SELECT DRV.AccidentId, DRV.ContactID, DRV.AccidentRefFinal, DRV.SolRef
FROM tbl_Accident AS DRV
UNION ALL SELECT PASS.AccidentId, PASS.ContactID, PASS.PassRefFinal, PASS.SolRefP
FROM tbl_Acc_Passengers AS PASS;
 

Union Query Linked with CaseComplete

SELECT C.FirstName & " " & [SurName] AS Client, MMQ_Clients.SolRef, MMQ_Clients.AccidentId, MMQ_Clients.AccidentRefFinal, CaseComplete.CaseCompleteID, CaseComplete.AccidentID, CaseComplete.FileComplete, CaseComplete.DateComplete, CaseComplete.Reason
 
FROM Contacts AS C INNER JOIN (MMQ_Clients LEFT JOIN CaseComplete ON MMQ_Clients.AccidentId = CaseComplete.AccidentID) ON C.ContactID = MMQ_Clients.ContactID
 
ORDER BY MMQ_Clients.AccidentId DESC;
 

[Delerna]

Yes that happens when you join an uneditable query to an editable query. The whole thing becomes uneditable.

To illustrate what I mean
Create a query on a table

select * from thetable
 

Open the query and the records are editable
Edit the query and make it a union of itself

select * from thetable
union all
select * from thetable
 

now open the query again and it is uneditable (and it should be uneditable)
now if you join that query to another query that is editable
the other query is now also uneditable


you will need to rethink what you are doing

[tasawer]

I am still struggling with this issue. Let me put it this way. How can put the two tables together (editable and uneditable) so that I can add data to the editable table.

There must be a solution with coding.

[Delerna]

Of course there is a solution :)
My post wan't meant to discourage you suggesting it is hopeless.
All I meant is that you cannot join an uneditable query to an editable one and expect the result to be editable.

You need to rethink your problem and come up with another way other than using a union query

If I understand you correctly
You have a table to record an accident and the drivers
and a table to record the passengers.
Because of that you need to union the two tables so you can get all people involved.

You would have been better off with a table to record the accident details
and a table to record the people involved in the accident.
The table recording the people has a field that identifies whether the person is a driver or a passenger

That way you don't need to union the two tables to get the people involved and your query will remain editable.

There would be of couse ways to work around your current design. For example using the union in your query only makes the query uneditable. The tables are still editable and you could use vba code in a form to update the table directly rather than through the query.

I certainly don't recommend that approach.
My recommendation is that you research normalization and redesign your tables accordingly. Hopefully you are not too far along with your current design.
I think you probably actually need 3 tables instead of the two you have

tblAccident=AccidentID,Location,
              Other fields directly relating to the accident       
tblCars=AccidentID,CarRego,
        Other fields directly relating to an individual car 
tblPeople=AccidentID,CarRego,PersonType (Driver Passenger),
            Other fields directly relating to an individual person
 

Something like that anyway.
That way an accident can invole any number of cars
each car can have any number of people

Also case complete should relate to the accident and the person
What if the same person is involved in more than 1 accident?

Ultimately it is your database and your decision.

[tasawer]

This is a good suggestion.

however, my database is afully functional product with over 500 records.
NevertheIess, I shall think about redesigining as I beleive, VB coding will make it very complex.

Thanks for your help. It has been a useful suggestion.

[Delerna]

VB coding is actually critical to a well designed database and can actually make things simpler. There are many thing that just can't be done any other way. Having said that I believe it is a mistake to use it to side step tables that are not well designed. Anyway it's not so bad redesigning an active database so long as take it one step at a time and backup backup backup. I've done it before and no doubt I will do it again.

I generally create a completely new database for the new design and another database that has the one off task of copying the data from the current live version into my new version when it is ready to be released.