Solved

Access Query join 2 tables

Posted on 2014-07-23
5
272 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now