Relationship

[ClownKiller]

Regards, im new to access with some basic knowledge of office tools and currently im trying to set up a relationship in between of my two tables which is not working as i would like it to and i can't guess why.

Hope to find an answer here ...

I have one table with user data (f name, l name, street etc.) and in the other table i have data when this user has contacted me, why, what did i do afterwards and so.



Here is my problem. I get duplicate data in user table each time when i enter data as access creates new entry in both table, user and usercontact and i would like that entering new data update only usercontact table if i already have that user in user table.

Im entering data using setup formwiz with combined data from both table and i supose that i did wrong relationship when i get that kind of double entry in user table.

I have related ID columns in each table.

[pbaldy]

Not sure how you set up the form, but you shouldn't be getting values added to both tables. In the scenario you've described, I'd expect the form to be bound to the "other" table. You'd have a combo box to select the user; that combo would get its selections from the user table, but be bound to a field in the other table. Typically that field would be a user ID field, which both tables would have, and which the relationship between them would be based on.

[ClownKiller]

Hi, i think i didn't explained right what am i doing. I have one table with following data User - PID (something as social security number in format of 11 digits w/o dashes),FirstName, LastName, DateOfBirth, City, Adress and another table Contact - Date, Time, Place, Adress. Now, i see what r u thinking with combo box but i have form for entry with all the data from first and last table and now when i fill all the data the first entry for that person is ok, but when im entering the same person again but with diferent data in Cotact table im getting another entry again in both table but there would be just one person in contact table and for that person in contact table two entries about contact with that person.

Hope that clarifies what im trying to do.

[pbaldy]

You generally shouldn't store that data in the other table, just the PID. You get the related data in a query. Either way, the form should only be bound to the other table, so you never get data added to the user table by accident.

[ClownKiller]

Im not sure what you mean but that is not what i need to do. i DO need form for entering data with all fields described. Don't know how to explain properly but im getting double entries of persons in user table. i have put UserID field in child table and relate that one to ID in user table with thoughts that access will generate one input in user and one in contact and then when im doing new input for the same person that it will check that this user exist in user table and just add him the rest of data in contact table as second record. Hope u understand what i need.

[pbaldy]

I suspect I understand what you need better than you do. You admit to being new to Access but you're arguing with me about how it should be set up. Trust me, you do NOT want the form bound to the user table, just the contacts table. You use a combo that gets its selections from the user table, but is bound to the contacts table. You shouldn't store anything other than the PID in the contacts table, but if you insist on it, you still don't have anything bound to the users table. You get the values from it and put them into textboxes bound to the contacts table.

[ClownKiller]

@pbaldy im not following you at all , the thing is that im stuck on the relationship between my two tables and no matter how i set relationship between them i don't get the right result, we can discuss about that in eternity but that will not solve my problem as well i do not understand half of what u r saying.

i will try to set up an example of what i need as my needs are somewhat complicated but following example would do:

imagine u r supervisor in security firm and have 10 or more employees as security guards and they are working at few local schools at night as they have problem with constant teen vandals, graffiti etc. so we need to track down that persons and on some way see when they are inputed 3 times as for the third time we should for instance need to call the police.

now, i know that i would need to set up the db first and the form last but to be able to determine what data inputs i need u have to see that form first. on one single form i need to have PID of persone, first name, last name, date of birth, city, adress, date and time of capture, city of capture, adress of capture, offense person did and Fname and Lname of security guard.

i read what u have wrote to me about setting the tables but im not sure what exactly u had on mind. i divided my data on person and place of capture in this example, using ID on autonumber on both table and person ID in table of capture which i related to ID in person table. on that way i thought that when person is created it gets auto ID in person table and PersonID in capture table and logicly after capturing the same person again and entering his data through form again person table should be intact but just capture table should be updated with new time of capture for that person.

i will make an example db if needed and attach it here so u could see what im doing wrong. Also, i do not like when im entering data through form, after exit and reentering on form i see past entered data on form and not new blank txt boxes ... that also gets on my nerves ...

either way, im not asking for a fish but i need a fishing pole in matter of speaking as i do want to figure out how that works. Read 3 books about access and stoped at relationships as im stucked here and cannot solve that problem.

[pbaldy]

If you only want the capture table updated (as I would expect), the form should only be bound to that table, not both. I don't know how to say it any clearer. If you bind the form to both tables, both will get updated. A sample would certainly help.

As to your issue of the form displaying previous data when opened, change its Data Entry property to Yes.

[ajetrumpet]

If you bind the form to both tables.

paul, how do you bind a form to two tables?

[pbaldy]

Presumably the OP used a query that joined the two. As I know you're aware, a form can only have one record source. That source can be a query that combines tables.

[ajetrumpet]

Presumably the OP used a query that joined the two. As I know you're aware, a form can only have one record source. That source can be a query that combines tables.

wasn't 100% that's what you meant Paul. I figured though. sorry

[ClownKiller]

If you only want the capture table updated (as I would expect), the form should only be bound to that table, not both. I don't know how to say it any clearer. If you bind the form to both tables, both will get updated. A sample would certainly help.

As to your issue of the form displaying previous data when opened, change its Data Entry property to Yes.

i really don't have an idea how to explain my need as simple as possible and i appreciate your effort but if you read my post above with example of security guards can you share with me your point of view how would you set up DB for that kind of records? I would avoid all the trouble with all that if im to use that accdb but im not, as others would need to and im 100% positive that they would mess all up if given basic accdb with tables and some basic input form, they sure will, that's why i need to setup this to be as simple to use for them and that they will have minimal chance for mess things up.

the problem is that we cant know in front how many offenders will be and i sure do not to check on every entry if that person is already in DB or not, that's why there is a need to have all data from both table on the form and here im having a problem with update records in person table and now as well in capture table as access is doing those gddamed double entry's in both of them. If i set up form that will be updating only second table how do you suppose that i will know if there is that person already in DB or not if im not to see in DB itself or by querrying DB for that user .... this aproach is not acceptable as i would manage with that but my colleagues will sure not as i know little about access and they know nor 1% of mine knowledge

I would really like to see your idea on how to set up that DB and form for entering data and searching DB on user basis.

[pbaldy]

My approach wouldn't change. I'd have a table for offenders and a table for offenses, related one-to-many on the offender ID. There would likely be other tables for other things, but those are the tables relevant to this discussion.

The common approach for making sure an offender is in the offenders table is with the previously described combo box. You use the not in list event along with setting the limit to list property to yes. The limit to list property means you can only enter/select people who are in the offenders table. If you enter someone who isn't, the not in list event steps in and asks the user if they want to add that person to the offenders table. The user would say yes, a secondary form (bound to the offenders table) would pop up allowing them to add the appropriate info, and then take them back to the original form so they can add the info about the offense. Searching for "notinlist" should find code for those methods here.

[ClownKiller]

My approach wouldn't change. I'd have a table for offenders and a table for offenses, related one-to-many on the offender ID. There would likely be other tables for other things, but those are the tables relevant to this discussion.

The common approach for making sure an offender is in the offenders table is with the previously described combo box. You use the not in list event along with setting the limit to list property to yes. The limit to list property means you can only enter/select people who are in the offenders table. If you enter someone who isn't, the not in list event steps in and asks the user if they want to add that person to the offenders table. The user would say yes, a secondary form (bound to the offenders table) would pop up allowing them to add the appropriate info, and then take them back to the original form so they can add the info about the offense. Searching for "notinlist" should find code for those methods here.

This is it, i believe so . First time when u spoke about cmbBox i didn't have in mind that is possible to set it up for list and entering. I believe that this would be an excellent way to handle my problem and i suppose that this part with NotInList will involve VBA or macros with which im not familiar at all but i will look in to that matter and hopefully create what i have imagined soon. Thanks a lot pbaldy!