Street names and numbers

[Tips]

I am looking for some advice or guidance on this please.

I am trying to set up a table, or multiples if advised to do so, that will store street names and house numbers.
Ideally I would like to enter a street name, and then number from and number to, ie: Main Street, from 1 to 30.

Each house number will have 4 fields associated to it.

The desired result in a form will be to enter the street name, numbers from and to, and then a sub form open up showing a list of numbers covering the range entered with 4 "Yes/No" fields next to each house number.

Some streets could go up to as most as 300 houses, but I would not want that number shown for every street when it is not necessary. only the relevant number according to what is entered.

All data would need to be stored and then queries and reports run against it.

I hop this makes sense. I have tried to explain it as best as what I can.

I am using access 2007 on windows 7.

thank you in advance from this novice user.

 

[plog]

I suggest 2 tables for this: Streets and Houses. They would look like this:

Streets
street_ID - autonumber primary key
street_Name - text

Houses
ID_street - number, foreign key to Streets.street_ID
house_ID - autonumber primary key
house_number - number, holds number portion of street address
house_YesNo1
house_YesNo2
house_YesNo3
house_YesNo4

The house_YesNo fields are just place holders for whatever 4 fields you need--rename them as necessary.

For the form I would use a continous form with a form header. In the form header would go 3 unbound inputs--one for the street name and the other 2 for the street number range and a button to filter on the inputs. Check out this link for info on using a filter: http://allenbrowne.com/ser-28.html

 

[Tips]

Thank you for the quick response, and I believe makes sense to me.
Jus one query, what do you mean by foreign key? I guess that this is a relationship, but please could you give me a bit more detail.

I appreciate the assistance.

 

[plog]

Here's a link to what a foreign key does: http://en.wikipedia.org/wiki/Foreign_key

For your situation this is what data in your table would look like:

Streets
street_ID, street_Name,
1, Main
2, Broadway
3, First

Houses
ID_street, house_ID, house_number,
2, 1, 7501
2, 2, 7511
1, 3, 413
3, 4, 56203

the ID_street value in Houses links to the street_ID field in Streets. That means the data in Houses would resolve to this:

7501 Broadway
7511 Broadway
413 Main
56203 First

 

[Tips]

Makes sense, thank you very much.

 

[Tips]

Your input has been great,
I have done as mentioned, and on my form I have got the street name as a combo box, but I can not get it to update changes in the subform if I change street name.

I am not sure what I am missing.
I have attached what I have done thus far.

 

[plog]

I'm not the best with forms, I know the general concepts so that's what I will give you. For better, specific advice, post in the forms section.

1. You need a continous form based on Houses.
2. On that form you need a form header section.
3. In the header you need 3 inputs: drop down for street and 2 text inputs for house number ranges.
4. You need a button in the header that when clicked applies a filter to the form to show just those records in Houses that meet the criteria supplied in the inputs.