compile data from unrelated tables

[CuriousMark]

How do I create a query to collect data from "unrelated" tables?

I am studying Medicare physician reimbursement data. Here is an abbreviated
version of the tables with example data:

table Name: 2007_01_01
Fields: [CPT], [RVU]
Data: 1, 10
2, 15
3, 20

table Name: tblConvFactor
Fields: [Year], [ConvFactor]
Data: 2005, 35
2006, 35.5
2007, 36

table Name: tblGPCI
Fields: [Table Name], [GPCI]
Data: 2006_01_01, 1.2
2007_01_01, 1.3
2008_01_01, 1.1

There are many tables containing the RVU data, each named with a date in the
2007_01_01 format. That name is used in the tblGPCI table.

How do I construct a query that will give me this, selecting data from table
1 for a specific CPT (in this example, "2")?

[Year], [RVU], [GPCI], [Conv Factor]
2007, 15, 1.3, 36

In reality, the tables have many more fields, but the concept is the same. I
have three individual queries for each table, but I can't figure out how to
link them to create one record. I could try to create a new table, then a
recordset, and add data to the recordset, but that seems cumbersome, and I
thought there must be a way to create a query to do it.

Thanks.

CM

 

[KARL DEWEY]

There are many tables containing the RVU data, each named with a date in
the 2007_01_01 format.
You should not have multiple tables like this - just one with a field
containing the '2007_01_01'.
Use a UNION query to pull the data together like this --
SELECT "2007_01_01" AS [Table Name], [CPT], [RVU]
FROM [2007_01_01]
UNION ALL SELECT "2006_01_01" AS [Table Name], [CPT], [RVU]
FROM [2006_01_01]
UNION ALL SELECT "2008_01_01" AS [Table Name], [CPT], [RVU]
FROM [2008_01_01];

 

[CuriousMark]

Thanks Karl, but not exactly what I want. There are about 20 tables (Medicare
comes out with a new fee schedule every few months), each with thousands of
records, so I don't think combining them all into one table with an added
field name that contains the date is best way to go. It them becomes
impractical to include all that table names in the SELECT statement the way
you suggest.

I should have been more clear. I plan to loop through each "date" table to
extract the RVU data for a specific CPT code from each table. I can use the
name of the table to extract the GPCI and Conv Factor data from the other two
tables. I was hoping there was a way to combine them into one row of a single
query.

wrote:

the 2007_01_01 format.
You should not have multiple tables like this - just one with a field
containing the '2007_01_01'.
Use a UNION query to pull the data together like this --
SELECT "2007_01_01" AS [Table Name], [CPT], [RVU]
FROM [2007_01_01]
UNION ALL SELECT "2006_01_01" AS [Table Name], [CPT], [RVU]
FROM [2006_01_01]
UNION ALL SELECT "2008_01_01" AS [Table Name], [CPT], [RVU]
FROM [2008_01_01];

--
KARL DEWEY
Build a little - Test a little

How do I create a query to collect data from "unrelated" tables?

I am studying Medicare physician reimbursement data. Here is an abbreviated
version of the tables with example data:

table Name: 2007_01_01
Fields: [CPT], [RVU]
Data: 1, 10
2, 15
3, 20

table Name: tblConvFactor
Fields: [Year], [ConvFactor]
Data: 2005, 35
2006, 35.5
2007, 36

table Name: tblGPCI
Fields: [Table Name], [GPCI]
Data: 2006_01_01, 1.2
2007_01_01, 1.3
2008_01_01, 1.1

There are many tables containing the RVU data, each named with a date in the
2007_01_01 format. That name is used in the tblGPCI table.

How do I construct a query that will give me this, selecting data from table
1 for a specific CPT (in this example, "2")?

[Year], [RVU], [GPCI], [Conv Factor]
2007, 15, 1.3, 36

In reality, the tables have many more fields, but the concept is the same. I
have three individual queries for each table, but I can't figure out how to
link them to create one record. I could try to create a new table, then a
recordset, and add data to the recordset, but that seems cumbersome, and I
thought there must be a way to create a query to do it.

Thanks.

CM

 

[KARL DEWEY]

How do I create a query to collect data from "unrelated" tables?
The union query is the only way I know to relate the tables.
You can use the union query to make a table and then there after just append
to that table.
--
KARL DEWEY
Build a little - Test a little

Thanks Karl, but not exactly what I want. There are about 20 tables (Medicare
comes out with a new fee schedule every few months), each with thousands of
records, so I don't think combining them all into one table with an added
field name that contains the date is best way to go. It them becomes
impractical to include all that table names in the SELECT statement the way
you suggest.

I should have been more clear. I plan to loop through each "date" table to
extract the RVU data for a specific CPT code from each table. I can use the
name of the table to extract the GPCI and Conv Factor data from the other two
tables. I was hoping there was a way to combine them into one row of a single
query.

wrote:

There are many tables containing the RVU data, each named with a date in

the 2007_01_01 format.
You should not have multiple tables like this - just one with a field
containing the '2007_01_01'.
Use a UNION query to pull the data together like this --
SELECT "2007_01_01" AS [Table Name], [CPT], [RVU]
FROM [2007_01_01]
UNION ALL SELECT "2006_01_01" AS [Table Name], [CPT], [RVU]
FROM [2006_01_01]
UNION ALL SELECT "2008_01_01" AS [Table Name], [CPT], [RVU]
FROM [2008_01_01];

--
KARL DEWEY
Build a little - Test a little

How do I create a query to collect data from "unrelated" tables?

I am studying Medicare physician reimbursement data. Here is an abbreviated
version of the tables with example data:

table Name: 2007_01_01
Fields: [CPT], [RVU]
Data: 1, 10
2, 15
3, 20

table Name: tblConvFactor
Fields: [Year], [ConvFactor]
Data: 2005, 35
2006, 35.5
2007, 36

table Name: tblGPCI
Fields: [Table Name], [GPCI]
Data: 2006_01_01, 1.2
2007_01_01, 1.3
2008_01_01, 1.1

There are many tables containing the RVU data, each named with a date in the
2007_01_01 format. That name is used in the tblGPCI table.

How do I construct a query that will give me this, selecting data from table
1 for a specific CPT (in this example, "2")?

[Year], [RVU], [GPCI], [Conv Factor]
2007, 15, 1.3, 36

In reality, the tables have many more fields, but the concept is the same. I
have three individual queries for each table, but I can't figure out how to
link them to create one record. I could try to create a new table, then a
recordset, and add data to the recordset, but that seems cumbersome, and I
thought there must be a way to create a query to do it.

Thanks.

CM

 

[CuriousMark]

Thanks Karl, but I probably have not been clear enough. As I understand, the
UNION query will append the results together to create more records. That's
not what I want to do.

Let's say that qryOne retrieves 4 fields from a single record of tblOne, and
qryTwo retrieves 2 fields from a single record of tblTwo. How can I put those
single-record fields together to create a result that has only one record,
but now with all 7 fields? Is that possible with a query, or do I have to
create a new table/recordset and then add the data to the fields?

qryOne result: a, b, c, d
qryTwo result: 1, 2

How can I get this?: a, b, c, d, 1, 2

Thanks.

The union query is the only way I know to relate the tables.
You can use the union query to make a table and then there after just append
to that table.
--
KARL DEWEY
Build a little - Test a little

Thanks Karl, but not exactly what I want. There are about 20 tables (Medicare
comes out with a new fee schedule every few months), each with thousands of
records, so I don't think combining them all into one table with an added
field name that contains the date is best way to go. It them becomes
impractical to include all that table names in the SELECT statement the way
you suggest.

I should have been more clear. I plan to loop through each "date" table to
extract the RVU data for a specific CPT code from each table. I can use the
name of the table to extract the GPCI and Conv Factor data from the other two
tables. I was hoping there was a way to combine them into one row of a single
query.

wrote:

There are many tables containing the RVU data, each named with a date in
the 2007_01_01 format.
You should not have multiple tables like this - just one with a field
containing the '2007_01_01'.
Use a UNION query to pull the data together like this --
SELECT "2007_01_01" AS [Table Name], [CPT], [RVU]
FROM [2007_01_01]
UNION ALL SELECT "2006_01_01" AS [Table Name], [CPT], [RVU]
FROM [2006_01_01]
UNION ALL SELECT "2008_01_01" AS [Table Name], [CPT], [RVU]
FROM [2008_01_01];

--
KARL DEWEY
Build a little - Test a little


:

How do I create a query to collect data from "unrelated" tables?

I am studying Medicare physician reimbursement data. Here is an abbreviated
version of the tables with example data:

table Name: 2007_01_01
Fields: [CPT], [RVU]
Data: 1, 10
2, 15
3, 20

table Name: tblConvFactor
Fields: [Year], [ConvFactor]
Data: 2005, 35
2006, 35.5
2007, 36

table Name: tblGPCI
Fields: [Table Name], [GPCI]
Data: 2006_01_01, 1.2
2007_01_01, 1.3
2008_01_01, 1.1

There are many tables containing the RVU data, each named with a date in the
2007_01_01 format. That name is used in the tblGPCI table.

How do I construct a query that will give me this, selecting data from table
1 for a specific CPT (in this example, "2")?

[Year], [RVU], [GPCI], [Conv Factor]
2007, 15, 1.3, 36

In reality, the tables have many more fields, but the concept is the same. I
have three individual queries for each table, but I can't figure out how to
link them to create one record. I could try to create a new table, then a
recordset, and add data to the recordset, but that seems cumbersome, and I
thought there must be a way to create a query to do it.

Thanks.

CM

 

[KARL DEWEY]

A UNION query will not append unless you make it into an append query.

I do not know what you are talking about when you say 4 fields and 2 fields
from a union query.
A union query does not do this. This is called 'concatenation.'

Your post was how to 'complie data from unrelated tables.' I showed you how
to relate the tables so you could retrieve the data using a union query.

--
KARL DEWEY
Build a little - Test a little

Thanks Karl, but I probably have not been clear enough. As I understand, the
UNION query will append the results together to create more records. That's
not what I want to do.

Let's say that qryOne retrieves 4 fields from a single record of tblOne, and
qryTwo retrieves 2 fields from a single record of tblTwo. How can I put those
single-record fields together to create a result that has only one record,
but now with all 7 fields? Is that possible with a query, or do I have to
create a new table/recordset and then add the data to the fields?

qryOne result: a, b, c, d
qryTwo result: 1, 2

How can I get this?: a, b, c, d, 1, 2

Thanks.

How do I create a query to collect data from "unrelated" tables?

The union query is the only way I know to relate the tables.
You can use the union query to make a table and then there after just append
to that table.
--
KARL DEWEY
Build a little - Test a little

Thanks Karl, but not exactly what I want. There are about 20 tables (Medicare
comes out with a new fee schedule every few months), each with thousands of
records, so I don't think combining them all into one table with an added
field name that contains the date is best way to go. It them becomes
impractical to include all that table names in the SELECT statement the way
you suggest.

I should have been more clear. I plan to loop through each "date" table to
extract the RVU data for a specific CPT code from each table. I can use the
name of the table to extract the GPCI and Conv Factor data from the other two
tables. I was hoping there was a way to combine them into one row of a single
query.

wrote:

There are many tables containing the RVU data, each named with a date in
the 2007_01_01 format.
You should not have multiple tables like this - just one with a field
containing the '2007_01_01'.
Use a UNION query to pull the data together like this --
SELECT "2007_01_01" AS [Table Name], [CPT], [RVU]
FROM [2007_01_01]
UNION ALL SELECT "2006_01_01" AS [Table Name], [CPT], [RVU]
FROM [2006_01_01]
UNION ALL SELECT "2008_01_01" AS [Table Name], [CPT], [RVU]
FROM [2008_01_01];

--
KARL DEWEY
Build a little - Test a little


:

How do I create a query to collect data from "unrelated" tables?

I am studying Medicare physician reimbursement data. Here is an abbreviated
version of the tables with example data:

table Name: 2007_01_01
Fields: [CPT], [RVU]
Data: 1, 10
2, 15
3, 20

table Name: tblConvFactor
Fields: [Year], [ConvFactor]
Data: 2005, 35
2006, 35.5
2007, 36

table Name: tblGPCI
Fields: [Table Name], [GPCI]
Data: 2006_01_01, 1.2
2007_01_01, 1.3
2008_01_01, 1.1

There are many tables containing the RVU data, each named with a date in the
2007_01_01 format. That name is used in the tblGPCI table.

How do I construct a query that will give me this, selecting data from table
1 for a specific CPT (in this example, "2")?

[Year], [RVU], [GPCI], [Conv Factor]
2007, 15, 1.3, 36

In reality, the tables have many more fields, but the concept is the same. I
have three individual queries for each table, but I can't figure out how to
link them to create one record. I could try to create a new table, then a
recordset, and add data to the recordset, but that seems cumbersome, and I
thought there must be a way to create a query to do it.

Thanks.

CM

 

[John W. Vinson/MVP]

Thanks Karl, but I probably have not been clear enough. As I understand, the
UNION query will append the results together to create more records. That's
not what I want to do.

Let's say that qryOne retrieves 4 fields from a single record of tblOne, and
qryTwo retrieves 2 fields from a single record of tblTwo. How can I put those
single-record fields together to create a result that has only one record,
but now with all 7 fields? Is that possible with a query, or do I have to
create a new table/recordset and then add the data to the fields?

qryOne result: a, b, c, d
qryTwo result: 1, 2

How can I get this?: a, b, c, d, 1, 2

IF - and it's a pretty big IF! - each table can be reliably counted on
to have only one record, then a Cartesian Join will work: add both
queries to the query grid with no join line.

However, if qryOne returns 100 records, and qryTwo returns 200, you
will get all 20000 possible combinations of records.

 

[CuriousMark]

IF - and it's a pretty big IF! - each table can be reliably counted on
to have only one record, then a Cartesian Join will work: add both
queries to the query grid with no join line.

However, if qryOne returns 100 records, and qryTwo returns 200, you
will get all 20000 possible combinations of records.

Thanks John. That is exactly what I needed!

CM