Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Query join 2 tables

Posted on 2014-07-23
5
Medium Priority
?
279 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 48

Accepted Solution

by:
Dale Fye earned 2000 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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
ID: 40216440
glad to help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 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