Referencing Primary Key

[Guest]

I think I figured out what I need to do to solve my problem, but I'm not sure
how to go about coding it.

I'm making a list of test numbers, and a seperate database with the same
test number as the primary key. The second database containts cost to patient
(I work in a hospital) a billing code and the test name.

What I want is to set up a form (kind of like mail merge, but I don't think
Mail Merge can do something like this) that, when the test number is
displayed, will reference the second data base and display the information
that corresponds to said number. I want to make the data printable, if that
changes anything.

Any idea as to how I go about doing that or is there a VB-for-dummies
website I could check out?

Thank you!

 

[Steve Schapel]

Aaron,

When you are using the word "database", are you actually referring to
"table"?

Normally, if you have data in more than one table that are related to
each other, you use a Query to return a setr of data that includes data
from both tables. This sounds like it might be the case here... but I
am confused by your reference to the "test number" being the primary key
in the second table - this doesn't make sense to me at the moment.
Maybe you could post back with examples, and a more complete
explanation, of the data you are working with.

As for printing the data, this is normally done with a Report, and a
report normally uses data from a query. At this stage, I don't see any
role here for a macro. But again, give us some examples to go by.

 

[Guest]

I'm sorry. I did mean table, not database.

Ok. Here is an example (or at least how I have the tables set up at the
moment):

Profile Name: CAH Profile
Tests Included (by number):
1503
8177
1321

And in the second table
Test Number: 1503
Test Name: CBC
Cost: $8.00
CPT: 1234567

I don't have access to the actual data at the moment (I'm at home) but
that's the gist of it. When I said 'Primary Key' I meant that each table
needs to have a 'primary key' that it uses as, I guess, a record number.

Now what I'm trying to do is have Access read the 'test number' in the first
table, look in the second table for that same number, and spit out the
information.

It would probably help if I explained what I was doing this for.

I work for a hospital that keeps custom blood test profiles for the doctors
offices. Once a year we have to send out a letter that lets them indicate
whether or not they want to keep using the profile, as well as what the cost
will be (to the patient) if the test isn't covered by insurance.

Now I could technically just build one big table that includes each doctor's
office, their profile, tests and costs but that would be a hellaciously long
proccess. Also, the cost of the tests can change over time. With the 'one big
table' idea, that would mean I would have to search every record and change
each one indivudually every time there was a price change.

I think that if I use two tables, I will only have to change the data in one
spot. I hope I'm making sense. I'm just having trouble figuring out how to
get said data into a 'form letter' that I can print and have sent to the
doctor's office.

Thank you!
Aaron

 

[Steve Schapel]

Aaron,

Thanks for the further explanation.

You are correct to have the tables designed like this. "I could
technically just build one big table" is technically true, but it would
be an invalid database design. The structure of your tables is dictated
by the nature of the data, not by what you like, or simplicity, or
convenience, or anything else. So, looks good to me.

Yes, as I intimated in my earlier reply, this should be a very simple
query. The query includes both tables, joined on the test number field
from both, and presumably with a criteria to select the Profile
required, and it should give you what you want. This query can then be
used as the basis of a report, or a mail merge, or whatever.

 

[Guest]

I'm having a little bit of trouble.

I have both of the tables designed, but I don't have the slightest idea how
to go about creating a query that will reference both tables and display the
data the way I want.

Truth be told, I don't know how to create a query.

Is there a 'Beginner's Guide to Access Queries' I could look at? Or maybe
someone to get me pointed in the right direction?

 

[Steve Schapel]

Aaron,

It would probably be good to have a good, general purpose Access book to
refer to. There are a few around. "Building Access Applications" by
John Viescas is excellent, though quickly gtoes into a lot more depth
than we are talking about here.

From the Database Window, select the Queries tab, and then click the
New button.
Leave the New Query dialog selection on 'Design View', and click OK.
In the Show Table dialog, select each of your 2 tables in turn, and
click the 'Add' button.
Then click Close.
I guess the 2 tables are then depicted in the upper panel of the query
design window, with no lines between them.
Join the tables by clicking the Test Number field in Second table, and
drag/drop to the Tests Included field in the CAH Profile table. This
should then show a line between the two tables.
Drag the fields from both tables that you want to see, from the table,
to the grid in the lower panel of the query design window. I assume the
CAH Profile table contains more fields than what you showed so far, right?
Click the Run button on the toolbar (that's the one with the red [!]
icon), which should then open the query datasheet. See if it produces
the desired set of results.

 

[Guest]

I keep getting an error:

Type mismatch in expression.

I've linked the tables from test number in the profile page (there are 8
values) linked to a field called cpttestnumber in the table that gives all
the test information.

Am I doing something wrong? I'm getting the book sometime this week, but if
I could get pointed in the right direction it'd be most helpful.

Thank you!

 

[Steve Schapel]

Aaron,

Are [cpttestnumber] and [test number] both defined in the table design
as Number data type?

 

[Guest]

They weren't, but I switched 'em and the error went away.

Aaron,

Are [cpttestnumber] and [test number] both defined in the table design
as Number data type?

--
Steve Schapel, Microsoft Access MVP

I keep getting an error:

Type mismatch in expression.

I've linked the tables from test number in the profile page (there are 8
values) linked to a field called cpttestnumber in the table that gives all
the test information.

Am I doing something wrong? I'm getting the book sometime this week, but if
I could get pointed in the right direction it'd be most helpful.

 

[Steve Schapel]

Cool. Let us know if you need any further help.