Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine two table fileds (2003)

    I have two table, one has cost info and another has budget data. The identifiers are combination of two fields(CC and PID). I do not know how to make a query that says take all CC and PID combination and insert into a table. I can only include, from my knowledge, all the CC-PID in one table or the other table and not both. I do not have a master table that has both tables CC and PID in them either. I have master table with all the PID's, but once again i do not know how to make query relationship that says include all CC combinations from the two tables. Any suggestions??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine two table fileds (2003)

    Welcome to Woody's Lounge!

    I'm not sure I fully understand your question, but perhaps you can do the following:

    1) Create a query in design view.
    Don't add any tables.
    Select View | SQL.
    Type the following SQL statement, subsituting the names of the two tables:

    SELECT CC, PID FROM [CostTable]
    UNION
    SELECT CC, PID FROM [BudgetTable]

    Select View | Datasheet to see the result.
    Save the query if it returns the correct combinations.

    2) If you want to store the combinations in a new table, create a new query based on the union query you created in step 1).
    Add the CC and PID fields to the query grid.
    Select Query | Make-Table Query...
    Specify a name for the new table, then click OK.
    Select Query | Run to execute the new query.

    If this is not what you wanted, please post back with more details.

  3. #3
    New Lounger
    Join Date
    Jul 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine two table fileds (2003)

    the two tables i'm working with are named zzz and PUDBUD R/OT. The budget $$ amount I have in PUDBUD R/OT applies to a CC or department then to a specific PID or project. what happens in the actualls(zzz table) though is people from other departments work on the project, but their cost still incures under the same CC. What i need to create is a table that takes the budget $$$ or a CC and PID combination in PUDBUD r/ot and also adds to it the CC and PID combination from the actuals(zzz).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine two table fileds (2003)

    I'm afraid I don't understand your description. Could you try to explain it slowly and in detail? For example, I don't understand "... takes the budget $$$ <big>or</big> a CC and PID combination ...", and neither "... and <big>also</big> adds it ..." - also in addition to what?

  5. #5
    New Lounger
    Join Date
    Jul 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine two table fileds (2003)

    sorry wrong screen shot

  6. #6
    New Lounger
    Join Date
    Jul 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine two table fileds (2003)

    Screenshot deleted by HansV - see next post in this thread

    ya no problem sorry about that. I'll attach a screen shot of the two tables I'm working with. project ID and cost center or another name is CC are the fields i'm working with. Like I said before, I'm looking at lets say project ID 100 and has 50 dollars budgeted under field Budget OT and this project ID belongs to cost center 1. This project ID may also have 50 dollars budgeted under cost center 5 too. This why the identifier is cost center and then project ID and my relationship can be seen like this in the screen shot. The budget dollars is lister under the fields "OT budget "and "regular budget." under PUDBUD r/ot table. The cost incured is on the ZZZ table and the cost dollars is under the fields "OT Gross" and Regular Gross." The problem I'm running into is the project ID being charged to are not budgeted for on PUDBUD R/OT therefore that project ID is not in that table. So if I try to combine the budget and cost table together certain project Id's are left out because they are not on the PUDBUD R/OT table or they are not on the ZZZ table. The 3rd table I haven't mentioned has all the Project ID's in it but does not have a cost center field in it., the table i'm refering to is DBO_ID Master. I mention this table incase the solution to my problem could involve this table, but I don't know. Basically what I want to do is if I were to do a relationship between the tables zzz and PUDBUD R/OT I would want a many to many between to the two with the relationship line haveing arrows at both ends. A one to many relationship from zzz to PUDBUD R/OT leaves a couple project ID's off and vise verca does the same. I hope this explains what I'm trying to do. My goal in the end is so I can tell the cost center manager's where there budgets are being spent and how much they have left in them. Of coarse, the query I'm asking about is one four queries, but the other 3 queries I have working no problem.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine two table fileds (2003)

    If the DBO_ID Master table contains ALL Project IDs, you can do the following:
    - Create a query based on DBO_ID Master, zzz and PUDBUD R/OT.
    - If Access automatically creates joins between zzz and PUDBUD R/OT, remove them.
    - Join DBO_ID Master to zzz on Project_ID vs Project ID.
    - Double click the join line and select the option to return all records from DBO_ID Master and only related records from zzz, then click OK.
    - Join DBO_ID Master to PUDBUD R/OT on Project_ID vs ProjectID.
    - Double click the join line and select the option to return all records from DBO_ID Master and only related records from PUDBUD R/OT, then click OK.
    - Add the Project_ID field from DBO_ID Master, plus the fields from the other tables you need (e.g. OT Gross and OT Budget)

    Note: in the future, if you post the wrong screenshot, you can edit your post and correct it: click the Edit post button <IMG SRC=http://www.wopr.com/w3timages/edit.gif> in the post header, tick the check box Delete attachment, and specify the correct file in the Attach a file box.

  8. #8
    New Lounger
    Join Date
    Jul 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine two table fileds (2003)

    sorry I still need the cost center field though in the output table, how would I get a table created with the cost center field in it with the project ID that would include all the Project ID's from both tables. I need the cost center field because that is how I'll be reporting it.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine two table fileds (2003)

    We're back to my first reply, more or less. This time with the actual names:

    1) Create a query in design view.
    Don't add any tables.
    Select View | SQL.
    Type the following SQL statement, subsituting the names of the two tables:

    SELECT ProjectID, CC FROM [PUDBUD R/OT]
    UNION
    SELECT [Project ID], [Cost Center] FROM zzz

    Save this query as (say) qryCombinations.

    2) Create a new query based on PUDBUD R/OT, qryCombinations and zzz.
    If Access automatically creates joins between PUDBUD R/OT and zzz, remove them.
    Join qryCombinations to PUDBUD R/OT on ProjectID and on CC.
    Double click each of the join lines and select the option to return all records from qryCombinations, then click OK
    Join qryCombinations to zzz on ProjectID vs Project ID and on CC vs Cost Center.
    Double click each of the join lines and select the option to return all records from qryCombinations, then click OK
    Add the ProjectID and CC fields from qryCombinations to the query grid, the budget fields from PUBBUD R/OT and the cost fields from zzz.
    This query should give you the data for all ProjectID / CC combinations.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •