Multiple Tables

[Cortney]

I have a database with 3 tables where the data is imported monthly. One
table always has entries, the other two may not have entries. The only
fields they have in common are the customer name and the month. We were
using V-Lookups in excel to put the entries from the 2 tables in the correct
spot in the first and then importing. I would like to import them into the
three tables and then do a make table query to get all the information in the
correct spots. I can't seem to get the information to line up with the month
fields correctly. Does anyone have any suggestions on how I should
accomplish this?

 

[Pat Hartman\(MVP\)]

We need more information on the relationships between the tables. Tell us
more about the primary keys and the actual fields.

 

[Cortney]

There is a fourth table that has just a customer list with the customer name
as the primary key. The rest of the fields are financial information, a/r
aging, payments, charges, etc. I was able to create a form that brought the
information up for each month from each table using subforms but I couldn't
get it to report correctly. I just figured putting everything into one table
would make it easier to report. What do you think I should do?

 

[John Vinson]

There is a fourth table that has just a customer list with the customer name
as the primary key. The rest of the fields are financial information, a/r
aging, payments, charges, etc. I was able to create a form that brought the
information up for each month from each table using subforms but I couldn't
get it to report correctly. I just figured putting everything into one table
would make it easier to report. What do you think I should do?

Jamming all the information into one table is neither necessary nor
appropriate.

Instead, create a Query joining the Customer table to each of these
three tables; select each Join line and choose Option 2 (or maybe 3) -
"Show all records in Customers and matching records in <other table>".
Base your Report on this query. If there's nothing in the a/r table
for a given customer, that table's fields will just be NULL for that
customer, but you'll still see the customer records and the data from
the other two tables.

John W. Vinson[MVP]

 

[Cortney]

That worked great. I had tried to join them before but I used the date field
and it returned like 200 entries for each customer when I only have two
months of data in it so far.

The only problem I am running into now is that the query returns each month
twice for each customer. What can I do to change that?

 

[Cortney]

I am still having the problem with the date fields. The are not linking up
to the correct date from each table. Do you know how to fix that?

 

[John Vinson]

I am still having the problem with the date fields. The are not linking up
to the correct date from each table. Do you know how to fix that?

No, I don't - because I cannot see your table structure or your query
or the results you are seeing.

You can see your computer. I cannot. You're doing something wrong in
the query, but I don't know what the query IS because I cannot see it.

Please open the query in SQL view and copy and paste it to a message
here. Also give an example of what's in the table, and what you're
seeing that is not "linking up".


John W. Vinson[MVP]

 

[Cortney]

SELECT [AR Aging Percentages Query].[Billins Address Subgroup], [AR Aging
Percentages Query].[Commercial/Blue], [AR Aging Percentages
Query].[Contracted/Non Contracted], [AR Aging Percentages Query].Date AS [AR
Aging Percentages Query_Date], [AR Aging Percentages Query].[Full Denial as a
% of A/R], [AR Aging Percentages Query].[A/R balance 0-90 Days], [AR Aging
Percentages Query].[AR Balance 0-120 Days], [AR Aging Percentages Query].[AR
Balance 0-181 Days], [AR Aging Percentages Query].[A/R Balance > 181 Days],
[Payments to Charges Query].[Payments to Charges Ratio], [Days Billed
Outstanding].[Days Billed Outstanding]
FROM (([Payer Table] LEFT JOIN [Payments to Charges Query] ON [Payer
Table].[Billins Address Subgroup] = [Payments to Charges Query].[Billins
Address Subgroup]) LEFT JOIN [AR Aging Percentages Query] ON [Payer
Table].[Billins Address Subgroup] = [AR Aging Percentages Query].[Billins
Address Subgroup]) LEFT JOIN [Days Billed Outstanding] ON [Payer
Table].[Billins Address Subgroup] = [Days Billed Outstanding].[Billins
Address Subgroup];

Billins Address Subgroup Commercial/Blue Contracted/Non Contracted AR Aging
Percentages Query_Date Full Denial as a % of A/R A/R balance 0-90 Days AR
Balance 0-120 Days AR Balance 0-181 Days A/R Balance > 181 Days Payments to
Charges Ratio Days Billed Outstanding
BCBS OF
TN Blue Non-Contracted Sep-05 53.69% 54.99% 62.27% 76.84% 23.16% 73.13% 143
BCBS OF
TN Blue Non-Contracted Oct-05 56.52% 47.70% 57.99% 70.16% 29.84% 73.13% 143
BCBS OF
TN Blue Non-Contracted Sep-05 53.69% 54.99% 62.27% 76.84% 23.16% 79.14% 143
BCBS OF
TN Blue Non-Contracted Oct-05 56.52% 47.70% 57.99% 70.16% 29.84% 79.14% 143

This doesn't look pretty but the problem is that the last two entries,
Payments to charges ratio and days billed outstanding are not linking with
the date field in the Payer Profile table. It is duplicating the entries in
each month. In this instance, there are two months of data in the payer
profile, there is an entry for each month in the Payments to Charges Ratio (
Sept is 73% Oct is 79%) but there is only one month in the days billed
outstanding field(Oct is 143) . It is pulling the numbers in for each month
whether is corresponds with the correct month or not. I was wondering if I
could join the date fields somehow or do I need to set up my tables
different? Each table has a date field with the format mmm-yy.

 

[Cortney]

I was able to link it through subforms by linking the name and the date on
the master/child fields. The only reason I was trying to go a different
route was because I could not get the information to report correctly, it was
just coming from the main form rather than all the forms. If you knew a way
to print the subform results as well as the main form results, I would be
able to keep the subform format. It was easier than this, that's for sure.

SELECT [AR Aging Percentages Query].[Billins Address Subgroup], [AR Aging
Percentages Query].[Commercial/Blue], [AR Aging Percentages
Query].[Contracted/Non Contracted], [AR Aging Percentages Query].Date AS [AR
Aging Percentages Query_Date], [AR Aging Percentages Query].[Full Denial as a
% of A/R], [AR Aging Percentages Query].[A/R balance 0-90 Days], [AR Aging
Percentages Query].[AR Balance 0-120 Days], [AR Aging Percentages Query].[AR
Balance 0-181 Days], [AR Aging Percentages Query].[A/R Balance > 181 Days],
[Payments to Charges Query].[Payments to Charges Ratio], [Days Billed
Outstanding].[Days Billed Outstanding]
FROM (([Payer Table] LEFT JOIN [Payments to Charges Query] ON [Payer
Table].[Billins Address Subgroup] = [Payments to Charges Query].[Billins
Address Subgroup]) LEFT JOIN [AR Aging Percentages Query] ON [Payer
Table].[Billins Address Subgroup] = [AR Aging Percentages Query].[Billins
Address Subgroup]) LEFT JOIN [Days Billed Outstanding] ON [Payer
Table].[Billins Address Subgroup] = [Days Billed Outstanding].[Billins
Address Subgroup];

Billins Address Subgroup Commercial/Blue Contracted/Non Contracted AR Aging
Percentages Query_Date Full Denial as a % of A/R A/R balance 0-90 Days AR
Balance 0-120 Days AR Balance 0-181 Days A/R Balance > 181 Days Payments to
Charges Ratio Days Billed Outstanding
BCBS OF
TN Blue Non-Contracted Sep-05 53.69% 54.99% 62.27% 76.84% 23.16% 73.13% 143
BCBS OF
TN Blue Non-Contracted Oct-05 56.52% 47.70% 57.99% 70.16% 29.84% 73.13% 143
BCBS OF
TN Blue Non-Contracted Sep-05 53.69% 54.99% 62.27% 76.84% 23.16% 79.14% 143
BCBS OF
TN Blue Non-Contracted Oct-05 56.52% 47.70% 57.99% 70.16% 29.84% 79.14% 143

This doesn't look pretty but the problem is that the last two entries,
Payments to charges ratio and days billed outstanding are not linking with
the date field in the Payer Profile table. It is duplicating the entries in
each month. In this instance, there are two months of data in the payer
profile, there is an entry for each month in the Payments to Charges Ratio (
Sept is 73% Oct is 79%) but there is only one month in the days billed
outstanding field(Oct is 143) . It is pulling the numbers in for each month
whether is corresponds with the correct month or not. I was wondering if I
could join the date fields somehow or do I need to set up my tables
different? Each table has a date field with the format mmm-yy.

No, I don't - because I cannot see your table structure or your query
or the results you are seeing.

You can see your computer. I cannot. You're doing something wrong in
the query, but I don't know what the query IS because I cannot see it.

Please open the query in SQL view and copy and paste it to a message
here. Also give an example of what's in the table, and what you're
seeing that is not "linking up".


John W. Vinson[MVP]

 

[John Vinson]

If you knew a way
to print the subform results as well as the main form results, I would be
able to keep the subform format.

Well, the results aren't in the Subform. They're in the subform's
Recordsource (table or query). The form/subform is of course just a
window, not a data storage medium.

If you base the Report on a query joining the mainform table to the
subform table, and pull the date fields from the subform table, you'll
see the results that exist in the table (whether they are the results
you want or not depends on how your table is structured!)

John W. Vinson[MVP]