multiple table query for a report

G

Guest

I have a table with a person's info and two tables of photos associated with
the person. Here are the relevant tables and fields for the query:

Personal Information (Table):
PersonalID (PK)
First Name
Last Name
CategoryID
SubCategoryID

Photographs (Table):
PersonalID (FK)
PhotoFileName

TagIdentifiers:
PersonalID (FK)
Tag

GangCategories (Table):
CategoryID
GangCategory

GangSubCategories (Table):
SubCategoryID
GangSubCategory

I have created a sample person and a query with this sql statement:

SELECT [Personal Information].[First Name], [Personal Information].[Last
Name], GangCategories.GangCategory, GangSubCategories.GangSubCategory,
Photographs.PhotoFileName, TagIdentifiers.Tag
FROM ((([Personal Information] LEFT JOIN GangCategories ON [Personal
Information].CategoryID=GangCategories.CategoryID)
LEFT JOIN GangSubCategories ON [Personal
Information].SubCategoryID=GangSubCategories.SubCategoryID)
LEFT JOIN Photographs ON [Personal
Information].PersonalID=Photographs.PersonalID)
LEFT JOIN TagIdentifiers ON [Personal
Information].PersonalID=TagIdentifiers.PersonalID
WHERE ((([Personal Information].[PersonalID])= 25));

Which gives these results:

First Name Last Name GangCategory GangSubCategory PhotoFileName Tag
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag3.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag3.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag3.jpg

But I would prefer a result like this, where photos or tags aren't duplicated:

First Name Last Name GangCategory GangSubCategory PhotoFileName Tag
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag3.jpg

What I would like to do is use this for a report that would display the
person's info and each photo and tag below that. The result I have now will
show the person's info then the first photo, all three tags, the second
photo, all three tags, and on. How do I fix this? Any help would be
appreciated.
 
D

Duane Hookom

Are the tags related to the photographs?
A single query like this will not work. You might want to consider
subreports.
 
G

Guest

No, the tags and photographs are both related to the Personal Information
table. I have a Personal Information form that has two subforms, one for tags
and one for photographs. I'll look into creating subreports to handle this.

Duane Hookom said:
Are the tags related to the photographs?
A single query like this will not work. You might want to consider
subreports.

--
Duane Hookom
MS Access MVP
--

David Tilman said:
I have a table with a person's info and two tables of photos associated
with
the person. Here are the relevant tables and fields for the query:

Personal Information (Table):
PersonalID (PK)
First Name
Last Name
CategoryID
SubCategoryID

Photographs (Table):
PersonalID (FK)
PhotoFileName

TagIdentifiers:
PersonalID (FK)
Tag

GangCategories (Table):
CategoryID
GangCategory

GangSubCategories (Table):
SubCategoryID
GangSubCategory

I have created a sample person and a query with this sql statement:

SELECT [Personal Information].[First Name], [Personal Information].[Last
Name], GangCategories.GangCategory, GangSubCategories.GangSubCategory,
Photographs.PhotoFileName, TagIdentifiers.Tag
FROM ((([Personal Information] LEFT JOIN GangCategories ON [Personal
Information].CategoryID=GangCategories.CategoryID)
LEFT JOIN GangSubCategories ON [Personal
Information].SubCategoryID=GangSubCategories.SubCategoryID)
LEFT JOIN Photographs ON [Personal
Information].PersonalID=Photographs.PersonalID)
LEFT JOIN TagIdentifiers ON [Personal
Information].PersonalID=TagIdentifiers.PersonalID
WHERE ((([Personal Information].[PersonalID])= 25));

Which gives these results:

First Name Last Name GangCategory GangSubCategory PhotoFileName Tag
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag3.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag3.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag3.jpg

But I would prefer a result like this, where photos or tags aren't
duplicated:

First Name Last Name GangCategory GangSubCategory PhotoFileName Tag
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag3.jpg

What I would like to do is use this for a report that would display the
person's info and each photo and tag below that. The result I have now
will
show the person's info then the first photo, all three tags, the second
photo, all three tags, and on. How do I fix this? Any help would be
appreciated.
Click to expand...
Click to expand...
 
D

Duane Hookom

Two subforms suggests two subreports.

--
Duane Hookom
MS Access MVP
--

David Tilman said:
No, the tags and photographs are both related to the Personal Information
table. I have a Personal Information form that has two subforms, one for
tags
and one for photographs. I'll look into creating subreports to handle
this.

Duane Hookom said:
Are the tags related to the photographs?
A single query like this will not work. You might want to consider
subreports.

--
Duane Hookom
MS Access MVP
--

David Tilman said:
I have a table with a person's info and two tables of photos associated
with
the person. Here are the relevant tables and fields for the query:

Personal Information (Table):
PersonalID (PK)
First Name
Last Name
CategoryID
SubCategoryID

Photographs (Table):
PersonalID (FK)
PhotoFileName

TagIdentifiers:
PersonalID (FK)
Tag

GangCategories (Table):
CategoryID
GangCategory

GangSubCategories (Table):
SubCategoryID
GangSubCategory

I have created a sample person and a query with this sql statement:

SELECT [Personal Information].[First Name], [Personal
Information].[Last
Name], GangCategories.GangCategory, GangSubCategories.GangSubCategory,
Photographs.PhotoFileName, TagIdentifiers.Tag
FROM ((([Personal Information] LEFT JOIN GangCategories ON [Personal
Information].CategoryID=GangCategories.CategoryID)
LEFT JOIN GangSubCategories ON [Personal
Information].SubCategoryID=GangSubCategories.SubCategoryID)
LEFT JOIN Photographs ON [Personal
Information].PersonalID=Photographs.PersonalID)
LEFT JOIN TagIdentifiers ON [Personal
Information].PersonalID=TagIdentifiers.PersonalID
WHERE ((([Personal Information].[PersonalID])= 25));

Which gives these results:

First Name Last Name GangCategory GangSubCategory PhotoFileName Tag
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag3.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag3.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag3.jpg

But I would prefer a result like this, where photos or tags aren't
duplicated:

First Name Last Name GangCategory GangSubCategory PhotoFileName Tag
Bob Smith Street Gang OMG Sub 1 thumb1.jpg tag1.jpg
Bob Smith Street Gang OMG Sub 1 thumb2.jpg tag2.jpg
Bob Smith Street Gang OMG Sub 1 thumb3.jpg tag3.jpg

What I would like to do is use this for a report that would display the
person's info and each photo and tag below that. The result I have now
will
show the person's info then the first photo, all three tags, the second
photo, all three tags, and on. How do I fix this? Any help would be
appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple ComboBox linked to Outlook and Interlinked 1

Top