Results 1 to 10 of 10
  1. #1
    Kevin_ is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    4

    Unhappy Options when joining two tables

    Hi - I was wondering if this was possible. I have two tables - let's say one table has baseball teams, dates of games, # of runs scored, etc. Let's say the second table has players names, height, weight, etc. Let's assume there are exactly 15 players on each team.



    First, I've run a query just on the 1st table to boil it down to Team and # of Runs (let's say Red Sox 722 total runs scored, Yankees 563 runs scored, Rays 211 runs scored) So there are just two variables with a "sum" on the runs scored. Now I want to join this information with the list of players that are on the team. The common variable is team name.

    Naturally, I will get a total of 15 lines per team. Each Line that has Red Sox will have 722, each line that has Yankess will have 563, etc.

    Is it possible to run a Join Query that will NOT get 15 lines, but WILL get 16 lines? The first line will be Red Sox, 722 Runs scored, with the Player, Height, Weight all BLANK. The next 15 lines will be each player's name that is on the Red Sox, Height, Weight, but all 15 lines will be BLANK under Runs Scored. Then I will have a row for Yankees (563 runs scored) with no player data on that row. But I'll have 15 rows below with no runs scored, all being players on the yankees.

    This way, my total runs scored at the bottom of the query will be equal to the total runs scored on the original table - however, I've still brought in all the player info, effectively merging two tables while maintaing the integrity of the numbers.

    Obviously, my real life scenario is much different but any help would be greatly appreciated!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Are you trying to display this information on a report? If so the query you have will probably do fine, you just have to add group headers/footers to break up the information you have so it's visually more appealing.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Easiest solution is to build a report with Sorting and Grouping to display the data. Group By team name and place the name and runs in the group header/footer section, players in the detail section.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Kevin_ is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    4
    Quote Originally Posted by rpeare View Post
    Are you trying to display this information on a report? If so the query you have will probably do fine, you just have to add group headers/footers to break up the information you have so it's visually more appealing.
    Actually, I was trying to do this right in a query. I've never built a report in access before. At the end of the day, I want to copy/paste this into excel so that other people can input data into some of the fields that I have left blank.

    Can a report be copied/pasted into excel?

  5. #5
    Kevin_ is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    4
    Quote Originally Posted by June7 View Post
    Easiest solution is to build a report with Sorting and Grouping to display the data. Group By team name and place the name and runs in the group header/footer section, players in the detail section.
    Similar suggestion as above - I've never built a report in Access. Can that be copied and pasted easily into excel (while retaining same rows/columns)?

    Thanks!

  6. #6
    AndreT is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2011
    Posts
    25
    Create another query from the team only, then UNION it with your query. I do that all the time.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can export a report to Excel manually with the Export wizard or with VBA code.

    Otherwise, UNION is the way to create the query you describe. There is no wizard or designer for UNION query. Must type it in the SQL View editor.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Kevin_ is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    4
    Quote Originally Posted by June7 View Post
    Can export a report to Excel manually with the Export wizard or with VBA code.

    Otherwise, UNION is the way to create the query you describe. There is no wizard or designer for UNION query. Must type it in the SQL View editor.
    Hello - So I wrote a query and I am trying to UNION the query with a table and I'm getting an error: "subqueries cannot be used in expression." The subquery is an IIF statement.

    Are those allowed in a UNION?

    One of the other fields in the query (a field containing numbers) is dependent on the IIF statement.

    Thanks!

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't think issue is the UNION, but the query in an expression. A query returns a record, not a single value. Use data aggregate function (DLookup, DCount, DMax, DMin, DAvg) in an expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    AndreT is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2011
    Posts
    25
    Not sure what happened with your UNION query. Usually I do complicated union query by creating individual queries thru query design, then open up the SQL and copy the 2nd one into the first one and type in UNION in between them.

    If that still doesn't work, A sample SQL may help find out the problem. Or you may want to post sample input and expected output.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. joining tables
    By frozendmj in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 01:45 PM
  2. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 04:37 AM
  3. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 03:43 PM
  4. Joining or Combining Two tables
    By escuro19 in forum Queries
    Replies: 2
    Last Post: 02-16-2010, 03:55 PM
  5. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums