By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

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

100+
P: 106
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
Expand|Select|Wrap|Line Numbers
  1. SELECT DRV.AccidentId, DRV.ContactID, DRV.AccidentRefFinal, DRV.SolRef
  2. FROM tbl_Accident AS DRV
  3. UNION ALL SELECT PASS.AccidentId, PASS.ContactID, PASS.PassRefFinal, PASS.SolRefP
  4. FROM tbl_Acc_Passengers AS PASS;
  5.  
Union Query Linked with CaseComplete
Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
  3. FROM Contacts AS C INNER JOIN (MMQ_Clients LEFT JOIN CaseComplete ON MMQ_Clients.AccidentId = CaseComplete.AccidentID) ON C.ContactID = MMQ_Clients.ContactID
  4.  
  5. ORDER BY MMQ_Clients.AccidentId DESC;
  6.  
Feb 16 '10 #1
Share this Question
Share on Google+
5 Replies


Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. select * from thetable
  2.  
Open the query and the records are editable
Edit the query and make it a union of itself
Expand|Select|Wrap|Line Numbers
  1. select * from thetable
  2. union all
  3. select * from thetable
  4.  
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
Feb 17 '10 #2

100+
P: 106
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.
Mar 1 '10 #3

Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. tblAccident=AccidentID,Location,
  2.               Other fields directly relating to the accident       
  3. tblCars=AccidentID,CarRego,
  4.         Other fields directly relating to an individual car 
  5. tblPeople=AccidentID,CarRego,PersonType (Driver Passenger),
  6.             Other fields directly relating to an individual person
  7.  
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.
Mar 1 '10 #4

100+
P: 106
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.
Mar 1 '10 #5

Delerna
Expert 100+
P: 1,134
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.
Mar 1 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.