query question

[ncsthbell]

I am pulling my hair out trying to figure out how to make this work in a query so thought I would ask for any suggestions.
I have 2 queries, lets call one "Cats" and one "Dogs", the only key is "address". I am trying to build another query that will produce one row that combines the data results from both the "Cats" and "Dogs" queires. What I want to wind up with is a row of data for an address that has both "Cat" data and "Dog" data on it. In the situation I have, I have data in the "Cats" query when I run it separatly and no data in the "Dogs" query. So when I run the new query to combine the data, I get nothing. If I put data in the "Dogs" tables, I will get results, BUT, I will not always have data in both of them so I need to have the query.
I have tried a union of the 2 but that doesn't work because the data for each is different. Also, if I have "Cat" data but no "Dog" data, I get nothing. I want to get a row if I have either exist. It seems like it should be so simple, however, I just can't get the results I want. Hope this makes sense!!

[kcdoell]

Can you post the SQL of the "Dog" query and the SQL of the "Cat" query. It would make it easier to understand.

Best regards,

Keith.

[ncsthbell]

Sorry, here is the sql (I used cars & boats instead!)
This is what I am currently trying, a union query but it is not giving the results I need.

The UNION query
SELECT Owner, Car, Mileage, Make, year, color, MPG
FROM qCars
SELECT Owner, Boat, Type, color, Year, seating, maxWeight
FROM qBoats


RESULTS of union query:
Owner Car Mileage Make Year Color Mpg
Me Honda 139000 Accord 2000 Black 24
Me Bayliner Sailboat Blue 1999 8 4000

Note - I want to get one row with both miles and fuel on the same line but you can see I am getting 2 rows and the 2nd row is really my fuel/gals but it is creating it as if it is mileage. Not sure if I can do this with the UNION statement????


These are results from each of the miles & fuel queries individually:
qCars
Owner Car Mileage Make Year Color Mpg
Me Honda 139000 Accord 2000 Black 24

qBoats:
Owner Boat Type color Year seating maxWeight
Me Bayliner SailBoat Blue 1999 8 4000

[kcdoell]

RESULTS of union query:
Owner Car Mileage Make Year Color Mpg
Me Honda 139000 Accord 2000 Black 24
Me Bayliner Sailboat Blue 1999 8 4000

Note - I want .............

So I can understand, give me the result (the one row only..) that you thought the Union Qry would display using the above example........

Keith.

[kcdoell]

So I can understand, give me the result (the one row only..) that you thought the Union Qry would display using the above example........

Keith.

Also take a look at this Building Union Queries. All the columns have to match in name and type to work correctly.....

It sounds like you want to merge data all into one row. If so, can you not start from scratch, by pulling both tables into a new query and then pulling in the fields that you are looking to populate?

Keith.

[rbhulai]

Hello,

I don't think that you need an union query. Both queries got the fieldname Owner. So make a new query with the two tables with an join on Owner. Select all the fields you needed and you you will get one row.

Query:
SELECT Boats.Owner, Boats.Boat, Boats.Type, Boats.Color, Boats.Year, Boats.Seating, Boats.MaxWeight, CARS.Car, CARS.Mileage, CARS.Make, CARS.Year, CARS.color, CARS.MPG
FROM Boats INNER JOIN CARS ON Boats.Owner = CARS.Owner;

Try this one.