Solved

Access Query join 2 tables

Posted on 2014-07-23
5
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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