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!