Hi and thanks for reading this!

Background: I am a real estate investor looking to use the power of access to database buyers and leads.

The Function of the Database: I want to be able to enter a new lead or property and the database will run a query based on zip code on which buyers I should call. For example, if I enter 510 Fake Street, 63033 into my database, the database will return a list of buyers for 63033.

So far I have set up two tables, one for buyers and one for properties.

My Question: How should I set up the zip codes for buyers?

Originally, I was going to have each zip be a field and the contents of the field a boolean (-1,0). I thought this would be the best way to do this because while some buyers may only be interested in one zip code, there are plenty of buyers who are interested in multiple zip codes. I was going to use check boxes to enter the booleans into the proper fields (I have this working correctly). Will I be able to run a query based on field title?

Any ideas?

Thanks in advance.

You need a separate Table for Zip Codes.

This will be a One to Many Relationship with both your Buyers and Properties Tables.

Please post the design of your Tables and I will see if I can add some further advice.

So here is my contacts table:

http://i48.tinypic.com/2wcmyir.jpg

and the form that it coordinates with:

http://i48.tinypic.com/5kmzaq.jpg

and here is the form that my properties coordinate with:

http://i50.tinypic.com/1ovdzk.jpg

I want to be able to key in a property on the properties form and the list of prospects is updated with people who are interested in that zip code.

Thanks for the help!

The 63131 is just my test zip code, I will eventually have about 50 different zips.

Thanks.

First get rid of 63131.

Then change CityState to CityID and make the DataType Integer.

Now create a new Table and call it tblCity
The fields you will need are as follows.

CityID as AutoNumber
StateID as Integer
City as Text
ZipCode as Text.

Next you will need another Table for States. Call it tblState

StateID as AutoNumber
State as Text

Now you can create the Relationship between CityID to CityID and StateID to StateID

This will normalise your structure and allow you to create the searches you need.

Let me be a little more clear,

I want to put in buyers into the database, the information will consist of basic contact information. Each record will also store Yes/No information on if they are interested buyers in specific zip codes. This data will be entered with check boxes. There are about 80 different zip codes in total. All are in Missouri.

I also would like to store properties. In a form, there will be a list box that will contain potential buyers for that specific property.

So my question, I guess, is how do I do this right? (stability, ease of use, speed)

Also, is inputting buyer information into the database as a Boolean the best method?

Sorry for all these questions.

Thanks Rain.

I thought I answered that question.

Create more Tables and Relate them correctly.

RainLover, thanks for the help.

I set up the tables accordingly.

Now I am having trouble coordinating check boxes as tools for putting data into my tblBuyersZip.

I guess I need it so when I click 63101, 63101 is inserted into the tblBuyersZip table with the same BuyerID that is the current record. And when I click it again, it removes that zip from the tblBuyersZip table.

Do you know of a guide that can help me sort this out?

Don't use checkboxes.

Create a new Table with the Buyer's ID and a list of all zips that they are interested in.

I can't really help much with this click on 63101 problem as I don't know where it is or where it comes from.

Perhaps a cut down version of your database posted here would help me to understand the problems you are having.

Please do not post in anything later than 2003. I don't have 2007.