query does not return total if one table doesn't have excact match

[southernweyr]

I have two tables. One for previous time in make & model and one for adding new time (flightdata). I have a query that will get the sum of time from flightdata for each make & model and another query that adds the make&modelnewtotals total to the time from previousmake&model. The problem I have is that if there is not an excact match it won't display the total from previousmake&model.

flightdata has these make & models: R44 & 269C
so the query (make&modelnewtotals) only returns R44 & 269C
previousmake&model has R22, R44, and 269C and their totals
so the query (make&modeltotal) returns totals for only R44 and 269C.
Also I would like it to return a total if [A/C Make&Model] is like previousmake&model.[A/C Make&Model] instead of an excact match.

make&modelnewtotals

SELECT flightdata.[A/C Make&Model] AS Expr1, Sum(flightdata.[Total Time]) AS Total
FROM flightdata
GROUP BY flightdata.[A/C Make&Model];

make&modeltotals

SELECT [make&modelnewtotals].Expr1, Sum(nz([make&modelnewtotals].Total)+nz(previousmakemodel.[Total Time],0)) AS totalmodel
FROM previousmakemodel INNER JOIN [make&modelnewtotals] ON previousmakemodel.[A/C Make&Model] = [make&modelnewtotals].Expr1
GROUP BY [make&modelnewtotals].Expr1;

 

[lespaul]

how is R22 like R44 or 269C?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[southernweyr]

If previous has R22 and flightdata has R22 B II, I still want the total of R22 and R22 B II. It might also be something like R44, R44 Raven II, R44 Astro, R44 Raven. Or H269C, S296C, 269c, 269cb, 269cbi.

Thanks

 

[lespaul]

I saw a posting yesterday or the day before that did a join on a LIKE expression, which is what i think you need. I'll look for it and post a link.

 

[southernweyr]

thanks, any help is greatly appreciated

 

[southernweyr]

Even if I do/don't get the LIKE figured out I still need the total of previous time even if their no record in flightdata that matches. That is the main problem.

 

[lespaul]

you'll need a LEFT join to do that instead of an INNER JOIN. Check out this article to get an explanation why: Understanding SQL Joins

Now that I'm back from lunch I'll look for that other thread.

leslie

 

[lespaul]

Here's the thread!

Thread701-1176437

Good luck!

leslie

 

[southernweyr]

Thanks, the left join worked. Now if I can just read that article so I can understand why.

 

[southernweyr]

I was wondering, if I have a few names in one table or the other that aren't in both how would I have a query return totals for each name. In my limited understanding I created a left join query then a right join query and then queried both of those queries. So that gives me all the names and totals from both tables which is good. However this output is not very readable in a report because it outputs all names from one table into one field and the other names into another. I want all of the names combined into one field with the corresponding totals next to each name.

Any help is greatly appreciated.

 

[southernweyr]

I figured out a way to do it. I have the make & model table (previousmake&model) and if a new model is put into the new table (flightdata) I update previousmake&model with an append query. This puts all make & models into previousmake&model. Then I use the left join query to give me totals for every make and model previously flow and each new one.