Solved

Access Query join 2 tables

Posted on 2014-07-23
5
275 Views
Last Modified: 2014-07-24
Dear Experts

I am trying to join 2 tables together

Table 1 is a list of projects & is called dbo_AssignmentsView
Table 2 is a list of all milestones on projects & is called dbo_AssignmentMilestonesView

dbo_AssignmentsView contains a unique set of projects defined by [AssignmentId]
eg.
AssignmentId                CompanyName
ABC                                 Cocoa Cola
DEF                                 Pepsi

dbo_AssignmentMilestonesView contains multiple [AssignmentId] as each project can have multiple milestones (called [Name])
The link between the two is [AssignmentId]

AssignmentId                CompanyName     Name             StartDate
ABC                                 Coca Cola               Stage 1           01/01/2014
ABC                                 Coca Cola               Stage 2           02/02/2014
ABC                                 Coca Cola               Stage 3           03/03/2014
DEF                                 Pepsi                       Stage 1           21/01/2014

I want to be able to join them up so against each project all the milestones appear e.g.
AssignmentId          CompanyName          Stage 1                   Stage 2                     Stage 3
ABC                           Coca Cola                     01/01/2014           02/02/2014             03/03/2014
DEF                           Pepsi                             21/01/2014


Can anybody help, I have tried to create this in design view but I keep getting repeated AssignmentId's in the result
0
Comment
Question by:correlate
  • 3
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40214790
You need to create a Crosstab query.

Add the two tables to your query grid and join them on the AssignmentID field.

Then add the AssignmentID, and CompanyName fields from dbo_AssignmentsView to the grid and add the [Name] (bad field name, should be [Stage Name] or [Milestone Name] because 'Name' is a reserved word) and StartDate fields from dbo_AssignmentMilestonesView to the grid.

Then click on the Crosstab query symbol in the ribbon and set the values in the Total and 'Crosstab' rows below the grid like:

Field            AssignmentID     CompanyName    [Name]                     StartDate
Total            Group By              Group By              Group By                  Min
Crosstab     Row Heading       RowHeading         ColumnHeading     Value

If you want the columns in a particular order you can specify that order by right clicking in the top section of the query design form and opening the properties dialog.  You will see a column headings property, where you can list the column headings, in your case Stage1,Stage2,Stage3, ...  (Note: these values must exactly match the values in your [Name] column or that column will not display in the resulting crosstab.
0
 

Author Comment

by:correlate
ID: 40214868
Thanks for that - will have to give it a go in the morning, but all seems to make sense.  Also thanks for the comment re [name] - alas the data I'm playing with comes from a 3rd party app so & that's the field name, but will rename it in the query to avoid problems later on in the Db
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40215017
As long as you wrap it in brackets [ ] Access should treat it as a data field.  

Trust me, I understand about 3rd party data and inheriting databases.
0
 

Author Closing Comment

by:correlate
ID: 40216194
Perfect, works a treat, thank you very much
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40216440
glad to help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question