Prev: Please Help?? Split Database - Refering to backend
Next: 89 Opinion Dsiconsolas.com
From: sheri on 8 Oct 2009 18:06
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 9 Oct 2009 14:26
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 9 Oct 2009 17:28
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 9 Oct 2009 18:45
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 11:36
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!
> >>
> >>
> >>
>
>
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Please Help?? Split Database - Refering to backend
Next: 89 Opinion Dsiconsolas.com