Cannot enter value into blank field - outer join

[mrtn]

Hello everybody

It's my first on this forum which I have been following for some time now and have always been able to find solutions to my problems.

This particular issue is driving me mad. It's even worse that I have managed to sort it today and then changed something and can't get it back to work again...

I have created a simple db just to recreate the problem.

I've got two tables A & B. Table A contains clients (some duplicated) and table B contains "actions" for those clients.

I've got a query that lists all clients from A and "actions" field from table B. I need to be able to type in the "actions" field. Ideally it would add client and allocated action into table B automatically.

As mentioned before, I did manage to do it today. How???I have no idea. What I could see that the action that I typed in for the first duplicate rcord was pre-populated with others. I know it has something to do with the indexation and one-to-many relationships but as far as I am aware I am not able to create one-to-one relationship because of the duplicated clients in table A.

I hope you can help.

All the best!

 

[boblarson]

Your structure makes no sense from a relational database best practices viewpoint.

First off, if you have clients in table A and you have actions for those clients in table B then the structure should be:

tableA:
ClientID - Autonumber
ClientDescription - Text

tableB
ActionID - Autonumber
ClientID - Long Integer (Foreign Key which is primary key in table A)
ActionDescription - Text

And in the real world you don't use a single query for updating - this might work if configured this way but it would normally be that Table A (THE ONE) would be a main form and Table B (THE MANY) would be a subform.

 

[mrtn]

Hi Bob

Thanks for a promtp response. Your feedback is much appreciated.

I have changed the db to be set up as you said and it has in fact solved part of my problems.

I know that my approach may lack a common sense however I am trying to achieve a slightly different effect from the one given in your solution.

It may be easier if I describe what I am trying to do-I have an excel file of circa 30,000+ records that will be imported to Access on a regular basis. 1 field contains values(text made of 5+ words) which sometimes are duplicated. I need to exclude certain values during the first run which are to be stored in a seperate table for reference to a future imports. I need to be able to quickly add new values in the future to the standing data table. Therefore in my example I wanted to have just 1 action assigned for 1 value.

With your solution each client gets individual ID which means I have to enter the same action individually for the same clients.

What I managed to achive this morning (which I have lost) was the ability to enter just one value which was populated across the same client in the whole table.

I understand it may not be a proper way of doing it but it does the simple job for me...

Thanks