Newbie: Lookup within form

[Robin]

Hi there.

I am trying to perform a lookup using Access. My database contains 6
tables. This lookup concerns 2 of the tables.
Table A is Medicine Purchases
Table B is Medical Record

In Medicine Purchases, every medine bought has a batch code, expiry date and
medicine name, along with a few other fields.
In the Medical Record form, when I type in the batch code, I want Access to
populate the expiry date and medicine name, then I will carry on with the
details of who administred the drug etc (the other fields in the table).

How can this be accomplished?

Also, does anyone know of good websites on Access teaching things like this
and calculations?

Thanks in advance,
Robin

 

[Guest]

Hey, I've been teaching myself the basics on Access for
the past 2 days at the following website:
http://www.fgcu.edu/support/office2000/access/index.html .
As for the population... that's the same question I had.
Good luck.

-Chris

 

[DDM]

Robin, first you need to set up a one-to-many relationship between Medicine
Purchases (one) and Medical Record (many). Batch Code should be the common
field here.

Next, create a query based on these two tables. Include Batch Code from the
Medical Record table, and Medicine Name and Expiry Date from the Medicine
Purchases table. Include other fields as desired.

Note that running this query produces a "virtual table" that shows data from
the two underlying actual tables. If you enter a Batch Code, Access will
look it up in the Medicine Purchases table and fill in the Medicine Name and
Expiry Date fields automatically, which is what you are looking for.

Now create a form (use the wizard if necessary) based on this query. If you
already have a form, change the Record Source property so it points to this
query.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[Rural Computer Centre]

Thanks for that Chris.

Yes, it is a similar question, only I am not using sub-forms. Just need the
database to do its job in order to stop repeat entry.
You had any sucess yet?
Robin

 

[Robin]

Hi there DDM.

These instructions are great, but I seem to be running into a few problems.

When I attempt to set up a relationship between the tables, I get an
Indeterminate relationship, rather than a one-many. I can't figure out how
to change it.

With this type of relationship, I can't seem to manage to make the query
work. When I run the query, it comes out empty, no records / fields are
displayed. Is this due to the relationship type, or how I have set up the
query?

Thanks again,
Robin

 

[DDM]

Robin, I made a few assumptions based on your original post. First, I
assumed that the Batch Code was the unique identifier for the medicines
entered in the Medicine Purchases table (meaning that batch codes are not
repeated from record to record), and that the field was indexed. Second,
that the same Batch Codes are used in the Medical Record table (but that
here they can be repeated). If this is so, you should be able to set up a
one-to-many relationship between the two tables, and your query should show
what medicines (by Batch Code) were administered. If this is not so maybe
you could post back with some more details about how your tables are
structured, and we can take it from there.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[Robin]

OK, I'll return to the table and make Batch code the primary key (is that
the same as unique identifier?) and index it. I'll then try again.

Thanks,
Robin

 

[Robin]

OK, I'll return to the table and make Batch code the primary key (is that
the same as unique identifier?) and index it. I'll then try again.

Thanks,
Robin

Hello again.

I made the changes you suggested, and it works!!! Thanks very much, does
just what I want now.
Robin