2 Tables, 1 query

  • Thread starter Thread starter Luis
  • Start date Start date
L

Luis

Hi:

I have two tables that look like this:

Table 1:
Code Name No. of Mg.
J456 Neo 5
J457 Beta 10
J458 Mars 100

Table 2:Amount Allowed
Code Blue Cross United Foundation
J456 $20 $30 $45
J457 $40 $55 $33
J458 $32 $45 $30

I want to create a query in which I can bring all fields
from table one and the insurance field I choose:

For instance, If I want to see Blue Cross

Code Name No. of Mg. Blue Cross
J456 Neo 5 $20
J457 Beta 10 $40
J458 Mars 100 $32

If I want to see United:

Code Name No. of Mg. United
J456 Neo 5 $30
J457 Beta 10 $55
J458 Mars 100 $45

My J codes are 940 of them...

I hope someone can help me.

Thank you,

Luis
 
Luis said:
I have two tables that look like this:

Table 1:
Code Name No. of Mg.
J456 Neo 5
J457 Beta 10
J458 Mars 100

Table 2:Amount Allowed
Code Blue Cross United Foundation
J456 $20 $30 $45
J457 $40 $55 $33
J458 $32 $45 $30

I want to create a query in which I can bring all fields
from table one and the insurance field I choose:

For instance, If I want to see Blue Cross

Code Name No. of Mg. Blue Cross
J456 Neo 5 $20
J457 Beta 10 $40
J458 Mars 100 $32

If I want to see United:

Code Name No. of Mg. United
J456 Neo 5 $30
J457 Beta 10 $55
J458 Mars 100 $45

My J codes are 940 of them...
Click to expand...


Create a query and add both tables. Then make sure the
connecting line is between the two Code fields. Once that
is done, you can drag the desired fields to the query's
field list.
 
Hi:

I have two tables that look like this:

Table 1:
Code Name No. of Mg.
J456 Neo 5
J457 Beta 10
J458 Mars 100
Click to expand...

That's ok...
Table 2:Amount Allowed
Code Blue Cross United Foundation
J456 $20 $30 $45
J457 $40 $55 $33
J458 $32 $45 $30
Click to expand...

But that's not. You're storing data in field names; basically, you're
embedding a one (code) to many (provider) relationship in a single
record. A better design would be to have a proper many to many
relationship: your Table1; Providers (containing one field, with
values "Blue Cross", "United", "Foundation", and any possible future
values); and:

AmountsAllowed
Code
Provider
AmountAllowed

This table would have three *rows*, rather than three *fields*, for
each code.
I want to create a query in which I can bring all fields
from table one and the insurance field I choose:

For instance, If I want to see Blue Cross

Code Name No. of Mg. Blue Cross
J456 Neo 5 $20
J457 Beta 10 $40
J458 Mars 100 $32

If I want to see United:

Code Name No. of Mg. United
J456 Neo 5 $30
J457 Beta 10 $55
J458 Mars 100 $45

My J codes are 940 of them...
Click to expand...

Then you'll need three Queries, one for each provider, and a UNION
query linking them:

SELECT Table1.Code, Table1.Name, "Blue Cross" AS Provider,
Table2.[Blue Cross] AS [No. Of Mg.]
FROM Table1 INNER JOIN Table2
ON Table1.Code = Table2.Code
UNION ALL
SELECT Table1.Code, Table1.Name, "United" AS Provider,
Table2.[United] AS [No. Of Mg.]
FROM Table1 INNER JOIN Table2
ON Table1.Code = Table2.Code
UNION ALLSELECT Table1.Code, Table1.Name, "Foundation" AS Provider,
Table2.[Foundation] AS [No. Of Mg.]
FROM Table1 INNER JOIN Table2
ON Table1.Code = Table2.Code;


John W. Vinson[MVP]
 
I thought about doing table 1, but when I tried doing
suggested table two, it only accepts 255 codes across and
I have 940...How do I overcome it?

I figure table 2 will look:
Code
J456
-----Original Message-----
Hi:

I have two tables that look like this:

Table 1:
Code Name No. of Mg.
J456 Neo 5
J457 Beta 10
J458 Mars 100
Click to expand...

That's ok...
Table 2:Amount Allowed
Code Blue Cross United Foundation
J456 $20 $30 $45
J457 $40 $55 $33
J458 $32 $45 $30
Click to expand...

But that's not. You're storing data in field names; basically, you're
embedding a one (code) to many (provider) relationship in a single
record. A better design would be to have a proper many to many
relationship: your Table1; Providers (containing one field, with
values "Blue Cross", "United", "Foundation", and any possible future
values); and:

AmountsAllowed
Code
Provider
AmountAllowed

This table would have three *rows*, rather than three *fields*, for
each code.
I want to create a query in which I can bring all fields
from table one and the insurance field I choose:

For instance, If I want to see Blue Cross

Code Name No. of Mg. Blue Cross
J456 Neo 5 $20
J457 Beta 10 $40
J458 Mars 100 $32

If I want to see United:

Code Name No. of Mg. United
J456 Neo 5 $30
J457 Beta 10 $55
J458 Mars 100 $45

My J codes are 940 of them...
Click to expand...

Then you'll need three Queries, one for each provider, and a UNION
query linking them:

SELECT Table1.Code, Table1.Name, "Blue Cross" AS Provider,
Table2.[Blue Cross] AS [No. Of Mg.]
FROM Table1 INNER JOIN Table2
ON Table1.Code = Table2.Code
UNION ALL
SELECT Table1.Code, Table1.Name, "United" AS Provider,
Table2.[United] AS [No. Of Mg.]
FROM Table1 INNER JOIN Table2
ON Table1.Code = Table2.Code
UNION ALLSELECT Table1.Code, Table1.Name, "Foundation" AS Provider,
Table2.[Foundation] AS [No. Of Mg.]
FROM Table1 INNER JOIN Table2
ON Table1.Code = Table2.Code;


John W. Vinson[MVP]
.
Click to expand...
 
I thought about doing table 1, but when I tried doing
suggested table two, it only accepts 255 codes across and
I have 940...How do I overcome it?
Click to expand...

Please reread my message. That is NOT what I suggested!

Tables should grow "down" not "across". Rather than having 255 - or
940 - *fields*, you should have 940 *records*.


John W. Vinson[MVP]
 
Back
Top