Update Query with a Calculated field

[Lorlai]

I am trying to create an update query that will update a table with a calculated field. Every time I run this, I receive a type conversion failure error. How can I go about this?

The Tables:

Product

Product Number
FT Headcount Qtr 1
FT Headcount Qtr 2
FT Headcount Qtr 3
FT Headcount Qtr 4
PT Headcount Qtr 1
PT Headcount Qtr 2
PT Headcount Qtr 3
PT Headcount Qtr 4

FT Headcount Avg

Product Number
FTAvgQtr1
FTAVGQtr2
FTAVGQtr4

PT Headcount Avg

Product Number
PTAvgQtr1
PTAVGQtr2
PTAVGQtr3
PTAVGQtr4

Where the FT and PT Headcounts are calculated from:


Resources

Product Number
Qtr1: (nz([Nov],0)+nz([Dec],0)+nz([Jan],0))/3
Qtr2: (nz([Feb],0)+nz([Mar],0)+nz([Apr],0))/3
Qtr3: (nz([May],0)+nz([Jun],0)+nz([Jul],0))/3
Qtr4: (nz([Aug],0)+nz([Sep],0)+nz([Oct],0))/3

The SQL to create the PT and FT Headcounts:

SELECT Resources.Project_Number, Sum(Resources.Qtr1) AS AvgQtr1, Sum(Resources.Qtr2) AS AvgQtr2, Sum(Resources.Qtr3) AS AvgQtr3, Sum(.Qtr4) AS AvgQtr4
FROM Resources
WHERE (((Resources.Type)="FT"))
GROUP BY Resources.Project_Number;

SELECT Resources.Project_Number, Sum(Resources.Qtr1) AS AvgQtr1,  Sum(Resources.Qtr2) AS AvgQtr2, Sum(Resources.Qtr3) AS AvgQtr3,  Sum(.Qtr4) AS AvgQtr4
FROM Resources
WHERE (((Resources.Type)="PT"))
GROUP BY Resources.Project_Number;

What I am trying to do is to update the Product Table to contain the proper Headcounts. For example, Product.[FT Headcount Qtr 1] will contain [FT Headcount Avg].FTAvgQtr1.

My attempt to do this has given me the type conversion failure ( this was a test to see if I could get even one field to update. The error message was the same):

UPDATE Product 
SET Product.[FT Headcount Qtr 1] = DLookUp("FTAvgQtr1","[FT Headcount Avg]","[Product_Number]=" & [Products].[Product_Number]);

How can I create an update statement that will update the data the way that I need?

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

[ssanfu]

From what little info you posted on your tables, it looks to me that your structure is not normalized.
In any case, I do have a question: why do both queries have

Sum(.Qtr4) AS AvgQtr4

instead of

Sum(Resources.Qtr4) AS AvgQtr4

??

[Lorlai]

Hi Steve,

Thank you for your response. I have had to change the table names for security purposes, and I must have forgotten to add the Resources.Qtr4. This was an error on my part.

With this new knowledge, is there another way to work around this? Or do you need more information (such as data types for the tables)?

Thank you

[ssanfu]

It would help to know your structure and field types. I am wondering how you store the data for different years. I know you said that you changed table/field names, but from what you posted, it looks like you do not have a normalizes structure.

SQL is not my strongest suit, so I would do this using VBA. I would run queries, store values in variables, do the calculations and update the table.