A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

New User - Linking tables



 
 
Thread Tools Display Modes
  #1  
Old October 8th, 2009, 08:37 PM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default 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  
Old October 8th, 2009, 08:58 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old October 8th, 2009, 09:14 PM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default 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  
Old October 8th, 2009, 09:27 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old October 8th, 2009, 09:45 PM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default 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  
Old October 8th, 2009, 09:54 PM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default 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  
Old October 8th, 2009, 10:19 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old October 8th, 2009, 10:40 PM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default 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  
Old October 8th, 2009, 10:48 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old October 8th, 2009, 10:54 PM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.