UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Report Pulling From Multiple Tables    
 
   
Ricks6845
post Jan 25 2012, 12:36 PM
Post #1

New Member
Posts: 14



We are working on a Project Tracking database that currently has a Project Entry Form where all the fine details of the project are entered and a second form called "Project Comments" form that we use to add updates in the form of comments to each of the projects as time progrosses. Currently in the "Project Comment" form we have the project name automatically populate in a combobox with all of the project names that have been entered so far.
He are having issues pulling the data together in the form of a report. We want to generate a report that pulls information from both the comment table and the original project detail tables into one report. We want the report to look at the project name and pull additional details such as country, geomarket, start date, etc... from the detailed table and comments from the comment table
Is there a way for access to do a lookup such as: If Project Name is "....." then pull geomarket in from that table.. I've worked with the relationships and cannot get where I want to go.
We just want to keep our comment form simple by just having Project name and comment without the person having to add in all of the other details for us to pull a report together.
Thanks!
Ryan
Go to the top of the page
 
+
Jeff B.
post Jan 25 2012, 12:50 PM
Post #2

UtterAccess VIP
Posts: 8,736
From: Pacific NorthWet



welcome2UA.gif
Are you using a query to "join" those tables, then using the query as the source for the report?
Go to the top of the page
 
+
Ricks6845
post Jan 25 2012, 01:52 PM
Post #3

New Member
Posts: 14



No we have not. How do you reccommend joining them with a query? We have used queries for other things, but I know there is a number of types and ways of using them.
Go to the top of the page
 
+
Jeff B.
post Jan 25 2012, 02:46 PM
Post #4

UtterAccess VIP
Posts: 8,736
From: Pacific NorthWet



To join two tables, they have to share one/more "common" fields. Do yours?
Go to the top of the page
 
+
Ricks6845
post Jan 25 2012, 03:09 PM
Post #5

New Member
Posts: 14



Yes. They both share Project Name for both the Project Detail form and Project Comments Form.
Go to the top of the page
 
+
Jeff B.
post Jan 25 2012, 03:46 PM
Post #6

UtterAccess VIP
Posts: 8,736
From: Pacific NorthWet



Using something called a [ProjectName] could lead to issues ... is there any chance there could be more than one project with the same name? Who does the naming?
heck Access HELP for more information on building queries. It will explain how to "join" tables.
Go to the top of the page
 
+
Ricks6845
post Jan 25 2012, 04:01 PM
Post #7

New Member
Posts: 14



Project Name is the field: There should never been a project with the same name
The workflow is as follows:
When we recieve the project a person would create a project using our forms that has a large amount of fields. We wanted the ability to have one person set-up the project and then a separate form to add comments such as progress updates. their involvement, etc..
The comment form simply has Project Name (Combo box that only allows selections from the Main project table that is populated with the original information), Entered By:, Date, Comment Text Box.
We want to be able to generate a report that pulls the comments a long with a few select fields from the original project entry such as country, geomarket, etc...
We have an obvious relationship between Project Name so we built a query that has the relationship between project name in the two tables and then pulled down the fields we wanted in our report below to each of the field columns in the query. We run the query and it returns no results. If we double-click on the relationship lines it gives us 3 options:
"Only include rows where the joined fields from both sides are equal" - This fields us NO Results at all
"Include ALL records from "Project Table" and only those records from "CommentTable" where the joined fields are equal" - This pulls information from only the original large project table, but no results from the comment table
"Include ALL records from 'CommentTable' and only those records from 'Project Table" where the joined fields are equal. - This pulls information from only the comment table and leaves the fields we need from the original project table blank.
I hope this helps - I've used queries before, but for some reason we are missing something!
Thanks so much for all the help!
Ryan
Go to the top of the page
 
+
Jeff B.
post Jan 25 2012, 06:40 PM
Post #8

UtterAccess VIP
Posts: 8,736
From: Pacific NorthWet



If you're running a query that "produces no results", how 'bout showing us the query/SQL statement? It might help us help you...
... and again, if your ALL of table1 and ANY that match in table2 doesn't return any table2 records, that's probably because there aren't any that match.)
Go to the top of the page
 
+
Ricks6845
post Jan 26 2012, 09:00 AM
Post #9

New Member
Posts: 14



SELECT [Project Table].ProjectName AS [Project Table_ProjectName], [Project Table].Geomarket, [Project Table].Country, [Project Table].EstimatedContractUSD, [Project Table].ReceivedDate, [Project Table].ActualCompletionDate, [Project Table].EstimatedCompletionDate, [Project Table].[Project Type], [Project Table].[Project Status], CommentTable.ProjectName AS CommentTable_ProjectName, CommentTable.EnteredBy, CommentTable.CommentDate, CommentTable.CommentText
FROM [Project Table] INNER JOIN CommentTable ON [Project Table].[ProjectName] = CommentTable.[ProjectName];
Thanks for the continued help! I appreciate it!
Thanks!
Ryan
Go to the top of the page
 
+
Jeff B.
post Jan 26 2012, 11:22 AM
Post #10

UtterAccess VIP
Posts: 8,736
From: Pacific NorthWet



Ryan
Is suggested earlier, if an INNER JOIN (aka "equi-join") returns no records, that's Access' way of telling you that there are no records that match between the two tables.
Since you are using [ProjectName] as the (supposedly) matching field, better check each of the tables and be sure that you actually have matching values.
(I didn't spot anything in a first pass through your SQL statement...)
Go to the top of the page
 
+
Ricks6845
post Jan 26 2012, 12:31 PM
Post #11

New Member
Posts: 14



I appreciate all the hard work in helping us with this issue. After looking at the fields there was a formatting issue with the data that was not letting them equal and link. Thanks again for all the help!
Thanks so much!
Ryan
Go to the top of the page
 
+
Jeff B.
post Jan 26 2012, 02:51 PM
Post #12

UtterAccess VIP
Posts: 8,736
From: Pacific NorthWet



Ryan
One more (possible) concern ...
You mentioned "a formatting issue". Access tables store data one way, but allow you to "format" it to appear differently (think 2001.95 vs. $2,001.95). I'm not sure I understand how the "formatting" affected the raw data's ability to link...
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 08:28 AM