From: sheri on 8 Oct 2009 15:37 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!
From: Klatuu on 8 Oct 2009 15:58 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!
From: sheri on 8 Oct 2009 16:14 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!
From: Klatuu on 8 Oct 2009 16:27 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!
From: sheri on 8 Oct 2009 16:45 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!
|
Next
|
Last
Pages: 1 2 3 4 5 Prev: Please Help?? Split Database - Refering to backend Next: 89 Opinion Dsiconsolas.com |