Totaling field from multiple tables

[jsparker]

Ok so I hope this is easy so I can feel silly.

I have 5 different tables where information is entered. I want to run a query that pulls from those 5 tables three fields (WeekEnding, UserID, TotalTime). Each of the 5 tables has a Time specific to that table but I want to total that time.

So my end result would be

WeekEnding UserID Total of Time from all Tables.

Each table has multiple WeekEndings and multiple UserID which I think is my pain point. Each table is linked by WeekEnding and UserID which I was hoping would pull only the WeekEnding, UserID and provide a Total of Time from each table. In my query I have the Week Ending, UserID and then an Expression to Total the necessary fields to give me a TotalTime. I also have a criteria set to ask for a specific WeekEnding when it queries all the records. When I run the query it only comes back with one entry though I have 3 records with the same WeekEnding and UserID.

What am I missing that I'm only showing 1 record when I know I have more?

 

[georgedwilkinson]

You haven't told us much so I'll give you a general answer.

Look into using a union query along with whatever aggregate query you're using (I assume Sum()).

 

[jsparker]

I apologize. Didn't know if I gave enought information or not.

I'm currently using a Select Query but of course don't know if that's the correct one. I'm finding it only pulls results that are found in ALL of the tables. So if for example I have entries in 3 of the 5 tables it wont pull those records because it is not inclusive to all tables.

How do I do a Union query? Forgive my ignorance.

 

[georgedwilkinson]

Post the SQL of your query here and explain the tables a little better, please.

 

[jsparker]

SELECT [tblWeek Ending].[tblWeek Ending], [tblDC Associates].[tblUser ID], [OCR Mode Time].OCR, [KE Mode Time].KE, [BDE Mode Time].BDE, [KEV Mode Time].KEV, [KFI Mode Time].KFI, [OCRTotalWait]+[KETotalWait]+[BDETotalWait]+[KEVTotalWait]+[KFITotalWait] AS [Total Wait]

FROM [tblWeek Ending] INNER JOIN ((((([tblDC Associates] INNER JOIN [KFI Mode Time] ON [tblDC Associates].[tblUser ID] = [KFI Mode Time].UserID) INNER JOIN [KEV Mode Time] ON [tblDC Associates].[tblUser ID] = [KEV Mode Time].UserID) INNER JOIN [BDE Mode Time] ON [tblDC Associates].[tblUser ID] = [BDE Mode Time].UserID) INNER JOIN [KE Mode Time] ON [tblDC Associates].[tblUser ID] = [KE Mode Time].UserID) INNER JOIN [OCR Mode Time] ON [tblDC Associates].[tblUser ID] = [OCR Mode Time].UserID) ON ([tblWeek Ending].[tblWeek Ending] = [OCR Mode Time].WeekEnding) AND ([tblWeek Ending].[tblWeek Ending] = [KE Mode Time].WeekEnding) AND ([tblWeek Ending].[tblWeek Ending] = [BDE Mode Time].WeekEnding) AND ([tblWeek Ending].[tblWeek Ending] = [KEV Mode Time].WeekEnding) AND ([tblWeek Ending].[tblWeek Ending] = [KFI Mode Time].WeekEnding);

Tables
tblDC Associates = List of associates names and UserID
tbleWeek Ending = List of weeks
OCR, KE, KEV, KFI, BDE Mode Time = Metrics for production time, idle time, wait time and keystrokes/pages entered.

Let me know if you need anything else.

 

[georgedwilkinson]

Lightbulb comes on!

Ignore the advice regarding the union query...it is not appropriate for your needs.

What you'll need is to replace some of your joins with outer joins. You can do this quite easily in the query designer by right clicking on the lines between the tables and selecting "Join Properties". There are 3 options that will allow you to pick which table must have data and which is allowed to be "blank". When you see this you'll get it.

The bummer about this is that queries can get quite involved and SQL won't allow "incompatible" outer joins. If that is the case with you, you'll need to create separate outer join queries and tie them together with the "final" query (just select "Queries" in the Show Table dialog in the query designer).

Let us know if you get stuck.

 

[jsparker]

Thanks for the direction George. So I understand you correctly, you are stating that I should create multiple queries with outer joins first and then essentially query those queries to a final query? If that is correct which do I outer join first? The mode time tables to each other? Or the mode time tables to the associates table or the week ending table first?

Appreciate all your help.

 

[jsparker]

George,

I decided to join the Mode tables with the associate and week ending tables first. When I do outer joins with the mode to associate and from mode to week ending it gives me my list of records but then it gives me a second list of records that don't match with the week ending. The only way it gives me just the records I want is with the AND join (option one) where records are on both tables. What am I doing wrong?

 

[jsparker]

I think I solved the outer join problem. The 3 extra records it was pulling werent duplicates but actual records but they had a week ending date that wasnt found on the week ending table. So the outer join links should work as you suggested.

So once I have outer join queries for all modes to associates and week ending tables do I just do another query with all of the modes, week ending and associates tables to show the total I'm looking for?

 

[georgedwilkinson]

Yes. Sorry, I just woke up and am about to take my wife for minor surgery.

You didn't need to put criteria in the queries you base the final query off of, you can do that in the final query.

Also, did you try putting all the outer joins in one big unifying query? Not required but easiest to maintain, if possible.

 

[jsparker]

I hope everything is alright with the wife. Appreciate you taking time to respond to me.

I tried the outer joins in a monster query but got the error message about too many ambiguous joins etc.

 

[jsparker]

George,

Having the same problem when I query the queries. Telling me that I need to set up a query first. I've attached a screenshot.