David Ball
Registered User.
- Local time
- Tomorrow, 05:01
- Joined
- Aug 9, 2010
- Messages
- 230
Hi,
I have two schedules in Primavera P6. One is a copy of the other but with changes to the budgets for some activities. I want to build a database to help me quickly determine which activities have different budgets.
I have dropped each schedule into an Excel spreadsheet and created two tables in my database to accept this data, tblProjectData and tblProject_2_Data.
Two queries have been created, one from each of the tables.
There are some issues:
· I can’t use the Activity ID as a primary key because there are duplicates within each schedule (each “schedule” is actually several “projects”, with Activity ID’s duplicated).
· I want to concatenate the Activity ID with the Project ID to get unique values, and then use this to match activities between the two versions of the schedule and then look for those with mismatched budgets.
· Because you can’t have duplicate Project ID’s in P6 the copy of the original schedule has a suffix added to the Project ID’s. I need to use only the first 5 characters of the Project ID’s when I concatenate.
· In my queries I am using LEFT to extract the first 5 characters of the Project ID’s.
· I want to create a query, qryCompareBudgets, from the two above that joins the codes I have created by concatenating, using option 2 in the Join Properties window.
· The problem I am having is that if I create a query from qryProjectData and call it qryCompareBudgets, as soon as I drag in qryProject_2_Data things kind of seize up – for example the total number of records is not displayed at the bottom of the datasheet and if I try to go to the last record using the Last Record selector it locks up Access.
I am not sure why there is a problem. I thought it might be the length of the codes created by concatenating, (they are around 20 characters long) but if I replicate this exercise in Excel using VLookup it does it instantly without a problem.
I have attached the database but had to remove about half the data to get the file size down.
What do I need to change?
Thanks very much
Dave
I have two schedules in Primavera P6. One is a copy of the other but with changes to the budgets for some activities. I want to build a database to help me quickly determine which activities have different budgets.
I have dropped each schedule into an Excel spreadsheet and created two tables in my database to accept this data, tblProjectData and tblProject_2_Data.
Two queries have been created, one from each of the tables.
There are some issues:
· I can’t use the Activity ID as a primary key because there are duplicates within each schedule (each “schedule” is actually several “projects”, with Activity ID’s duplicated).
· I want to concatenate the Activity ID with the Project ID to get unique values, and then use this to match activities between the two versions of the schedule and then look for those with mismatched budgets.
· Because you can’t have duplicate Project ID’s in P6 the copy of the original schedule has a suffix added to the Project ID’s. I need to use only the first 5 characters of the Project ID’s when I concatenate.
· In my queries I am using LEFT to extract the first 5 characters of the Project ID’s.
· I want to create a query, qryCompareBudgets, from the two above that joins the codes I have created by concatenating, using option 2 in the Join Properties window.
· The problem I am having is that if I create a query from qryProjectData and call it qryCompareBudgets, as soon as I drag in qryProject_2_Data things kind of seize up – for example the total number of records is not displayed at the bottom of the datasheet and if I try to go to the last record using the Last Record selector it locks up Access.
I am not sure why there is a problem. I thought it might be the length of the codes created by concatenating, (they are around 20 characters long) but if I replicate this exercise in Excel using VLookup it does it instantly without a problem.
I have attached the database but had to remove about half the data to get the file size down.
What do I need to change?
Thanks very much
Dave
