Update query with sum data

[Lorlai]

I am trying to create an update query that is taking a certain set of data and counting the number. I can make the count query work on its own as a select query, but am having problems moving this into an update query.

The select statement:

SELECT DISTINCT Resource_Pool.Product_Number, Count(*) AS Headcount
FROM Resource_Pool
WHERE (((Resource_Pool.Resource_Pool_Name) Like "*TSmith*"))
GROUP BY Resource_Pool.Product_Number;

I had tried saving the query and using it as a table for my update statement, but recieve the "Syntax Error in query. Incomplete query clause" error. This is what I had:

UPDATE [Worker Headcount] 
LEFT JOIN Products ON [Worker Headcount].Product_Number = Products.ID
SET Products.[Worker Headcount] = [Worker Headcount].[Headcount];

Where [Worker Headcount] was the first select query.

Any clue as to how I can combine these to make a working update statement?? I'm very nearly finished and this last problem is just over my head!



Thank you for any help you may be able to provide!

[jzwp11]

I'm not sure why use are using the Left Join in the update query since you only would want to update those records where your query has a headcount value for the product. Even with that you would probably get "Operation must use an updateable query" error.

This might work:

UPDATE Products SET Products.[Worker HeadCount] = DLookUp("Headcount","[worker headcount]","product_number=" & Products.ID);


BTW, in most cases calculated values such as counts and sums are not stored in tables, but rather calculated on the fly when you need them in queries, forms or reports. So I have to question why you even need the update query or even the head count field in the product table, both are unnecessary.

[Lorlai]

Thank you jzwp11, I can feel like its getting closer.

I tried your suggestion, but am only getting blank records. There are the correct number (48), but all the records are blank.

The data I need is in two different tables. I am trying to update Products, and the headcount data that I need is in Resource_Data. I'm probably just having a thick head about this and am not asking the proper question. The data that I need is in Resource_Data.Resource_Pool_Name. I want to find a count of the individuals in Resource_Pool_Name like *TSmith*, and I want them grouped by Product Number. I then want these numbers to be updated to Products.[Workers Headcount].

Did I explain this a little better? As I said, I'm so close to being done! This is the last major step and I'm having so many problems wrapping my head around it. Any help is super appreciated!!

[jzwp11]

First, I still don't think you even need to waste your time on the update query. I believe that you can get what you need by using SELECT queries.

But, I'll try to answer some of your questions:

I tried your suggestion, but am only getting blank records.

Can you post what you tried? Are all names (field/table/query) spelled correctly?

I want to find a count of the individuals in Resource_Pool_Name like *TSmith*, and I want them grouped by Product Number. I then want these numbers to be updated to Products.[Workers Headcount].

I'm a little confused, if you want the total count from all individuals updated in the products table then you would not need to have totals by individuals nor would you limit the records counted by having *TSmith* in the criteria section of the counting query

Your original query:

SELECT DISTINCT Resource_Pool.Product_Number, Count(*) AS Headcount
FROM Resource_Pool
WHERE (((Resource_Pool.Resource_Pool_Name) Like "*TSmith*"))
GROUP BY Resource_Pool.Product_Number;

To get a total count by product get rid of the WHERE Clause




SELECT DISTINCT Resource_Pool.Product_Number, Count(*) AS Headcount
FROM Resource_Pool
GROUP BY Resource_Pool.Product_Number;

IF you do want a count by product AND individual then this is what you need, but of course trying to update the count in the product table from this query would not give you the total count:

SELECT DISTINCT Resource_Pool.Product_Number, Resource_Pool.Resource_Pool_Name, Count(*) AS Headcount
FROM Resource_Pool
GROUP BY Resource_Pool.Product_Number, Resource_Pool.Resource_Pool_Name

Perhaps if you supply some example raw data especially from the Resource_Pool table and what you expect the final data to look like, that might help clarify things for us.

[Lorlai]

Sorry this is so confusing! I really appreciate you taking the time to try and help me.

Some of the raw data looks like (I had major problems trying to insert a screen shot, and trying to put this in a table):

From Resource_Data:
Product_ID |Resource_Pool_Name
32569 Tsmith-bsmalls-jho
32569 Tsmith-bsmalls
32569 bobpark-Angels-dnobel
32569 wilsonn-daok
55678 Tsmith-puaol
55678 jjoe-kings-loopa
77798 bobpark-Angels-dnobel
999083 jjohnson-jjohnson-copan
999083 Tsmith-puaol

What I want is to gain of count of all resource_pool_names that are like *Tsmith* by product. So with the example data above, it would look like:
ProductID | Headcount
32569 2
55678 1
999083 1

In my Products table, I have a field called Workers_Headcount that needs to be updated with the headcount gained from the above query. Right now it looks like:
Product_ID |Color |Worker_Headcount
32569 Black
33779 Red
45660 Orange
55678 Yellow
62341 Green
77798 Black
100873 Blue
444890 Purple
999083 Silver

With Worker_headcount being blank. I need to insert the headcount numbers gained from the query two up. So the Products table would look like:
Product_ID |Color |Worker_Headcount
32569 Black 2
33779 Red
45660 Orange
55678 Yellow 1
62341 Green
77798 Black
100873 Blue
444890 Purple
999083 Silver 1

Does this make what I am trying a little more clear? Again, I really really appreciate your help with this!

[jzwp11]

Your original query:

SELECT DISTINCT Resource_Pool.Product_Number, Count(*) AS Headcount
FROM Resource_Pool
WHERE (((Resource_Pool.Resource_Pool_Name) Like "*TSmith*"))
GROUP BY Resource_Pool.Product_Number;

Should return:

ProductID | Headcount
32569 2
55678 1
999083 1

The update query I posted with the Dlookup() function should work, but I did notice that there was a missing "_" . See where I put it in red below. You will have to double check the spelling of all field/query and table names to make sure they match your database.UPDATE Products SET Products.[Worker_HeadCount] = DLookUp("Headcount","[worker headcount]","product_number=" & Products.ID);


The above query should give you this:

Product_ID |Color |Worker_Headcount
32569 Black 2
33779 Red
45660 Orange
55678 Yellow 1
62341 Green
77798 Black
100873 Blue
444890 Purple
999083 Silver 1




You may want to backup the database in case the update does not go as planned.

[Lorlai]

jzwp11, I owe you big time!! It was just that misspelling, I can't believe I missed it. I will sing your praises for a long time. You rock! Thank you soo so much for your help!

[jzwp11]

You're welcome. Glad you got it worked out!