Look up previous information.

T

Tay J.

is there an easy way when entering information in a database for Access to
look and see if the information is already in yoru database? I am using
Access 2003

Thanks!!
 
L

Lord Kelvan

what do you mean

are you trying to get a value from another table and put it into that
one or are you wanting to make sure information is unique

if you want the first option then use a lookup table

if you want the second option open the table in design view and select
the field you want to be unique and then down the bottom of the window
change the property

Indexed

from no or yes (duplicates OK) to

Yes (No Duplicates)

and that will make that field unique

that is a dirty simple method if you want to make a composit unique
key when the unique value is accross several values it gets a bit
harder

open the table in design view like before

this time up the top you will see a button next to the key that looks
liek a lightning bolt

that button is called indexes

when you click it you will get a table

index name fieldname sortorder

you will have your primary key there


index name fieldname sortorder
ID ID accending
primarykey ID accending

you need to add the fields you want


index name fieldname sortorder
ID ID accending
primarykey ID accending
compositunq field1 accending
field2 accending
field3 accending

and then click on the first one where is says compoditunq and you will
see properties appear at the bottom of the window

primary no
unique no
ingore nulls no

make it

primary no
unique yes
ingore nulls no


Hope the helps

Regards
Kelvan
 
T

Tay J.

I have a table that I use in this database that stores addresses, names,
telephone numbers, etc. that I enter when I complete a job. If at some point
in time I visit the same address is there a way to re-populate the fields
with the exsisting information from the previous time I was at the address?
 
J

John W. Vinson

I have a table that I use in this database that stores addresses, names,
telephone numbers, etc. that I enter when I complete a job. If at some point
in time I visit the same address is there a way to re-populate the fields
with the exsisting information from the previous time I was at the address?
Click to expand...

I think you're missing the point of how relational databases work! You don't
want to "repopulate" the table, storing the same data twice; this information
should be stored only once.

You can use the Combo Box tool on the form design toolbox to create a combo
which will *locate and display* an existing record; click the magic wand icon
on the toolbox and select the combo box tool, and choose the option "use this
combo to locate an existing record".

If you're using a table datasheet to interact with the data - don't. Tables
are of VERY limited utility (this is one of many things they can't do); Forms
are much more powerful and more flexible. If you're stuck using tables, you
can use the "binoculars" search tool to find an existing record.
 
L

Lord Kelvan

john is right you should have a second table

table 1
addressid name addesss phonenumbers

table 2
jobid addressid work done fields (dates etc)

so you enter the address in table 1 and in table 2 you take that
addressid and enter your work done fields against that addressid which
will remove the redundancy. and make eaiser to generate queries or
reports

if you want to use a form look up how to build a form and combo boxes
if not you can use lookup tables in the table 2

http://www.trigonblue.com/AccessLookup.htm

that page should help you

regards
kelvan
 
T

Tay J.

I did not think of using 2 tables... but great idea. So how do I incorporate
these two tables into on form I use to input the information. Can I just
update the form to include your suggestions?

I am somewhat new to the finer points of Access so I really appreciate the
help!!
 
J

John W. Vinson

I did not think of using 2 tables... but great idea. So how do I incorporate
these two tables into on form I use to input the information. Can I just
update the form to include your suggestions?
Click to expand...

Use a Form for the "one" side table, with a Subform for the "many".
 
T

Tay J.

I really do appreciate all of the help. I did try the combo box and it did
work but only brought up whatever field the combo box what attached to. Am I
missing something as I want to have it fill all of my fields from the name
address etc.

Thanks again!!
 
T

Tay J.

Let me add an additional question. If I were to look up using a query to see
if I have previously been to an address, if so can I have the system ask me
if I want to add a new entry or if the address is not found have it ask if I
would liek ot add a new record. Additionally I am redesigning this database
in Access 2007 then saving in 2003 so I cna use it at work on my XP laptop.
 
Top