Link to home
Start Free TrialLog in
Avatar of Tom Crowfoot
Tom CrowfootFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access Query join 2 tables

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
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tom Crowfoot

ASKER

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
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.
Perfect, works a treat, thank you very much
glad to help.