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 |
#11
|
|||
|
|||
New User - Linking tables
Now I've written a separate query for both the allocated donations and the
expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#12
|
|||
|
|||
New User - Linking tables
Is there a chance that you are expecting to get 'single line' results in
your query? Because if you join tables together, you'll get one row for each combination that matches your criteria. Sometimes, that means you see what you may describe as "duplicates" for some of the fields. If you are only getting some of the fields "duplicated", but each row containing a unique combination of values, then consider using a report to display/print out the results. You could use those so-called duplicates to "group by", and only show each one once in the report. Good luck! Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... Now I've written a separate query for both the allocated donations and the expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#13
|
|||
|
|||
New User - Linking tables
At first yes, but I figured out that part. I'm okay with the query being
what it is but why does the report show duplicates? I've tried everything I know to get this to report properly and it seems so basic, yet it still doesn't work. What it wants to do is match every donation with every check for each ID. If there is something I'm missing please let me know. "Jeff Boyce" wrote: Is there a chance that you are expecting to get 'single line' results in your query? Because if you join tables together, you'll get one row for each combination that matches your criteria. Sometimes, that means you see what you may describe as "duplicates" for some of the fields. If you are only getting some of the fields "duplicated", but each row containing a unique combination of values, then consider using a report to display/print out the results. You could use those so-called duplicates to "group by", and only show each one once in the report. Good luck! Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... Now I've written a separate query for both the allocated donations and the expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#14
|
|||
|
|||
New User - Linking tables
I'm still having a bit of difficulty envisioning the data & relationships.
Could you describe your table structure a bit more? Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... At first yes, but I figured out that part. I'm okay with the query being what it is but why does the report show duplicates? I've tried everything I know to get this to report properly and it seems so basic, yet it still doesn't work. What it wants to do is match every donation with every check for each ID. If there is something I'm missing please let me know. "Jeff Boyce" wrote: Is there a chance that you are expecting to get 'single line' results in your query? Because if you join tables together, you'll get one row for each combination that matches your criteria. Sometimes, that means you see what you may describe as "duplicates" for some of the fields. If you are only getting some of the fields "duplicated", but each row containing a unique combination of values, then consider using a report to display/print out the results. You could use those so-called duplicates to "group by", and only show each one once in the report. Good luck! Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... Now I've written a separate query for both the allocated donations and the expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#15
|
|||
|
|||
New User - Linking tables
Table 1 Students - List all students (name, grad date, address, etc)
Table 2 Donations - Has all donations, some related to a particular student (by Key from table 1), others related to specific expenses (donation key entered into table 3) and still others not related to anything. (Date, Donor, Amount, Program, Description, Type, Student ID) Table 3 Expenses - Has all checks written, some related to a particular student (by Key from table 1), others related to specific Donations (by Donation key from table 2)and still others not related to anything. (Date, Payee, Amount, Check no, Amount, Description, Donation Key, Student ID) I'm looking to get a report showing all donations and all expenses related to a particular student: ID: 40 Student: Sarah C Grad Date: 6/30/08 Donations: $1000 Jackson Foundation 1/1/09 Scholarship for Sarah C $5000 Rancho Foundation 6/30/08 Scholarship for Sarah C Expenses: $250 Bookstore 2/1/09 College books $2500 State College 9/1/08 College Tuition Remaining Balance: $3250 This just seems like a no brainer but it's not coming easily that's for sure. While I could combine the two tables to get what I want, the issue is that there are many more unrelated donations that I thought it would be best to keep separate tables. I have a query that pulls all donations that have a student ID and one that pulls all checks that have a student ID. I have a third that pulls those two together, but I don't think this one is necessary for the report I want. I've tried relating and unrelating the student ID in these queries and neither seems to work. HELP!! "Jeff Boyce" wrote: I'm still having a bit of difficulty envisioning the data & relationships. Could you describe your table structure a bit more? Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... At first yes, but I figured out that part. I'm okay with the query being what it is but why does the report show duplicates? I've tried everything I know to get this to report properly and it seems so basic, yet it still doesn't work. What it wants to do is match every donation with every check for each ID. If there is something I'm missing please let me know. "Jeff Boyce" wrote: Is there a chance that you are expecting to get 'single line' results in your query? Because if you join tables together, you'll get one row for each combination that matches your criteria. Sometimes, that means you see what you may describe as "duplicates" for some of the fields. If you are only getting some of the fields "duplicated", but each row containing a unique combination of values, then consider using a report to display/print out the results. You could use those so-called duplicates to "group by", and only show each one once in the report. Good luck! Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... Now I've written a separate query for both the allocated donations and the expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#16
|
|||
|
|||
New User - Linking tables
What was missing from your description was the primary key for the
table1-Students. Can I assume it is StudentID? Create a new query in design view, put [tblStudents] in there, put [tblDonations] in there and put [tblExpenses] in there. Now drag StudentID from tblStudent to StudentID in [tblDonations]. Do the same again to [tblExpenses]. Click on each join line, right-click, select Properties, and set the joins to "LEFT" joins (i.e., all [tblStudent] records and ANY matching from the joined-to table. Do this with each join line. This query should give you all students and any Donations and/or Expenses. Use that as the source for your report, grouping on Student, and possibly on Donation and on Expense. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "sheri" wrote in message ... Table 1 Students - List all students (name, grad date, address, etc) Table 2 Donations - Has all donations, some related to a particular student (by Key from table 1), others related to specific expenses (donation key entered into table 3) and still others not related to anything. (Date, Donor, Amount, Program, Description, Type, Student ID) Table 3 Expenses - Has all checks written, some related to a particular student (by Key from table 1), others related to specific Donations (by Donation key from table 2)and still others not related to anything. (Date, Payee, Amount, Check no, Amount, Description, Donation Key, Student ID) I'm looking to get a report showing all donations and all expenses related to a particular student: ID: 40 Student: Sarah C Grad Date: 6/30/08 Donations: $1000 Jackson Foundation 1/1/09 Scholarship for Sarah C $5000 Rancho Foundation 6/30/08 Scholarship for Sarah C Expenses: $250 Bookstore 2/1/09 College books $2500 State College 9/1/08 College Tuition Remaining Balance: $3250 This just seems like a no brainer but it's not coming easily that's for sure. While I could combine the two tables to get what I want, the issue is that there are many more unrelated donations that I thought it would be best to keep separate tables. I have a query that pulls all donations that have a student ID and one that pulls all checks that have a student ID. I have a third that pulls those two together, but I don't think this one is necessary for the report I want. I've tried relating and unrelating the student ID in these queries and neither seems to work. HELP!! "Jeff Boyce" wrote: I'm still having a bit of difficulty envisioning the data & relationships. Could you describe your table structure a bit more? Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... At first yes, but I figured out that part. I'm okay with the query being what it is but why does the report show duplicates? I've tried everything I know to get this to report properly and it seems so basic, yet it still doesn't work. What it wants to do is match every donation with every check for each ID. If there is something I'm missing please let me know. "Jeff Boyce" wrote: Is there a chance that you are expecting to get 'single line' results in your query? Because if you join tables together, you'll get one row for each combination that matches your criteria. Sometimes, that means you see what you may describe as "duplicates" for some of the fields. If you are only getting some of the fields "duplicated", but each row containing a unique combination of values, then consider using a report to display/print out the results. You could use those so-called duplicates to "group by", and only show each one once in the report. Good luck! Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... Now I've written a separate query for both the allocated donations and the expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#17
|
|||
|
|||
New User - Linking tables
Yes Student ID - I thought that was a given. Anyway, still not working. I'm
convinced that Access can't handle two unrelated tables in a report. It's trying to hard to relate the data and other than the fact that they both have a relationship with the Student table they really aren't related. This really seems like it should be easy though, so maybe somehow I'll figure it out. Thanks for trying. "Jeff Boyce" wrote: What was missing from your description was the primary key for the table1-Students. Can I assume it is StudentID? Create a new query in design view, put [tblStudents] in there, put [tblDonations] in there and put [tblExpenses] in there. Now drag StudentID from tblStudent to StudentID in [tblDonations]. Do the same again to [tblExpenses]. Click on each join line, right-click, select Properties, and set the joins to "LEFT" joins (i.e., all [tblStudent] records and ANY matching from the joined-to table. Do this with each join line. This query should give you all students and any Donations and/or Expenses. Use that as the source for your report, grouping on Student, and possibly on Donation and on Expense. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "sheri" wrote in message ... Table 1 Students - List all students (name, grad date, address, etc) Table 2 Donations - Has all donations, some related to a particular student (by Key from table 1), others related to specific expenses (donation key entered into table 3) and still others not related to anything. (Date, Donor, Amount, Program, Description, Type, Student ID) Table 3 Expenses - Has all checks written, some related to a particular student (by Key from table 1), others related to specific Donations (by Donation key from table 2)and still others not related to anything. (Date, Payee, Amount, Check no, Amount, Description, Donation Key, Student ID) I'm looking to get a report showing all donations and all expenses related to a particular student: ID: 40 Student: Sarah C Grad Date: 6/30/08 Donations: $1000 Jackson Foundation 1/1/09 Scholarship for Sarah C $5000 Rancho Foundation 6/30/08 Scholarship for Sarah C Expenses: $250 Bookstore 2/1/09 College books $2500 State College 9/1/08 College Tuition Remaining Balance: $3250 This just seems like a no brainer but it's not coming easily that's for sure. While I could combine the two tables to get what I want, the issue is that there are many more unrelated donations that I thought it would be best to keep separate tables. I have a query that pulls all donations that have a student ID and one that pulls all checks that have a student ID. I have a third that pulls those two together, but I don't think this one is necessary for the report I want. I've tried relating and unrelating the student ID in these queries and neither seems to work. HELP!! "Jeff Boyce" wrote: I'm still having a bit of difficulty envisioning the data & relationships. Could you describe your table structure a bit more? Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... At first yes, but I figured out that part. I'm okay with the query being what it is but why does the report show duplicates? I've tried everything I know to get this to report properly and it seems so basic, yet it still doesn't work. What it wants to do is match every donation with every check for each ID. If there is something I'm missing please let me know. "Jeff Boyce" wrote: Is there a chance that you are expecting to get 'single line' results in your query? Because if you join tables together, you'll get one row for each combination that matches your criteria. Sometimes, that means you see what you may describe as "duplicates" for some of the fields. If you are only getting some of the fields "duplicated", but each row containing a unique combination of values, then consider using a report to display/print out the results. You could use those so-called duplicates to "group by", and only show each one once in the report. Good luck! Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... Now I've written a separate query for both the allocated donations and the expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#18
|
|||
|
|||
New User - Linking tables
Perhaps a difference of definition...
I would state that your Expenses and Donations tables ARE related, via StudentID. But let me check ... are you using StudentID in each of those as a foreign key, or are you using the Access Autonumber to create a new (and unique) "StudentID"? If the latter, there's no way Access would know how to connect the three tables, because Autonumber is unique TO-THE-TABLE. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "sheri" wrote in message ... Yes Student ID - I thought that was a given. Anyway, still not working. I'm convinced that Access can't handle two unrelated tables in a report. It's trying to hard to relate the data and other than the fact that they both have a relationship with the Student table they really aren't related. This really seems like it should be easy though, so maybe somehow I'll figure it out. Thanks for trying. "Jeff Boyce" wrote: What was missing from your description was the primary key for the table1-Students. Can I assume it is StudentID? Create a new query in design view, put [tblStudents] in there, put [tblDonations] in there and put [tblExpenses] in there. Now drag StudentID from tblStudent to StudentID in [tblDonations]. Do the same again to [tblExpenses]. Click on each join line, right-click, select Properties, and set the joins to "LEFT" joins (i.e., all [tblStudent] records and ANY matching from the joined-to table. Do this with each join line. This query should give you all students and any Donations and/or Expenses. Use that as the source for your report, grouping on Student, and possibly on Donation and on Expense. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "sheri" wrote in message ... Table 1 Students - List all students (name, grad date, address, etc) Table 2 Donations - Has all donations, some related to a particular student (by Key from table 1), others related to specific expenses (donation key entered into table 3) and still others not related to anything. (Date, Donor, Amount, Program, Description, Type, Student ID) Table 3 Expenses - Has all checks written, some related to a particular student (by Key from table 1), others related to specific Donations (by Donation key from table 2)and still others not related to anything. (Date, Payee, Amount, Check no, Amount, Description, Donation Key, Student ID) I'm looking to get a report showing all donations and all expenses related to a particular student: ID: 40 Student: Sarah C Grad Date: 6/30/08 Donations: $1000 Jackson Foundation 1/1/09 Scholarship for Sarah C $5000 Rancho Foundation 6/30/08 Scholarship for Sarah C Expenses: $250 Bookstore 2/1/09 College books $2500 State College 9/1/08 College Tuition Remaining Balance: $3250 This just seems like a no brainer but it's not coming easily that's for sure. While I could combine the two tables to get what I want, the issue is that there are many more unrelated donations that I thought it would be best to keep separate tables. I have a query that pulls all donations that have a student ID and one that pulls all checks that have a student ID. I have a third that pulls those two together, but I don't think this one is necessary for the report I want. I've tried relating and unrelating the student ID in these queries and neither seems to work. HELP!! "Jeff Boyce" wrote: I'm still having a bit of difficulty envisioning the data & relationships. Could you describe your table structure a bit more? Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... At first yes, but I figured out that part. I'm okay with the query being what it is but why does the report show duplicates? I've tried everything I know to get this to report properly and it seems so basic, yet it still doesn't work. What it wants to do is match every donation with every check for each ID. If there is something I'm missing please let me know. "Jeff Boyce" wrote: Is there a chance that you are expecting to get 'single line' results in your query? Because if you join tables together, you'll get one row for each combination that matches your criteria. Sometimes, that means you see what you may describe as "duplicates" for some of the fields. If you are only getting some of the fields "duplicated", but each row containing a unique combination of values, then consider using a report to display/print out the results. You could use those so-called duplicates to "group by", and only show each one once in the report. Good luck! Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... Now I've written a separate query for both the allocated donations and the expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#19
|
|||
|
|||
New User - Linking tables
No the student ID is the Key from the Student table and I agree they are
related via the student ID but that doesn't seem to matter. I have another report that has a one to many relationship that works just fine (Donations and checks identified with the donation via the donation key). But for what I need it is a many to many relationship - I may be misusing this term - Many donations with many checks not specifically identified with the donations via the donation key. I think it should work but so far it hasn't (I've spent way too much time on this too!). I have a work around but it requires duplication of effort. Is there a way to have a form enter into two tables to avoid this? "Jeff Boyce" wrote: Perhaps a difference of definition... I would state that your Expenses and Donations tables ARE related, via StudentID. But let me check ... are you using StudentID in each of those as a foreign key, or are you using the Access Autonumber to create a new (and unique) "StudentID"? If the latter, there's no way Access would know how to connect the three tables, because Autonumber is unique TO-THE-TABLE. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "sheri" wrote in message ... Yes Student ID - I thought that was a given. Anyway, still not working. I'm convinced that Access can't handle two unrelated tables in a report. It's trying to hard to relate the data and other than the fact that they both have a relationship with the Student table they really aren't related. This really seems like it should be easy though, so maybe somehow I'll figure it out. Thanks for trying. "Jeff Boyce" wrote: What was missing from your description was the primary key for the table1-Students. Can I assume it is StudentID? Create a new query in design view, put [tblStudents] in there, put [tblDonations] in there and put [tblExpenses] in there. Now drag StudentID from tblStudent to StudentID in [tblDonations]. Do the same again to [tblExpenses]. Click on each join line, right-click, select Properties, and set the joins to "LEFT" joins (i.e., all [tblStudent] records and ANY matching from the joined-to table. Do this with each join line. This query should give you all students and any Donations and/or Expenses. Use that as the source for your report, grouping on Student, and possibly on Donation and on Expense. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "sheri" wrote in message ... Table 1 Students - List all students (name, grad date, address, etc) Table 2 Donations - Has all donations, some related to a particular student (by Key from table 1), others related to specific expenses (donation key entered into table 3) and still others not related to anything. (Date, Donor, Amount, Program, Description, Type, Student ID) Table 3 Expenses - Has all checks written, some related to a particular student (by Key from table 1), others related to specific Donations (by Donation key from table 2)and still others not related to anything. (Date, Payee, Amount, Check no, Amount, Description, Donation Key, Student ID) I'm looking to get a report showing all donations and all expenses related to a particular student: ID: 40 Student: Sarah C Grad Date: 6/30/08 Donations: $1000 Jackson Foundation 1/1/09 Scholarship for Sarah C $5000 Rancho Foundation 6/30/08 Scholarship for Sarah C Expenses: $250 Bookstore 2/1/09 College books $2500 State College 9/1/08 College Tuition Remaining Balance: $3250 This just seems like a no brainer but it's not coming easily that's for sure. While I could combine the two tables to get what I want, the issue is that there are many more unrelated donations that I thought it would be best to keep separate tables. I have a query that pulls all donations that have a student ID and one that pulls all checks that have a student ID. I have a third that pulls those two together, but I don't think this one is necessary for the report I want. I've tried relating and unrelating the student ID in these queries and neither seems to work. HELP!! "Jeff Boyce" wrote: I'm still having a bit of difficulty envisioning the data & relationships. Could you describe your table structure a bit more? Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... At first yes, but I figured out that part. I'm okay with the query being what it is but why does the report show duplicates? I've tried everything I know to get this to report properly and it seems so basic, yet it still doesn't work. What it wants to do is match every donation with every check for each ID. If there is something I'm missing please let me know. "Jeff Boyce" wrote: Is there a chance that you are expecting to get 'single line' results in your query? Because if you join tables together, you'll get one row for each combination that matches your criteria. Sometimes, that means you see what you may describe as "duplicates" for some of the fields. If you are only getting some of the fields "duplicated", but each row containing a unique combination of values, then consider using a report to display/print out the results. You could use those so-called duplicates to "group by", and only show each one once in the report. Good luck! Regards Jeff Boyce Microsoft Access MVP "sheri" wrote in message ... Now I've written a separate query for both the allocated donations and the expenses that have ID's and a third to try to combine the data, but it's just not working. Even taking the two queries in a report doesn't work. It's like they are too related. Seems simple but can't get it to work. I just need to know donations and expenses by ID in one report. If there is another way please let me know. Otherwise I have to have another table with the info combined and that brings up other questions. "sheri" wrote: Okay so I wrote a query to identify all allocated donations (ones with an ID) and then I wrote another query using that query and adding the expenses table but it still gives me multiple records no matter how the join is performed. "Klatuu" wrote: That helps some. In the query builder, there should be a line between the main table and the other tables. If you right click on the line to get a dialog that shows the join type, try choosing the option to show all the records in the main table and only matching record is the other tables. This will not give you exactly what you want, but if we can get that far, we can work it from there. -- Dave Hargis, Microsoft Access MVP "sheri" wrote: The results are that every check is matched with every deposit. Does that help. I tried changing the Join to left in the SQL view and it said it wasn't supported. "sheri" wrote: Okay, so I'm using Access 2000 and I'm not a programer so please speak in plain english. I have a table of all donations made and a table of all checks cut out of our program. The relationship between the tables is an ID if applicable and in some cases a donation key. Some, but not all, donations are related to checks and visa-versa and not all donations or checks have an ID. I'm trying to run a report by ID that shows both the donations and checks. I've designed a simple query to pull the information together but the results are showing a lot of duplicate records and I'm not sure why. Thanks in advance! |
#20
|
|||
|
|||
New User - Linking tables
That sounds like a work-around ... and those have a way of coming back to
bite you! Let me try paraphrasing your earlier description of your table structure, to make sure I understand where this starts: tblStudent StudentID FName LName GradDate Address etc. tblDonation DonationID DonationDate Donor Amount Program Description Type StudentID (foreign key, refers to tblStudent record) ??Expense related?? (how do you show this?) ??Related to nothing?? (how do you show this?) tblExpense ExpenseID StudentID (foreign key) DonationID (? I don't understand how an expense is related to a donation) Payee Amount CheckNo Description If I had three tables like this, I'd add all three to a new query, join tblStudent to tblDonation and to tblExpense on the StudentID field, then change the join type to "LEFT" (i.e., directional) joins (all of tblStudent records, no matter whether there are any records in either Donation or Expense). I guess I'm still not seeing the big picture here... Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. |
Thread Tools | |
Display Modes | |
|
|