Query to Total Multiple ID's (1 Viewer)

tmyers

Well-known member
Local time
Today, 07:49
Joined
Sep 8, 2020
Messages
1,090
I am trying to run a query to total up the quantities for each unique ID on a table.
Count Ex.PNG

TypeID is the ID that identifies each unique type. So I need a query to run a total for each unique TypeID. So in the picture above, the query would say TypeID 80 has a total of 15, 79 would have a total of 10 etc. All various instances I have tried, all total the entire lot or dont work at all unless I specifically tell it what ID to total.

Can anyone offer insight on this one?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:49
Joined
Oct 29, 2018
Messages
21,357
Have you tried?
SQL:
SELECT TypeID, Sum(Quantity) AS Total FROM TableName GROUP BY TypeID
 

tmyers

Well-known member
Local time
Today, 07:49
Joined
Sep 8, 2020
Messages
1,090
That worked!
I dont know what I was doing to muck it up.
I just had to add some Criteria to keep it specific to a job and it worked wonderfully.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:49
Joined
Oct 29, 2018
Messages
21,357
That worked!
I dont know what I was doing to muck it up.
I just had to add some Criteria to keep it specific to a job and it worked wonderfully.
Excellent! Glad to hear you got it sorted out. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:49
Joined
Feb 19, 2002
Messages
42,970
You really need to add Job or project or whatever you call your higher level grouping.. You can't be only working on a single jor/project. For example, these sheets are for the Hilton job and those sheets are for the Subway job. You would never want to total them together.
 

tmyers

Well-known member
Local time
Today, 07:49
Joined
Sep 8, 2020
Messages
1,090
How I ended up doing it was I made a Sum query for Quantities that is grouped by TypeID and JobID and filtered via the JobID on the main form so it only pulls types on that given job.

So it only looks at JobID say, 2 then takes all unique TypeID's within that job and totals them. So when the user opens a specific job, the form has the JobID that the query can grab, then goes to each TypeID with that JobID and adds all quantites for each unique TypeID. So for JobID 2, TypeID 17 on JobID 2 has 3 instances each with a quantity of 2, so it returns a total of 6.

Im sure that explanation wasn't needed, but that is how I managed to get it to work. Is that a correct method of doing it? I have tested it across various instances, and it has not messed up yet.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:49
Joined
Feb 19, 2002
Messages
42,970
Yes, that's fine. What you showed us didn't not include job. I was only reminding you that you needed Job as a criteria.
 

Users who are viewing this thread

Top Bottom