Prev: Please Help?? Split Database - Refering to backend
Next: 89 Opinion Dsiconsolas.com
From: Jeff Boyce on 12 Oct 2009 17:36
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" <sheri(a)discussions.microsoft.com> wrote in message
news:EE930F13-C36D-4061-822E-30409889C5E4(a)microsoft.com...
> 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" <sheri(a)discussions.microsoft.com> wrote in message
>> news:B211F905-751B-4F0E-A71F-256AF3CA5206(a)microsoft.com...
>> > 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" <sheri(a)discussions.microsoft.com> wrote in message
>> >> news:77264FDA-1C15-429B-9045-E1680666831C(a)microsoft.com...
>> >> > 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!
>> >>
>> >>
>> >>
>>
>>
>>


From: sheri on 12 Oct 2009 19:45
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" <sheri(a)discussions.microsoft.com> wrote in message
> news:EE930F13-C36D-4061-822E-30409889C5E4(a)microsoft.com...
> > 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" <sheri(a)discussions.microsoft.com> wrote in message
> >> news:B211F905-751B-4F0E-A71F-256AF3CA5206(a)microsoft.com...
> >> > 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" <sheri(a)discussions.microsoft.com> wrote in message
> >> >> news:77264FDA-1C15-429B-9045-E1680666831C(a)microsoft.com...
> >> >> > 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!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
From: Jeff Boyce on 13 Oct 2009 11:05
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" <sheri(a)discussions.microsoft.com> wrote in message
news:8827F86A-F013-4AA6-8299-BD49DA5281D8(a)microsoft.com...
> 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" <sheri(a)discussions.microsoft.com> wrote in message
>> news:EE930F13-C36D-4061-822E-30409889C5E4(a)microsoft.com...
>> > 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" <sheri(a)discussions.microsoft.com> wrote in message
>> >> news:B211F905-751B-4F0E-A71F-256AF3CA5206(a)microsoft.com...
>> >> > 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" <sheri(a)discussions.microsoft.com> wrote in message
>> >> >> news:77264FDA-1C15-429B-9045-E1680666831C(a)microsoft.com...
>> >> >> > 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!
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


From: sheri on 13 Oct 2009 14:24
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" <sheri(a)discussions.microsoft.com> wrote in message
> news:8827F86A-F013-4AA6-8299-BD49DA5281D8(a)microsoft.com...
> > 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" <sheri(a)discussions.microsoft.com> wrote in message
> >> news:EE930F13-C36D-4061-822E-30409889C5E4(a)microsoft.com...
> >> > 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" <sheri(a)discussions.microsoft.com> wrote in message
> >> >> news:B211F905-751B-4F0E-A71F-256AF3CA5206(a)microsoft.com...
> >> >> > 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" <sheri(a)discussions.microsoft.com> wrote in message
> >> >> >> news:77264FDA-1C15-429B-9045-E1680666831C(a)microsoft.com...
> >> >> >> > 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!
> >> >> >>
> >> >> >>
> >> >> >>
From: Jeff Boyce on 14 Oct 2009 12:29
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.


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Please Help?? Split Database - Refering to backend
Next: 89 Opinion Dsiconsolas.com