Access 2013 - Prevent Duplicate Data entry

jleavy046

Hi All,
I'm setting up a very simple database using Access for a Residents Association...

I've the following tables:
tbl_Roads
tbl_Member_Details

Roads just has a field with a list of 20 roads or so (RoadName is primary key).

Member details has: MemberNo(auto number, primary key), Name, house number, Odd Number (yes/No), road (selected from list of roads), 2 phone number fields, and e-mail address fields.

From house number and road name, i can ran a query to find duplicate addresses in the database.

Is it possible to prevent 1 ABC Road to be entered twice at data entry?

I don't yet have any forms for data entry or anything like that, but that's the plan.

End game:
Find members by road.
Find members living on one side of the road (odd or even numbered house)
Find by membership number.
Find by Address (maybe select road first, then enter the house number).
Find all members with mobile numbers/no numbers etc.


All basic enough, hopefully - I don't really have a massive amount of time to devote to this to be honest, so the more basic the better.


Joe

SilverSideUp

Is it possible to prevent 1 ABC Road to be entered twice at data entry?

Yes it is. In your table tbl_Member_Details you just need to make your road field a foreign key that refers to your primary key in table tbl_roads.

jleavy046

SilverSideUp wrote: »

Is it possible to prevent 1 ABC Road to be entered twice at data entry?

Yes it is. In your table tbl_Member_Details you just need to make your road field a foreign key that refers to your primary key in table tbl_roads.

I'm not sure I follow how that will help, but I'll definitely try it out and let you know asap.

Thanks a million indeed,
Joe

SilverSideUp

jleavy046 wrote: »

I'm not sure I follow how that will help, but I'll definitely try it out and let you know asap.

Thanks a million indeed,
Joe

Your primary key in tbl_Roads uniquely identifies all roads in that table. So you can't have duplicate roads in that table. Your problem is in your second table where your field roads may contain duplicate entries. You need to introduce a constraint on that field to stop duplicate roads in that table. You do that by making roads a foreign key which references the primary key in tbl_Roads.

SilverSideUp

SilverSideUp wrote: »

Your primary key in tbl_Roads uniquely identifies all roads in that table. So you can't have duplicate roads in that table. Your problem is in your second table where your field roads may contain duplicate entries. You need to introduce a constraint on that field to stop duplicate roads in that table. You do that by making roads a foreign key which references the primary key in tbl_Roads.

Sorry. I just re-read your post. You want the house number and road name not to be duplicated, is that right? If that's the case, then in addition to my advice above, just change your primary key in your second table to a combined primary key of house number and roads.

jleavy046

SilverSideUp wrote: »

Sorry. I just re-read your post. You want the house number and road name not to be duplicated, is that right?

I don't want any address to be entered twice.
Example 1 o'Connell Street
O'Connell street is a road NAME and number 1 is a field in the second table....

I can run a query to find duplicates, no bother, but how to prevent duplicates UGC the first place.

Many people may live in O'Connell St, but only one person will live at number 1 O'Connell street.

SilverSideUp

jleavy046 wrote: »

I don't want any address to be entered twice.
Example 1 o'Connell Street
O'Connell street is a road NAME and number 1 is a field in the second table....

I can run a query to find duplicates, no bother, but how to prevent duplicates UGC the first place.

Many people may live in O'Connell St, but only one person will live at number 1 O'Connell street.

Change your primary key in your second table to a combined primary key of house number and roads. Make roads a foreign key as mentioned previously.

jleavy046

SilverSideUp wrote: »

Change your primary key in your second table to a combined primary key of house number and roads. Make roads a foreign key as mentioned previously.

Ok, I'll try tomorrow

Johnboy1951

jleavy046 wrote: »

I don't want any address to be entered twice.
Example 1 o'Connell Street
O'Connell street is a road NAME and number 1 is a field in the second table....

I can run a query to find duplicates, no bother, but how to prevent duplicates UGC the first place.

Many people may live in O'Connell St, but only one person will live at number 1 O'Connell street.

is membership limited to only one per household?

jleavy046

Johnboy1951 wrote: »

is membership limited to only one per household?

Yes, so that address should only be in the database once.

jleavy046

SilverSideUp wrote: »

Change your primary key in your second table to a combined primary key of house number and roads. Make roads a foreign key as mentioned previously.

Ok, it worked!

Foreign key i already had, I didn't know you could have two Primary keys.

Now, it get's more complex - is it possible to have a nice error that says 'Hey, that address already exists - see memberNo: 123' ?

Given i don't yet know how to create a data entry form or a search box, perhaps I'm skipping ahead a bit!

Joe