Re: Referencing Primary Key

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.

--
Steve Schapel, Microsoft Access MVP

Aaron wrote:
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" wrote:

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.

--
Steve Schapel, Microsoft Access MVP

Aaron wrote:
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!
.

Relevant Pages

  • Re: Referencing Primary Key
    ... I did mean table, not database. ... Profile Name: CAH Profile ... needs to have a 'primary key' that it uses as, I guess, a record number. ... whether or not they want to keep using the profile, as well as what the cost ...
    (microsoft.public.access.macros)
  • Re: Database Design
    ... If you create a query that returns the necessary data, ... presumably with a primary key of CountryCd or something like that. ... If the cost components vary from Product Family to Product Family, ... When you say country product ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Cumulative cost
    ... When you say "running total per student", I believe this means the same ... You can use a Totals query to get a "per student" Sum ... AND calculate something you're calling "cumulative cost". ...
    (microsoft.public.access.forms)
  • Re: FTS Performance in SQL 2005
    ... Now if I do a query directly to the field I would theoretically need: ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... its the pipe between the CPU and Memory that could be the bottleneck. ... and that you have a covering index on the columns in the query. ... Plan window will then give you a percentage query cost relative ...
    (microsoft.public.sqlserver.fulltext)