Tom Crowfoot
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_AssignmentMilestonesVi ew
dbo_AssignmentsView contains a unique set of projects defined by [AssignmentId]
eg.
AssignmentId CompanyName
ABC Cocoa Cola
DEF Pepsi
dbo_AssignmentMilestonesVi ew 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
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_AssignmentMilestonesVi
dbo_AssignmentsView contains a unique set of projects defined by [AssignmentId]
eg.
AssignmentId CompanyName
ABC Cocoa Cola
DEF Pepsi
dbo_AssignmentMilestonesVi
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Trust me, I understand about 3rd party data and inheriting databases.
ASKER
Perfect, works a treat, thank you very much
glad to help.
ASKER