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
|
|||
|
|||
New User - Linking tables
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! |
#2
|
|||
|
|||
New User - Linking tables
Post the SQL of the query that is returning the duplicates, please.
To do that, open the query in the query builder, select SQL view, and copy/paste it into a reply and we will have a look. -- Dave Hargis, Microsoft Access MVP "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! |
#3
|
|||
|
|||
New User - Linking tables
Here you go - Yeh-haw:
SELECT [Expenses SPA DEV].ID, [LastName] & ", " & [FirstName] AS [Full Name], [Expenses SPA DEV].PAYEE, [Expenses SPA DEV].[CHECK DATE], [Expenses SPA DEV].[CHECK NO], [Expenses SPA DEV].AMOUNT AS [Expenses SPA DEV_AMOUNT], [Expenses SPA DEV].DESCRIPTION, [All Donations].CONTRIBUTOR, [All Donations].AMOUNT AS [All Donations_AMOUNT], [All Donations].DATE, [All Donations].[GENERAL USE OF FUNDS] FROM [SPA DEV Students] INNER JOIN ([All Donations] INNER JOIN [Expenses SPA DEV] ON [All Donations].[Student ID] = [Expenses SPA DEV].ID) ON ([SPA DEV Students].ID = [All Donations].[Student ID]) AND ([SPA DEV Students].ID = [Expenses SPA DEV].ID); "Klatuu" wrote: Post the SQL of the query that is returning the duplicates, please. To do that, open the query in the query builder, select SQL view, and copy/paste it into a reply and we will have a look. -- Dave Hargis, Microsoft Access MVP "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! |
#4
|
|||
|
|||
New User - Linking tables
A bit complex, so it is hard to tell without having it in front of me, but it
could be how you are joining the tables. Is there any reason not to use a LEFT join rather than an INNER join? -- Dave Hargis, Microsoft Access MVP "sheri" wrote: Here you go - Yeh-haw: SELECT [Expenses SPA DEV].ID, [LastName] & ", " & [FirstName] AS [Full Name], [Expenses SPA DEV].PAYEE, [Expenses SPA DEV].[CHECK DATE], [Expenses SPA DEV].[CHECK NO], [Expenses SPA DEV].AMOUNT AS [Expenses SPA DEV_AMOUNT], [Expenses SPA DEV].DESCRIPTION, [All Donations].CONTRIBUTOR, [All Donations].AMOUNT AS [All Donations_AMOUNT], [All Donations].DATE, [All Donations].[GENERAL USE OF FUNDS] FROM [SPA DEV Students] INNER JOIN ([All Donations] INNER JOIN [Expenses SPA DEV] ON [All Donations].[Student ID] = [Expenses SPA DEV].ID) ON ([SPA DEV Students].ID = [All Donations].[Student ID]) AND ([SPA DEV Students].ID = [Expenses SPA DEV].ID); "Klatuu" wrote: Post the SQL of the query that is returning the duplicates, please. To do that, open the query in the query builder, select SQL view, and copy/paste it into a reply and we will have a look. -- Dave Hargis, Microsoft Access MVP "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! |
#5
|
|||
|
|||
New User - Linking tables
Sorry don't even know what that means or how it gets there.
"Klatuu" wrote: A bit complex, so it is hard to tell without having it in front of me, but it could be how you are joining the tables. Is there any reason not to use a LEFT join rather than an INNER join? -- Dave Hargis, Microsoft Access MVP "sheri" wrote: Here you go - Yeh-haw: SELECT [Expenses SPA DEV].ID, [LastName] & ", " & [FirstName] AS [Full Name], [Expenses SPA DEV].PAYEE, [Expenses SPA DEV].[CHECK DATE], [Expenses SPA DEV].[CHECK NO], [Expenses SPA DEV].AMOUNT AS [Expenses SPA DEV_AMOUNT], [Expenses SPA DEV].DESCRIPTION, [All Donations].CONTRIBUTOR, [All Donations].AMOUNT AS [All Donations_AMOUNT], [All Donations].DATE, [All Donations].[GENERAL USE OF FUNDS] FROM [SPA DEV Students] INNER JOIN ([All Donations] INNER JOIN [Expenses SPA DEV] ON [All Donations].[Student ID] = [Expenses SPA DEV].ID) ON ([SPA DEV Students].ID = [All Donations].[Student ID]) AND ([SPA DEV Students].ID = [Expenses SPA DEV].ID); "Klatuu" wrote: Post the SQL of the query that is returning the duplicates, please. To do that, open the query in the query builder, select SQL view, and copy/paste it into a reply and we will have a look. -- Dave Hargis, Microsoft Access MVP "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! |
#6
|
|||
|
|||
New User - Linking tables
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! |
#7
|
|||
|
|||
New User - Linking tables
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! |
#8
|
|||
|
|||
New User - Linking tables
I don't have a "main table" per se. I tried changing one of the joins and
now it's saying "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258)" So, I'll try doing that. "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! |
#9
|
|||
|
|||
New User - Linking tables
Unless I am mistaken, in this case, Expenses SPA DEV would be your main table.
-- Dave Hargis, Microsoft Access MVP "sheri" wrote: I don't have a "main table" per se. I tried changing one of the joins and now it's saying "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258)" So, I'll try doing that. "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! |
#10
|
|||
|
|||
New User - Linking tables
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! |
Thread Tools | |
Display Modes | |
|
|