If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Tables
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? |
#2
|
|||
|
|||
Multiple Tables
We need more information on the relationships between the tables. Tell us
more about the primary keys and the actual fields. "Cortney" wrote in message ... 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? |
#3
|
|||
|
|||
Multiple Tables
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? "Pat Hartman(MVP)" wrote: We need more information on the relationships between the tables. Tell us more about the primary keys and the actual fields. "Cortney" wrote in message ... 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? |
#4
|
|||
|
|||
Multiple Tables
On Mon, 21 Nov 2005 13:16:28 -0800, "Cortney"
wrote: 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] |
#5
|
|||
|
|||
Multiple Tables
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? "John Vinson" wrote: On Mon, 21 Nov 2005 13:16:28 -0800, "Cortney" wrote: 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] |
#6
|
|||
|
|||
Multiple Tables
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? "Cortney" wrote: 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? "John Vinson" wrote: On Mon, 21 Nov 2005 13:16:28 -0800, "Cortney" wrote: 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] |
#7
|
|||
|
|||
Multiple Tables
On Tue, 22 Nov 2005 08:02:08 -0800, "Cortney"
wrote: 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] |
#8
|
|||
|
|||
Multiple Tables
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. "John Vinson" wrote: On Tue, 22 Nov 2005 08:02:08 -0800, "Cortney" wrote: 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] |
#9
|
|||
|
|||
Multiple Tables
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. "Cortney" wrote: 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. "John Vinson" wrote: On Tue, 22 Nov 2005 08:02:08 -0800, "Cortney" wrote: 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] |
#10
|
|||
|
|||
Multiple Tables
On Tue, 22 Nov 2005 11:46:06 -0800, "Cortney"
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Split Database: Keep Tables in single Access file or multiple files ??? | Will | General Discussion | 2 | August 24th, 2005 05:31 AM |
How do you query by form on multiple tables? | Jo | General Discussion | 1 | August 1st, 2005 06:18 PM |
Can Access compare multiple fields from multiple tables? | Comparing Multiple Fields | General Discussion | 1 | June 30th, 2005 08:22 PM |
Using Multiple LOOKUP tables | KG | General Discussion | 3 | May 7th, 2005 01:00 AM |
Multiple tables on to one form | LMB | New Users | 4 | May 23rd, 2004 03:35 AM |