PC Review


Reply
Thread Tools Rate Thread

Access -- Make a macro and relational lookup

 
 
Mac Hardy
Guest
Posts: n/a
 
      31st Dec 2009
I have a database with 2 tables: Brds and Numbers.
Birds contains 250,000 records and several fields, including one named
SPECIES and one called SEQUENCE. Numbers contains only 350 records (all
unique) and 4 fields: SPECIES, SEQUENCE, ABBREV, TAXON.
I want a macro to enter a new record in birds as follows. I type a 4-letter
abbreviation into the SPECIES field and the macro will find the record in
Numbers, ABBREV field, and return the SPECIES field of Numbers to the SPECIES
field of Birds, then return the SEQUENCE field of Numbers to the SEQUENCE
field of Birds.
There is more but I will take any progress offered. I have made none so
far. Thanks. Mac Hardy
 
Reply With Quote
 
 
 
 
tighe
Guest
Posts: n/a
 
      31st Dec 2009
Mac,

there be a more elegant way but:
I would make it so where you type the ABBREV a combo box so you know you are
using one that exists, then on the after update event of the combo box, run
an append query that uses the Combo box data as filter to add the addition
fields information to the new record, or you could use the setvalue action in
a macro to Dlookup the other data based on the abbreviation.

hope this helps.

"Mac Hardy" wrote:

> I have a database with 2 tables: Brds and Numbers.
> Birds contains 250,000 records and several fields, including one named
> SPECIES and one called SEQUENCE. Numbers contains only 350 records (all
> unique) and 4 fields: SPECIES, SEQUENCE, ABBREV, TAXON.
> I want a macro to enter a new record in birds as follows. I type a 4-letter
> abbreviation into the SPECIES field and the macro will find the record in
> Numbers, ABBREV field, and return the SPECIES field of Numbers to the SPECIES
> field of Birds, then return the SEQUENCE field of Numbers to the SEQUENCE
> field of Birds.
> There is more but I will take any progress offered. I have made none so
> far. Thanks. Mac Hardy

 
Reply With Quote
 
 
 
 
Steve Schapel
Guest
Posts: n/a
 
      1st Jan 2010
Mac,

There is a problem with the design of your tables. What you are proposing
will mean that the same information is stored in two different places within
your database. This is called un-normalised, and flouts one of the basic
rules of database design, so this leads me to believe you have misunderstood
one of the principles of relational data. Please let us know if you can
consider a schema change, and if so somebody will be happy to help.

--
Steve Schapel, Microsoft Access MVP


"Mac Hardy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a database with 2 tables: Brds and Numbers.
> Birds contains 250,000 records and several fields, including one named
> SPECIES and one called SEQUENCE. Numbers contains only 350 records (all
> unique) and 4 fields: SPECIES, SEQUENCE, ABBREV, TAXON.
> I want a macro to enter a new record in birds as follows. I type a
> 4-letter
> abbreviation into the SPECIES field and the macro will find the record in
> Numbers, ABBREV field, and return the SPECIES field of Numbers to the
> SPECIES
> field of Birds, then return the SEQUENCE field of Numbers to the SEQUENCE
> field of Birds.
> There is more but I will take any progress offered. I have made none so
> far. Thanks. Mac Hardy


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Relational Mapping Pattern for Excel/Relational DB =?Utf-8?B?R2FyeSBG?= Microsoft Excel Programming 0 10th Oct 2006 09:27 PM
To use relational or not to use relational? Rick B Microsoft Access 4 23rd Jul 2006 10:46 PM
Difference between Designing Relational & Non-Relational Database Cicada Microsoft Access 13 14th Jul 2006 02:02 PM
How to make a relational questionnaire response database? =?Utf-8?B?UXVlc3Rpb25uYWlyZSByZXNwb25zZSBkYXRhYmFz Microsoft Access 3 20th Jul 2005 07:22 PM
how to make a database relational via a query David Schwartz Microsoft Access 2 10th Jul 2003 01:17 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:11 PM.